本文是本系列第 4 篇。上一篇已经创建了专栏业务数据库kb_shop本文继续在其中规划业务模式让客户、商品、订单等对象有清晰的归属。引言上一篇文章已经创建了专栏业务库kb_shop解决了业务对象应该放在哪个数据库中的问题。但一个数据库内部仍然可能包含大量表、视图、函数和辅助对象如果全部放进默认的public模式随着文章推进和对象增多结构会很快变得混乱。本文进一步讨论 KingbaseES 中的 Schema 治理。我们会把kb_shop划分为sales、inventory、ops三个模式用它们分别承载销售、库存和运维辅助对象。这样做不仅是为了目录清晰更是为了后续权限控制、对象检索、查询编写和备份维护更加接近真实项目。很多数据库新手会把所有表都建在public模式下。刚开始只有一两张表时看不出问题但业务对象一多就容易出现命名混乱、权限边界不清、查询路径不明确等问题。KingbaseES 支持模式也就是 schema。模式可以理解为数据库内部的“逻辑分区”数据库 kb_shop ├─ sales 客户、订单、订单明细 ├─ inventory 商品、库存 ├─ ops 运维检查、脚本执行记录 └─ public 默认模式尽量少放业务对象本文目标在kb_shop中创建多个业务模式。理解search_path的作用。学会使用完整对象名访问表。建立本系列后续文章的对象命名规范。文章目录引言Schema 是数据库对象治理的第一层边界一、连接 kb_shop二、查看当前已有模式三、创建业务模式四、理解 search_path五、完整对象名更适合文章演示六、创建一个 ops 检查表七、设置数据库级默认 search_path八、对象命名规范九、常见问题排查问题 1报错 schema does not exist问题 2查询表时报 relation does not exist问题 3search_path 设置了但没有生效问题 4不确定某张表在哪个模式十、本文小结Schema 是数据库对象治理的第一层边界Schema 通常翻译为“模式”。在一个数据库中模式用于组织表、视图、函数等数据库对象。它既不是独立数据库也不是简单文件夹而是一种数据库内部的命名空间和权限边界。为什么不能所有对象都放进public核心原因有三点问题全放在 public 的后果使用 Schema 的收益命名冲突不同业务模块容易出现同名表通过sales.customer、crm.customer区分权限粗糙很难只授权某一类业务对象可以按模式授予访问范围维护困难对象多后缺乏业务分层DBA 和开发能快速定位对象归属在企业系统中模式设计往往对应业务模块设计。销售对象、库存对象、运维对象分开管理能够让后续权限控制、备份策略、脚本维护和问题排查更加清晰。本文将kb_shop划分为sales、inventory、ops本质上是在做数据库对象治理。这个设计会贯穿后续文章客户和订单归属于sales商品和库存归属于inventory检查和辅助表归属于ops。一、连接 kb_shop打开命令提示符进入工具目录cd /d D:\Tools\Kingbase\ES\Server\bin连接kb_shopksql -U system -d kb_shop -h localhost -p 54321确认当前数据库SELECTcurrent_database()AScurrent_db,current_userASlogin_user;预期结果二、查看当前已有模式执行\dn是ksql中查看模式列表的快捷命令。相比直接查询系统视图它更适合日常交互式检查而 SQL 查询更适合写进脚本或报表。两种方式并不冲突前者便捷后者可编程。\dn通常会看到public等模式。也可以使用 SQL 查询SELECTschema_nameFROMinformation_schema.schemataORDERBYschema_name;这两种方式都可以。\dn适合日常命令行查看SQL 查询适合写脚本和报表。三、创建业务模式为了后续文章形成清晰结构我们创建 3 个模式这一步不是为了“多分几个目录”而是提前建立业务边界。sales负责销售域对象inventory负责库存域对象ops负责运维辅助对象。后续讲权限时就可以做到只给某个用户访问某个模式而不是把整个数据库都开放出去。模式名用途sales客户、订单、订单明细等销售业务对象inventory商品、库存等库存业务对象ops巡检、脚本记录、辅助管理对象执行CREATESCHEMAIFNOTEXISTSsales;CREATESCHEMAIFNOTEXISTSinventory;CREATESCHEMAIFNOTEXISTSops;再次查看\dn可以看到为了让对象用途更清楚可以加备注COMMENTONSCHEMAsalesISCustomer and order business objects;COMMENTONSCHEMAinventoryISProduct and stock business objects;COMMENTONSCHEMAopsISOperation check and helper objects;四、理解 search_path创建了模式后会遇到一个问题如果执行下面的 SQLSELECT*FROMcustomer;数据库应该去哪一个模式下找customer表这就涉及search_path。查看当前搜索路径search_path决定了当 SQL 中没有写模式名时数据库按什么顺序查找对象。它是方便工具也是潜在风险。方便之处在于 SQL 可以写得短风险在于不同会话的search_path不一致时同一句 SQL 可能访问到不同对象。SHOWsearch_path;常见结果类似这表示当你不写模式名时数据库会按顺序查找对象。为了让后续操作更方便可以在当前会话设置SETsearch_pathTOsales,inventory,ops,public;再查看SHOWsearch_path;此时如果执行SELECT*FROMcustomer;数据库会优先在sales模式中寻找customer。五、完整对象名更适合文章演示虽然search_path很方便但在写技术文章、脚本和生产变更时我更推荐使用完整对象名完整对象名能降低上下文依赖。尤其是文章和教程读者的环境可能和我文章里写的不完全一致使用sales.customer这样的写法比只写customer更稳也更能体现数据库对象归属。sales.customer inventory.product ops.script_run_log这样读者一眼就知道对象属于哪里也能减少“当前搜索路径不一致”导致的报错。例如后续创建客户表时不写CREATETABLEcustomer(...);而写CREATETABLEsales.customer(...);查询也尽量写SELECT*FROMsales.customer;这会让文章更稳定读者复制 SQL 时也不容易受当前会话状态影响。六、创建一个 ops 检查表为了验证模式是否可用我们先在ops模式下创建一张检查表。CREATETABLEIFNOTEXISTSops.schema_check(idSERIALPRIMARYKEY,schema_nameVARCHAR(50)NOTNULL,check_resultVARCHAR(50)NOTNULL,checked_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);插入检查记录INSERTINTOops.schema_check(schema_name,check_result)VALUES(sales,created),(inventory,created),(ops,created);查询SELECTid,schema_name,check_result,checked_atFROMops.schema_checkORDERBYid;预期结果类似七、设置数据库级默认 search_path如果每次连接kb_shop后都手工执行SETsearch_pathTOsales,inventory,ops,public;会比较麻烦。可以给数据库设置默认搜索路径ALTERDATABASEkb_shopSETsearch_pathTOsales,inventory,ops,public;设置后断开重连\q重新连接ksql -U system -d kb_shop -h localhost -p 54321再查看SHOWsearch_path;如果结果中包含sales, inventory, ops, public说明数据库级默认配置生效。如果你不想设置数据库级默认值也可以不执行这一步。后续文章会尽量使用完整对象名避免依赖搜索路径。八、对象命名规范为了让后续文章风格统一系列文章采用以下命名规则类型规则示例数据库小写下划线kb_shop模式小写业务含义明确sales、inventory表小写单数名词或业务短语customer、product、customer_order字段小写下划线customer_id、created_at主键表名加_idproduct_id检查约束ck_表_字段ck_product_price唯一约束uk_表_字段uk_customer_phone外键约束fk_子表_父表fk_order_customer这种命名方式的好处是可读性高后续排查约束报错时也更容易定位。九、常见问题排查问题 1报错 schema does not exist如果执行CREATETABLEsales.customer(...);却提示schema sales does not exist说明模式还没创建。先执行CREATESCHEMAIFNOTEXISTSsales;再建表。问题 2查询表时报 relation does not exist可能原因是表在某个模式下但查询时没有写完整对象名。错误示例SELECT*FROMcustomer;推荐写法SELECT*FROMsales.customer;或者先设置SETsearch_pathTOsales,inventory,ops,public;问题 3search_path 设置了但没有生效如果执行了ALTERDATABASEkb_shopSETsearch_pathTOsales,inventory,ops,public;但当前窗口里SHOW search_path;仍然没变原因通常是数据库级配置对新连接生效。处理方式\q重新连接后再查看。问题 4不确定某张表在哪个模式可以查询系统信息SELECTtable_schema,table_nameFROMinformation_schema.tablesWHEREtable_nameschema_checkORDERBYtable_schema;如果后续对象多了这个查询很实用。十、本文小结本文承接kb_shop数据库的创建完成了数据库内部第一层对象治理Schema 分层。通过sales、inventory、ops三个模式我们把后续业务对象按照职责分开避免所有表都堆在public中。本文在kb_shop中完成了业务模式规划sales 客户、订单 inventory 商品、库存 ops 检查、辅助管理 public 默认模式尽量少放业务对象同时我们掌握了\dn CREATE SCHEMA COMMENT ON SCHEMA SHOW search_path SET search_path ALTER DATABASE ... SET search_path 完整对象名访问下一篇会开始创建真正的业务表客户表、商品表、订单表、订单明细表并重点讲字段类型、主键、唯一约束、检查约束和外键约束。也就是说第四篇完成了对象分层第五篇会进入关系建模和数据完整性设计。
KES数据库Schema 治理与业务对象分层设计
本文是本系列第 4 篇。上一篇已经创建了专栏业务数据库kb_shop本文继续在其中规划业务模式让客户、商品、订单等对象有清晰的归属。引言上一篇文章已经创建了专栏业务库kb_shop解决了业务对象应该放在哪个数据库中的问题。但一个数据库内部仍然可能包含大量表、视图、函数和辅助对象如果全部放进默认的public模式随着文章推进和对象增多结构会很快变得混乱。本文进一步讨论 KingbaseES 中的 Schema 治理。我们会把kb_shop划分为sales、inventory、ops三个模式用它们分别承载销售、库存和运维辅助对象。这样做不仅是为了目录清晰更是为了后续权限控制、对象检索、查询编写和备份维护更加接近真实项目。很多数据库新手会把所有表都建在public模式下。刚开始只有一两张表时看不出问题但业务对象一多就容易出现命名混乱、权限边界不清、查询路径不明确等问题。KingbaseES 支持模式也就是 schema。模式可以理解为数据库内部的“逻辑分区”数据库 kb_shop ├─ sales 客户、订单、订单明细 ├─ inventory 商品、库存 ├─ ops 运维检查、脚本执行记录 └─ public 默认模式尽量少放业务对象本文目标在kb_shop中创建多个业务模式。理解search_path的作用。学会使用完整对象名访问表。建立本系列后续文章的对象命名规范。文章目录引言Schema 是数据库对象治理的第一层边界一、连接 kb_shop二、查看当前已有模式三、创建业务模式四、理解 search_path五、完整对象名更适合文章演示六、创建一个 ops 检查表七、设置数据库级默认 search_path八、对象命名规范九、常见问题排查问题 1报错 schema does not exist问题 2查询表时报 relation does not exist问题 3search_path 设置了但没有生效问题 4不确定某张表在哪个模式十、本文小结Schema 是数据库对象治理的第一层边界Schema 通常翻译为“模式”。在一个数据库中模式用于组织表、视图、函数等数据库对象。它既不是独立数据库也不是简单文件夹而是一种数据库内部的命名空间和权限边界。为什么不能所有对象都放进public核心原因有三点问题全放在 public 的后果使用 Schema 的收益命名冲突不同业务模块容易出现同名表通过sales.customer、crm.customer区分权限粗糙很难只授权某一类业务对象可以按模式授予访问范围维护困难对象多后缺乏业务分层DBA 和开发能快速定位对象归属在企业系统中模式设计往往对应业务模块设计。销售对象、库存对象、运维对象分开管理能够让后续权限控制、备份策略、脚本维护和问题排查更加清晰。本文将kb_shop划分为sales、inventory、ops本质上是在做数据库对象治理。这个设计会贯穿后续文章客户和订单归属于sales商品和库存归属于inventory检查和辅助表归属于ops。一、连接 kb_shop打开命令提示符进入工具目录cd /d D:\Tools\Kingbase\ES\Server\bin连接kb_shopksql -U system -d kb_shop -h localhost -p 54321确认当前数据库SELECTcurrent_database()AScurrent_db,current_userASlogin_user;预期结果二、查看当前已有模式执行\dn是ksql中查看模式列表的快捷命令。相比直接查询系统视图它更适合日常交互式检查而 SQL 查询更适合写进脚本或报表。两种方式并不冲突前者便捷后者可编程。\dn通常会看到public等模式。也可以使用 SQL 查询SELECTschema_nameFROMinformation_schema.schemataORDERBYschema_name;这两种方式都可以。\dn适合日常命令行查看SQL 查询适合写脚本和报表。三、创建业务模式为了后续文章形成清晰结构我们创建 3 个模式这一步不是为了“多分几个目录”而是提前建立业务边界。sales负责销售域对象inventory负责库存域对象ops负责运维辅助对象。后续讲权限时就可以做到只给某个用户访问某个模式而不是把整个数据库都开放出去。模式名用途sales客户、订单、订单明细等销售业务对象inventory商品、库存等库存业务对象ops巡检、脚本记录、辅助管理对象执行CREATESCHEMAIFNOTEXISTSsales;CREATESCHEMAIFNOTEXISTSinventory;CREATESCHEMAIFNOTEXISTSops;再次查看\dn可以看到为了让对象用途更清楚可以加备注COMMENTONSCHEMAsalesISCustomer and order business objects;COMMENTONSCHEMAinventoryISProduct and stock business objects;COMMENTONSCHEMAopsISOperation check and helper objects;四、理解 search_path创建了模式后会遇到一个问题如果执行下面的 SQLSELECT*FROMcustomer;数据库应该去哪一个模式下找customer表这就涉及search_path。查看当前搜索路径search_path决定了当 SQL 中没有写模式名时数据库按什么顺序查找对象。它是方便工具也是潜在风险。方便之处在于 SQL 可以写得短风险在于不同会话的search_path不一致时同一句 SQL 可能访问到不同对象。SHOWsearch_path;常见结果类似这表示当你不写模式名时数据库会按顺序查找对象。为了让后续操作更方便可以在当前会话设置SETsearch_pathTOsales,inventory,ops,public;再查看SHOWsearch_path;此时如果执行SELECT*FROMcustomer;数据库会优先在sales模式中寻找customer。五、完整对象名更适合文章演示虽然search_path很方便但在写技术文章、脚本和生产变更时我更推荐使用完整对象名完整对象名能降低上下文依赖。尤其是文章和教程读者的环境可能和我文章里写的不完全一致使用sales.customer这样的写法比只写customer更稳也更能体现数据库对象归属。sales.customer inventory.product ops.script_run_log这样读者一眼就知道对象属于哪里也能减少“当前搜索路径不一致”导致的报错。例如后续创建客户表时不写CREATETABLEcustomer(...);而写CREATETABLEsales.customer(...);查询也尽量写SELECT*FROMsales.customer;这会让文章更稳定读者复制 SQL 时也不容易受当前会话状态影响。六、创建一个 ops 检查表为了验证模式是否可用我们先在ops模式下创建一张检查表。CREATETABLEIFNOTEXISTSops.schema_check(idSERIALPRIMARYKEY,schema_nameVARCHAR(50)NOTNULL,check_resultVARCHAR(50)NOTNULL,checked_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);插入检查记录INSERTINTOops.schema_check(schema_name,check_result)VALUES(sales,created),(inventory,created),(ops,created);查询SELECTid,schema_name,check_result,checked_atFROMops.schema_checkORDERBYid;预期结果类似七、设置数据库级默认 search_path如果每次连接kb_shop后都手工执行SETsearch_pathTOsales,inventory,ops,public;会比较麻烦。可以给数据库设置默认搜索路径ALTERDATABASEkb_shopSETsearch_pathTOsales,inventory,ops,public;设置后断开重连\q重新连接ksql -U system -d kb_shop -h localhost -p 54321再查看SHOWsearch_path;如果结果中包含sales, inventory, ops, public说明数据库级默认配置生效。如果你不想设置数据库级默认值也可以不执行这一步。后续文章会尽量使用完整对象名避免依赖搜索路径。八、对象命名规范为了让后续文章风格统一系列文章采用以下命名规则类型规则示例数据库小写下划线kb_shop模式小写业务含义明确sales、inventory表小写单数名词或业务短语customer、product、customer_order字段小写下划线customer_id、created_at主键表名加_idproduct_id检查约束ck_表_字段ck_product_price唯一约束uk_表_字段uk_customer_phone外键约束fk_子表_父表fk_order_customer这种命名方式的好处是可读性高后续排查约束报错时也更容易定位。九、常见问题排查问题 1报错 schema does not exist如果执行CREATETABLEsales.customer(...);却提示schema sales does not exist说明模式还没创建。先执行CREATESCHEMAIFNOTEXISTSsales;再建表。问题 2查询表时报 relation does not exist可能原因是表在某个模式下但查询时没有写完整对象名。错误示例SELECT*FROMcustomer;推荐写法SELECT*FROMsales.customer;或者先设置SETsearch_pathTOsales,inventory,ops,public;问题 3search_path 设置了但没有生效如果执行了ALTERDATABASEkb_shopSETsearch_pathTOsales,inventory,ops,public;但当前窗口里SHOW search_path;仍然没变原因通常是数据库级配置对新连接生效。处理方式\q重新连接后再查看。问题 4不确定某张表在哪个模式可以查询系统信息SELECTtable_schema,table_nameFROMinformation_schema.tablesWHEREtable_nameschema_checkORDERBYtable_schema;如果后续对象多了这个查询很实用。十、本文小结本文承接kb_shop数据库的创建完成了数据库内部第一层对象治理Schema 分层。通过sales、inventory、ops三个模式我们把后续业务对象按照职责分开避免所有表都堆在public中。本文在kb_shop中完成了业务模式规划sales 客户、订单 inventory 商品、库存 ops 检查、辅助管理 public 默认模式尽量少放业务对象同时我们掌握了\dn CREATE SCHEMA COMMENT ON SCHEMA SHOW search_path SET search_path ALTER DATABASE ... SET search_path 完整对象名访问下一篇会开始创建真正的业务表客户表、商品表、订单表、订单明细表并重点讲字段类型、主键、唯一约束、检查约束和外键约束。也就是说第四篇完成了对象分层第五篇会进入关系建模和数据完整性设计。
