MySQL数据库开发规范

本文描述数据库表的设计流程以及DDL和DML的设计规约；

表设计流程

1. 表设计：用StarUML等UML建模工具中的ER图来设计表结构；
2. 表创建：导出DDL语句（yyyyMMddHHmmss_.sql）至项目的Flyway数据迁移目录，启动项目完成表的初始化；
3. ORM代码生成：
   • Idea中安装插件free-mybatis-plugin；
   • 在database视图，连接db，选择生成的表右击选择mybatis-generator，在gui界面配置package和目录，完成生成DAO层的DaoMapper接口和Mapper.xml文件；

DDL设计规约

存储引擎

通常不需要手动设置数据库存储引擎（如innodb,myisam），按数据库默认值即可；

• 原因：客户的数据库可能是基于MySQL/MariaDB封装了自己的存储引擎，如果指定了会导致flyway初始化异常；

字符集

新系统的数据库本身库、表、列所有字符集必须保持一致，使用utf8mb4；

• 示例：create table(...) default charset = utf8mb4；
• 原因：utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符；采用utf8mb4编码的好处是存储与获取数据的时候，不用再考虑emoji字符的编码与解码问题；
• 注意：utf8mb4的最低mysql版本支持版本为5.5.3+；

字段类型和长度

• 整数类型
  • int：次数，状态等类型字段存储
• 小数类型
  • decimal：禁止使用float和double。float和double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。
• 布尔类型
  • unsigned_tinyint(1)：1表示true，0表示false
• 文本类型
  • 短字符：varchar(64)，如name,code,id
  • 中字符：varchar(190)，如description
    • 需要建索引的字段，长度最好不超过190，因为utf8mb4编码单个索引最大长度是767字节（767/4约等于191）
  • 短文本：text，最大64kb
  • 中文本：mediumtext，最大16MB，如存储HTML页面内容
• 时间类型
  • 记录经常变化的时间：timestamp，时间范围（1970-2038），如更新/创建/发布/日志时间/注册时间等，并且是近来的时间，够用，区自动处理，比如说做海外购或者业务可能拓展到海外；
  • 记录固定时间：datetime，时间范围（1000-9999），如服务器执行计划任务时间/健身锻炼计划时间等，在任何时区都是需要一个固定的时间要做某个事情；
  • 自定义时间字段：bigint，不确定的时间范围存储；

命名规则

库名、表名、列名的修改代价很大，命名时需要慎重考虑

公共命名规则

1. 【强制】表名、列表都必须有comment注释；
2. 【强制】表名、列名只能使用字母、数字和下划线，禁止出现数字开头，一律小写；

库名

库名与应用名称尽量一致。

表名

1. 【强制】表的命名规则：业务名_表的作用，如scheduler_job。
2. 【强制】表名带当前系统前缀，如mlp_，便于后续数据平台聚合使用；
3. 【强制】表名不使用复数名词。

列名

1. 【强制】列的命名规则：实体类型_字段含义，如user_name、user_id。
2. 【强制】FK外键保持原字段名，方便join；
3. 【强制】字段名禁用数据库保留字；
4. 表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。

索引名

1. 【强制】主键索引：uk_字段名；
2. 【强制】普通索引：idx_字段名；
3. 【强制】唯一索引：uk_字段名；

表必备字段

1. 【强制】{entity}_id：主键
2. 【强制】create_time：记录创建时间，date_time，数据库默认值；
3. 【强制】update_time：记录更新时间，date_time，数据库自动设置；
4. is_deleted：unsigned_inyint(1)，是否删除，需支持软删除的表必备；
5. create_by：创建人，varchar(64)，需权限控制的数据表必备；
6. update_by：更新人，varchar(64)，需权限控制的数据表必备；

DML设计规约

1. 【强制】alter table：对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行；
   • 因为alter table会产生表锁，期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。
2. 【强制】select或insert语句必须指定具体字段名称，禁止写成*；
   • 因为select *会将不该读的数据也从MySQL里读出来，造成网卡压力。且表字段一旦更新，但model层没有来得及更新的话，系统会报错；
3. 【强制】除静态表或小表(100行以内)，DML语句必须有where条件
4. 【强制】where条件需用explain确认会使用索引；索引至少要达到 range 级别，要求是ref级别，如果可以是consts最好；
5. 【强制】where条件中等号左右字段类型必须一致，且不要使用函数或表达式，否则无法利用索引；
6. 【强制】where条件中禁止只使用全模糊的like条件进行查找，必须有其它等值或范围查询条件，否则无法利用索引。
7. select语句不要使用union，推荐使用union all，并且union子句个数限制在5个以内；
   • 因为union all不需要去重，节省数据库资源，提高性能；