---
name: "p3c-mysql-database"
description: "Provides MySQL database coding standards including table creation, SQL statements, indexing, and ORM mapping rules. Invoke when user asks about MySQL best practices, database design, or SQL optimization."
---

# P3C MySQL数据库规范

本技能提供阿里巴巴Java开发手册中的MySQL数据库相关规范，包括建表规约、SQL语句、索引规约和ORM映射。

## 建表规约

### 字段要求 *** 强制执行 ***
1. 【强制】表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（1表示是，0表示否）
2. 【强制】必须有一个id字段，作为主键，自增，无符号
3. 【强制】必须有一个业务id字段，用于关联业务实体，唯一索引，varchar(32)
4. 【强制】必须有一个创建时间字段，记录数据创建时间，`create_time`：datetime
5. 【强制】必须有一个更新时间字段，记录数据最后更新时间，`update_time`：datetime
6. 【强制】如果是假删除，必须有删除标记字段，0：正常、1：已删除，`delete_state`：tinyint(1)，索引，无符号
7. 【强制】如果是假删除，必须有删除时间字段，记录删除数据的时间，`delete_time`：datetime
8. 【强制】字段顺序是：id、业务id、业务字段、删除标记、创建时间、更新时间、删除时间
9. 【强制】字段：id、业务id、业务字段、删除标记、创建时间、更新时间、删除时间，直接输出不要自由发挥
10. 【强制】所有字段都必须有注释，说明字段的业务含义和有效值范围
11. 【强制】子表中的管理字段，必须有一个业务id字段，用于关联业务实体
12. 【强制】字段名称必须使用下划线命名法，所有字段名称必须小写
13. 【强制】字段命名：完整表名称_字段名称
14. 【强制】如此字段被别的表所使用，字段名必须一致
15. 【强制】文件只存储文件id，字段：`system_file_id`

### 表名要求
16. 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字
17. 【强制】表名不使用复数名词
18. 【强制】禁用保留字，如`desc`、`range`、`match`、`delayed`等
19. 【强制】主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名
20. 【强制】小数类型为decimal，禁止使用float和double
21. 【强制】如果存储的字符串长度几乎相等，使用char定长字符串类型
22. 【强制】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text
23. 【强制】表必备三字段：id, gmt_create, gmt_modified
24. 【推荐】表的命名最好是加上"业务名称_表的作用"
25. 【推荐】库名与应用名称尽量一致
26. 【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释
27. 【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致
28. 【推荐】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表
29. 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度

## SQL语句规约

1. 【强制】不要使用count(列名)或count(常量)来替代count(*)，count(*)是SQL92定义的标准统计行数的语法
2. 【强制】count(distinct col) 计算该列除NULL之外的不重复行数
3. 【强制】当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL
4. 【强制】使用`ISNULL()`来判断是否为NULL值
5. 【强制】在代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句
6. 【强制】不得使用外键与级联，一切外键概念必须在应用层解决
7. 【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性
8. 【强制】数据订正（特别是删除、修改记录操作）时，要先select，避免出现误删除
9. 【推荐】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内
10. 【参考】如果有全球化需要，所有的字符存储与表示，均以utf-8编码
11. 【参考】TRUNCATE TABLE 比 DELETE 速度快，但TRUNCATE无事务且不触发trigger，故不建议在开发代码中使用此语句

## 索引规约

1. 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
2. 【强制】超过三个表禁止join。需要join的字段，数据类型必须绝对一致
3. 【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引
4. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决
5. 【推荐】如果有order by的场景，请注意利用索引的有序性
6. 【推荐】利用覆盖索引来进行查询操作，避免回表
7. 【推荐】利用延迟关联或者子查询优化超多分页场景
8. 【推荐】SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好
9. 【推荐】建组合索引的时候，区分度最高的在最左边
10. 【推荐】防止因字段类型不同造成的隐式转换，导致索引失效

## ORM映射规约

1. 【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明
2. 【强制】POJO类的布尔属性不能加is，而数据库字段必须加is_，要求在resultMap中进行字段与属性之间的映射
3. 【强制】不要用resultClass当返回参数，即使所有类属性名与数据库字段一一对应，也需要定义
4. 【强制】sql.xml配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现SQL注入
5. 【强制】iBATIS自带的queryForList(String statementName,int start,int size)不推荐使用
6. 【强制】不允许直接拿HashMap与Hashtable作为查询结果集的输出
7. 【强制】更新数据表记录时，必须同时更新记录对应的gmt_modified字段值为当前时间
8. 【推荐】不要写一个大而全的数据更新接口
9. 【参考】`@Transactional`事务不要滥用
10. 【参考】`<isEqual>`中的compareValue是与属性值对比的常量，一般是数字