MySQL数据库设计规范

参考阿里巴巴MySQL数据库设计规范，详细信息可查看《阿里巴巴Java开发手册》第五章。

一、建表规约

1. 表达是否概念的字段，必须使用 is_xxx 的方式命名，数据类型必须为unsigned tinyint(1)；

   例如：是否是删除状态应该使用 is_delete ；

2. 数据库名、表名、字段名只能使用 小写字母（a-z）、划线（_） 、阿拉伯数字（0-9）组成，禁止使用中文、大写字母、特殊符号、mysql关键字`命名。

   例如：正例 user_name0，反例 userName0

   附《MySQL》关键字大全：

   https://dev.mysql.com/doc/refman/5.7/en/keywords.html
   

3. 表名应以 模块名_表作用 命名；

   例如：系统权限表 sys_permission，车辆保险表 card_insurance，支付配置表 pay_config

4. 主键索引（primary key）的索引名为 pk_字段名，唯一索引（unique key）的索引名为 uk_字段名，

   普通索引（index）的索引名为 idx_字段名。

5. 存储小数使用decimal，如果精度超过限制的，可把整数和小数分开存储。

   例如：我们存储 124.123，数据类型定义为：decimal(16,3)

6. 固定长度的应使用 char(n)，n表示存储的字符数。

   提示：char里不要存储汉字，一般用于存储手机号码，电话号码，身份证之类的长度相对比较固定的数据。

7. varchar 为可变长度的字符串，存储长度不要超过5000字符，超过的应定义为text，并且独立存储。

   常用varchar长度参考：

   姓名：varchar(75)
   民族：varchar(16)
   政治面貌：varchar(16)
   企业名称：varchar(64)
   地址：varchar(255)
   记录备注：varchar(255)
   

8. 存储金额可以转化为分存储，存储类型 10位以内使用int(11)，超过 10位以上使用bigint(21)；

   提示：bigint(21) 占用空间8Bytes，int(11) 占用空间4Bytes

9. 表中必须包含4个字段 id，gmt_create，gmt_modified，is_delete 字段；

   提示：其中id为primary key、unsigned、bigint，gmt_create为记录创建时间，gmt_modified记录修改时间，is_delete 记录是否逻辑删除；gmt_create、gmt_modified均为datetime类型。

二、索引规约

1. 具有唯一特性的字段，必须建唯一索引（墨菲定理：坏可能一定会发生）。

2. join禁止超过3个表，需要join字段类型必须绝对一致，多表关联查询，被关联的字段需要有索引。

3. 在 varchar 上建立索引时，必须指定索引的长度。

   语法：
   CREATE INDEX index_name ON table_name (column_name(length), clolumn_name(length)…)；
   
   计算区分度：column_name如果设置length为5时，区分度为计算如下，
   select count(distinct left(column_name, 5))/count(*) from table_name;
   

4. 在建索引时区分度最高的在最左边。

   例如：在t表中，a、b、c字段的区分度从大到小为 b>a>c ，那么建索引的顺序为 b_a_c；需要注意的是，如果出现等号非等号的混合判断，等号的列必须放在索引的前列，如：where a>? and b=?，即使a的区分度在高b也要在索引的前列。

三、SQL语句规约

1. 不要使用 count(column_name)、count(num)来代替count(*)，count(*)是SQL92标准统计行数的语法。

2. count(column_name)时，如果column_name中包含NULL值的，会被忽略；

3. sum(column_name)如果column_name列中全是NULL，则返回NULL，为了防止查询出NULL值可使用IF判断来设置默认值

   select IF(ISNULL(column_name),0,sum(column_name)) as count_num from table_name
   

4. 必须使用ISNULL(column_name)来判断是否为NULL值，是则返回1，否则返回0 ；

   例如：查询business_bill表里arrears_amount值为null的记录

    select * from business_bill where ISNULL(arrears_amount)=1;
   

5. 禁止使用视图、存储过程、触发器，因为难以调试和扩展。

6. 在删除和修改数据之前，必须先查询出数据，防止数据误删。

7. 在使用in操作符时，in括号里面的数量小于1000。

四、ORM映射

1. 在mybatis中，禁止使用 * 号作为查询列表，要写出查询的字段。

2. xml文件中参数不要使用${} ，防止sql注入。

3. 修改记录时必须修改gmt_modified为当前时间。

4. 在使用事务@Transactional时，要考虑各方面的事务回滚，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等操作。