数据库开发规范
修订记录
| 版本号 | 日期 | 状态 | 修订人 | 摘要 |
|---|---|---|---|---|
| 0.0.1 | 2022/03/28 | C | 张秀仕 | 创建文档 |
| 0.0.2 | 2022/06/06 | M | 王小露 | 补充正反例及相关说明，数据库设计规范相关条目进行分类，增加表命名单数、临时表命名、关联表设计、主键、联合主键、唯一索引、单表索引数目、活跃表、insert及select操作、mybatis防注入条目，修改自定义函数条目 |
数据库设计规范
库
-
【建议】数据库名与应用名称尽量一致
应用名称:admin 正例：数据库名称:koca_admin 反例：数据库名称:koca_admin1,koca_studio -
【建议】库名，表名，字段名使用英文字母，数字，下划线，并以英文字母开头，不使用驼峰命名，禁止出现数字开头，禁止两个下划线中间只出现数字
正例：koca_user_info_base,level3_name 反例：kocaUserInfoBase,level_3_name -
【建议】数据库对象表名、字段名、约束名长度不超过30
Oracle数据库，从12.2.0.1.0版本开始，数据库对象长度支持为128，在此之前的版本为30
表
-
【强制】表名、字段名禁用保留字，禁用数据库关键字
MYSQL保留字：ACCESSIBLE,ADD,ALTER,AS,ASC等 ORACLE保留字：ACCESS，ADD，ALL，ALTER等 ORACLE关键字：ADMIN，CURSOR，FOUND，MOUNT，AFTER，CYCLE，FUNCTION等 MYSQL关键字：ACCOUNT，ACTION，AFTER，AGAINST等 -
【建议】表的命名按照"模块业务名称_表的作用"
正例：koca_user_info_base 反例: koca_info_base, user_info_base -
【建议】表命名应以单数形式，而非复数
-
【建议】临时表命名以
_日期_tmp结束,备份表命名原表名_日期_bak，日志表以_log结束。 -
【建议】关联表设计：表之间的关联字段应该尽量少。关联字段的数据类型应该保持一致。关联字段在命名上，应该可以明显体现出关联关系。例如，采用同样名称来命名。
-
【建议】表与表之间的相同字段允许适当冗余，以提高查询性能，但必须考虑数据一致性
冗余字段应遵循: 1) 不是频繁修改的字段。 2) 不是 varchar 超长字段，更不能是 text 字段。 3) 不是唯一索引的字段。 正例:商品类目名称使用频率高，字段长度短，名称基本不变，可在相关联的表中冗余存储类目名称，避免关联查询
约束
-
【建议】主键索引名为
pk_表名_字段名；唯一索引名为uk_表名_字段名；普通索引名则为idx_表名_字段名。主键名使用单独SQL定义，不在字段名后面使用primary关键字指定说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称 给 ACT_HI_PROCINST 表的 BUSINESS_KEY_ 字段添加索引 正例：IDX_HI_PRO_HI_BUSKEY 反例：IDX_HI_PRO_HI_C4971C0F4BC891F9,IDX_C4971C0F4BC891F9 -
【建议】表必须有主键，且主键禁止被更新
-
【建议】不使用联合主键
存储实体数据的表，不使用联合主键。存储实体表间多对多关系的表允许例外。 当使用联合主键时，其它库或者表引用时，会同时引用多列，增加维护成本，稳定性降低。 -
【建议】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
说明:不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的;另外， 即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。 -
【建议】单表索引数量不超过5个，单个索引中的字段不超过5个
字段
-
【建议】建表语句必须完善字段注释
正例：noid varchar(32) not null comment '序列号id' 反例：noid varchar(32) not null -
【建议】定义合适的字符长度，字符串字段长度尽量保证是 2 的倍数
使用2 ^ n长度是更好的磁盘或内存块对齐 -
【建议】活跃表中不使用大字段，如blob、text、varchar(2048)等
SQL编写规范
-
【强制】SQL中的表名、字段名大小写与建表语句保持一致
说明：程序中的sql字段大小写跟建表语句大小写不一致，可能会导致sql操作报错 -
【强制】查询涉及多个表，都需要在列名前加表的别名（或表名）
说明：如果对操作列前没有加别名（或表名），并且操作列在多表中存在时，会发生错误 -
【强制】SQL 语句中字段别名前加
as -
【强制】delete 操作必须跟
from关键字说明：MYSQL数据库只有delete from table可以执行，delete table无法执行。而ORACLE数据库两者均可以执行 -
【强制】函数名后面不能带空格，如COUNT (1）改为 COUNT(1）
说明：MYSQL数据库函数名称跟(之间不允许有空格,否则会执行报错 -
【建议】避免使用数据库专有函数或特性，如Oracle的hint
说明：防止以后对接其它数据库时出现问题 -
【建议】select只获取必要的字段,禁止使用select *
-
【建议】insert语句指定具体字段名称，禁止写成insert into t1 values(…)
函数使用规范
-
【强制】不要使用存储过程和视图，原因：存储过程难以调试和扩展、更没有移植性
-
【建议】不要使用某个数据库特有的函数，便于项目适配不同数据库，如Oracle的hint
说明：防止后续对接其它数据库时出现问题 -
【建议】函数同名但是用法不同时，尽量避免使用；
说明：防止后续对接其它数据库时出现问题 -
【建议】不使用数据库的自定义函数。函数问题通过KOCA异构数据库兼容方案解决。以
DB_XXX定义函数，在sql层面进行函数转化TDSQL官方文档表示： 不支持自定义函数、事件、表空间 不支持视图、存储过程、触发器、游标 -
【建议】不使用外键与级联，在应用层解决
说明:以机构和人员的关系为例，机构表中的 org_code 是主键，那么人员表中的 org_code 则为外键。如果更新机构表中的 org_code，同时触发人员表中的 org_code 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群;级联更新是强阻塞，存在数据库更新风暴的风险;外键影响数据库的插入速度 TDSQL官方文档表示： 不支持外键、自建分区
Mybatis使用规范
-
【强制】入参数类型如果使用对象，指定类的全路径
不建议使用mybatis别名，需要额外增加mybatis配置type-aliases-package，且在类名相同的情况下易出错。尽量去使用实体类的全限定类名，这样会减少程序出现问题 -
【强制】sql.xml 配置参数使用#{}，不要使用${}，此种方式容易出现 SQL 注入
-
【建议】返回对象尽量使用ResultMap做属性映射
配置映射关系，使字段与 DO 类解耦，方便维护 -
【建议】不直接使用Mybatis的原生标签进行数据批量操作。建议使用KOCA框架提供的mybatis自定义批量操作标签
<batch>来解决，或者KOCA框架提供的JdbcHelper中提供的batchUpdate方法解决说明：通过mybatis <insert> 进行数据的批量插入，一方面，不同的数据库插入sql不同，需要写多份sql，另一方面，原生插入效率较低 -
【建议】使用成熟、依然在维护的分页插件（PageHelper），不单独封装分页工具
说明：自行封装分页工具需考虑异构数据库问题
分片表设计规范
-
【建议】不要轻易修改已创建好的分片键
-
分片键选择
分片键是在水平拆分过程中用于生成拆分规则的数据表字段，必须在建表时指定好。TDSQL 建议分片键尽可能找到数据表中的数据在业务逻辑上的主体，并确定大部分（或核心的）数据库操作都围绕这个主体的数据进行，方可使用该主体对应的字段作为拆分键，进行分表（该分表方案名为 Group-Shard）
- 分片操作
TDSQL 目前只支持单个分片键下的 JOIN 和 TRANSACTION，以及跨节点的 TRANSACTION，跨节点的 JOIN 暂不支持
- 分片键变更
一旦选择好分区字段（shardkey），就不能轻易更改。若需要修改一个表的分区字段，只能新建一个表。 若需要修改一个分表某一行中的 shardkey 值，需要先 insert 再 delete。直接操作 update 不能修改分区字段的值