数据库开发规范

数据库开发规范

一.表结构

• 表和列的名称必须控制在32个字符以内，表名、列名只能使用字母、数字和下划线，一律小写。
• 建表必须有comment注释，包括字段和表。
• 数值表主键为业务名缩写+id，如department_id，类型为int或bigint，使用雪花算法生成值。
• 禁用保留字，如：desc、range、match、or、where、if、else等，详见SQL Server、MySQL、PostgresSql等数据库官方保留字。
• 反范式设计：把经常需要join查询的字段，在其他表里冗余一份，减少join查询。
• 表名不使用复数名称，用原始词即可。
• 业务过程中使用的中间表，名称必须以tmp开头，备份表用于备份或抓取源表快照，名称必须以bak开头，中间表和备份表定期清理。
• 业务数据表必备字段：create_time,update_time,create_by,update_by,create_name,
  update_name, is_delete，这些字段起到似metadata的作用，一些关系表、不做更新操作的表可以不需要。

二.字段

• 表中的自增列（auto_increment属性），使用bigint类型。因为无符号int存储范围为-2147483648~2147483647（大约21亿左右），溢出后会导致报错。

• 表中字段根据业务可以设置NULL或者NOT NULL属性，定义DEFAULT值。

• 文本数据尽量用varchar存储，因为varchar是变长存储，比char更省空间，MSSQL用nvarchar。

• 小数类型为decimal，禁止使用float和double，因为float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。

• 字典值、编码字段，名称字段统一使用30个字符长度

三. 索引

• 主键索引的名称以"pk_"开头，唯一键索引以"uq"开头，普通索引以"idx"开头，一律使用小写格式，以表名/字段的名称或缩写作为后缀。

• 在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。

• 建表或加索引时，保证表里互相不存在冗余索引，如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除。

• 每个数据表索引数据控制在7个以内，不允许超出。

四.字符集

• 数据库本身库、表、列所有字符集必须保持一致，统一为utf8。
• 程序字符集或者环境变量中的字符集，与数据库、表的字符集必须一致，统一为utf8。

五.DML语句

• select语句必须指定具体字段名称，禁止写成*。
• insert语句指定具体字段名称，不要写成insert into t1 values(…)。
• in值列表限制在500以内。
• where子句中禁止只使用全模糊的LIKE条件进行查找，必须有其他查询条件，否则无法利用索引。
• 减少使用or语句，可将or语句优化为union，然后在各个where条件上使用索引。
• 分页查询，当limit起点较高时，可先用过滤条件进行过滤。如select a,b,c from t1 limit 10000,20;优化为: select a,b,c from t1 where id>10000 limit 20;。
• 建议将复杂查询sql拆开结合程序多次查询，尽量使用join来代替子查询。