MySQL开发规范

一、命名规范

1. 命名要见名知意，全部使用小写字母，多个单词之间使用下划线连接

   禁止使用中文拼音！

   例：t_article_detail

2. database
   db_[数据库名]
   例如：db_hms
3. table
   t_[表名]
   例如：t_school
4. index
   idx_[普通索引]、uk_[唯一索引]
   例如：idx_uid、uk_record_id
5. view
   v_[视图名]
6. function（默认禁止使用）
   f_[方法名]
7. procedure（默认禁止使用）
   p_[存储过程名]
8. event（默认禁止使用）
   e_[事件名]

二、建表规范

1. 所有列名、表名都要有注释

2. 字符集统一使用utf8mb4，排序规则使用utf8mb4_general_ci（特殊例外）
   排序规则默认使用utf8mb4_general_ci，列值如果需要根据大小写过滤则配置对应列排序规则为utf8mb4_bi，如验证码、密码

### 字符集使用utf8mb4，默认使用utf8mb4_general_ci
CREATE TABLE t_test (
 id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
 val varchar(20) NOT NULL DEFAULT '' COMMENT '值',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

### 特殊列需要根据大小写过滤，配置列排序规则utf8mb4_bin
CREATE TABLE `t_test` (
 id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
 pwd varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '密码',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

3. 字段名禁止使用数据库保留关键字
   例如：date、datetime、desc、range、match、comment、add等
   所有MySQL保留关键字详见 mysql5.7关键字和保留字

4. 表的主键的命名建议依据表的含义+_id
   例如：用户表t_user 主键为user_id

5. 新建表需要创建字段
   【必选】创建时间create_time ,修改时间 update_time；【可选】是否可用 is_flag

 CREATE TABLE `t_test` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `is_flag` tinyint(2) unsigned NOT NULL DEFAULT '1' COMMENT '是否可用，1是，0否',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表'
;

6. 每个表都需要有主键，自增主键使用unsigned
   建议使用自增键作为主键，避免使用uuid、字符串等做主键，禁止无主键
7. 小数类型使用 decimal ，禁止使用 float 和 double
   说明：float 和 double 在存储的时候,存在精度损失的问题。decimal(M,D)，M表示总位数，D表示小数点后位数；
   例如：decimal(5,2)，整数部分可以存储3位，小数部分可以存储2位
8. 数值类型的字段只有正数时，指定unsigned

 CREATE TABLE `t_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '只有正数，指定unsigned'
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表'
;

9. 存储货币，类型使用整型，单位为分
10. 字段设置默认值，尽量避免空值null
11. 表之间的关联字段，字段类型和字符集要保持一致

#t_class的id和t_student的class_id关联，两者的字段类型和字符集要一致
select t_class.*,t_student.* 
from t_student  
 left join t_class on t_class.id=t_student.class_id;

12. 表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是unsigned tinyint( 1 表示是,0 表示否 ) 。
    说明:任何字段如果为非负数,必须是unsigned。
    例如:表达逻辑删除的字段名 is_deleted ,1 表示删除,0 表示未删除。
13. varchar长度限制
    建议不要超过5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来关联，避免影响其它字段索引效率。
14. 删除自增初始值
    说明：有的表结构通过测试环境导出，会含有AUTO_INCREMENT=N，请将其删除再提交。
15. 修改同一张多个字段，合并为单条SQL
    说明：经常有同事对同一张表添加多个字段，分成多条SQL，带来的影响是每执行一条DDL都会对该表重建和锁表。

#反例，添加t_task多个字段
ALTER TABLE t_task ADD identity_type  tinyint(4) DEFAULT '1' COMMENT '用户身份类型: 非会员:0、全员:1、白银会员:2、超级会员:3;';
ALTER TABLE t_task ADD online_time date DEFAULT NULL COMMENT '上线时间 格式： YYYY-MM-DD';
ALTER TABLE t_task ADD offline_time date DEFAULT NULL COMMENT '下线时间 格式： YYYY-MM-DD';

#正例
ALTER TABLE t_task 
  ADD identity_type  tinyint(4) DEFAULT '1' COMMENT '用户身份类型: 非会员:0、全员:1、白银会员:2、超级会员:3;',
  ADD online_time date DEFAULT NULL COMMENT '上线时间 格式： YYYY-MM-DD',
  ADD offline_time date DEFAULT NULL COMMENT '下线时间 格式： YYYY-MM-DD';

16. 有限类型选项，选择【够用】类型
    说明：不同类型取值范围不一样，有的字段取值就0、1、2，10以内，字段类型可选择为tinyint

    类型	取值范围（有符号）	取值范围（无符号）
    tinyint	-128~127	0~255
    smallint	-32768~32767	0~65535
    mediumint	-8388608~8388607	0~16777215
    int	-2147483648~2147483647	0~4294967295

17. 禁止使用外键

18. 基础字段长度标准
    说明：经常有表需要添加字段【操作人名称】(operation_name/user_name)、图片链接地址(url)等，可按此标准创建（需开发确认），防止字段长度过短被截取或报错。

    字段	建议长度	备注
    operation_name/user_name	varchar(50)	操作人名称
    url	varchar(200)	图片链接地址

19. 表限制 列数：单表列数小于30 行数：以三年内的数据量，超过 500 万行或者单表容量超过 2 GB ,建议进行分库分表。

20. 时间字段类型建议使用datetime，而不是timestamp
    说明：虽然timestamp占用空间比datetime小，但仅能存储到2038-01-19

21. 字段类型tinyint(1)建议更改为tinyint(2) 说明：括号中数字跟存储的大小范围无关，但canal会将tinyint(1)类型解析成bit导致同步错误。

22. DRDS分布式事务 drds支持2PC，但在同一个事务里，涉及insert的表都需要创建全局序列Sequence，否则会报错。(目前仅dbfileserver有使用)

三、索引规范

1. 索引命名
   以idx_开头，单个字段为列名全称，组合索引的列名之间通过下划线关联，列名内取消下划线

alter table t_test add index idx_student_id(student_id);  #单个字段，列名全称
alter table t_test add index idx_classid_studentid(class_id,student_id);  #多个字段，列名内取消下划线，列名之间通过下划线关联

2. 索引限制 单表索引个数建议在5个以内，单个索引字段不超过5个

3. 禁止左模糊搜索或全模糊搜索

#正例
select * from t_student where name like '中%';
 
#反例
select * from t_student where name like '%中';
select * from t_student where name like '%中%';

4. varchar字段建立索引，指定索引长度
   正常没必要对整个字段建立索引，一般长度20能区分90%数据。
   varchar长度在50以内，可以直接使用整个字段建立索引，超过50以后使用count(distinct left(列名, 索引长度))/count(*)的区分度来确认索引长度。

#name字段长度为100
alter table t_test add index idx_name(name(20));

5. 避免在区分度不高，频繁更新列上建立索引
6. 表数据量在1000以内，可不用建索引

四、SQL规范

1. 尽量使用简单SQL
   对复杂SQL进行拆分，化简成简单SQL，尽量减少多表关联。
2. 避免在索引列进行运算导致索引失效引发全表扫描

#反例：对列create_time进行date_format运算
select * from t_student where date_format(create_time,'%Y-%m-%d')='2021-02-24';
 
#正例：转换成对值的判断
select * from t_student where create_time >= '2021-02-24' and create_time < '2021-02-25';

3. 避免使用大事务造成执行锁定时间，同时导致备库只读节点数据延迟
4. 查询时显示指定要查询列，避免直接select *
5. 利用延迟关联或者子查询优化超多分页场景

#原始SQL
SELECT * FROM 表1 where 条件 LIMIT 100000,20;
 
#建议，延迟关联，先查询主键id，再通过关联出所有字段
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id;