MySQL 规范
本文约定 ECShopX Java 后端（MyBatis-Plus、Flyway）使用 MySQL 时的建表、索引与 SQL 编写规范。数据库默认字符集建议使用 utf8mb4，时区 Asia/Shanghai。
一、建表规约
【强制】表达是与否概念的字段，必须使用 is_xxx 命名，类型为 unsigned tinyint
- 1 表示是，0 表示否。
- 任何字段如果为非负数，必须是
unsigned。 - 说明：Domain 实体中的 boolean 字段若不加
is前缀，需在 MyBatis@TableField或<resultMap>中配置is_xxx→ 属性名的映射。数据库侧仍坚持is_xxx命名，以明确取值含义与范围。 - 正例：逻辑删除字段
is_deleted，1 表示删除，0 表示未删除。
【强制】表名、字段名必须使用小写字母或数字
- 禁止数字开头；禁止两个下划线中间只出现数字。
- 字段名修改代价大（难以预发布），命名需慎重。
- 说明：MySQL 在 Windows 下不区分大小写，Linux 下默认区分。库名、表名、字段名均不允许大写字母。
- 正例：
aliyun_admin，rdc_config，level3_name - 反例：
AliyunAdmin，rdcConfig，level_3_name
【强制】表名不使用复数名词
- 表名仅表示实体内容，不表示数量；对应 Domain 类名也为单数。
【强制】禁用保留字
- 如
desc、range、match、delayed等，参考 MySQL 官方保留字。
【强制】索引命名
| 类型 | 命名 |
|---|---|
| 主键 | pk_字段名 |
| 唯一 | uk_字段名 |
| 普通 | idx_字段名 |
pk_ = primary key；uk_ = unique key；idx_ = index。
【强制】小数类型使用 decimal，禁止 float 和 double
- 存储与比较时
float/double存在精度损失。 - 超出
decimal范围时，建议拆成整数与小数分开存储。
【强制】长度几乎相等的字符串使用 char
【强制】varchar 长度不要超过 5000
- 超过则定义为
text，独立成表，用主键关联，避免影响其它字段索引效率。
【强制】表必备三字段：id、create_time、update_time
id必为主键，类型bigint unsigned；单表自增、步长 1。create_time、update_time均为datetime：前者表示主动创建，后者表示被动更新。
【推荐】表命名遵循「业务名称_表的作用」
- 正例：
alipay_task/force_project/trade_config
【推荐】库名与应用名称尽量一致
【推荐】修改字段含义或追加状态时，及时更新字段注释
【推荐】字段允许适当冗余以提高查询性能
须考虑数据一致，冗余字段应满足：
a. 不是频繁修改的字段
b. 不是唯一索引的字段
c. 不是超长
varchar，更不能是text正例：各业务线冗余存储商品名称，避免查询时再调外部服务。
【推荐】分库分表阈值
单表行数超过 500 万 或单表容量超过 2GB 时再考虑分库分表。若三年内达不到该量级，建表时不要提前分库分表。
【参考】字符类型与存储长度
合适的字符存储长度可节约空间与索引，并提升检索速度。无符号类型可避免误存负数并扩大表示范围。
| 对象 | 年龄/时间区间 | 类型 | 字节 | 表示范围 |
|---|---|---|---|---|
| 人 | 150 岁以内 | tinyint unsigned | 1 | 0 ~ 255 |
| 龟 | 数百岁 | smallint unsigned | 2 | 0 ~ 65535 |
| 恐龙化石 | 数千万年 | int unsigned | 4 | 0 ~ 约 43 亿 |
| 太阳 | 约 50 亿年 | bigint unsigned | 8 | 0 ~ 约 10^19 |
二、索引规约
【强制】业务上具有唯一特性的字段必须建唯一索引
- 即使是组合字段也要建唯一索引。
- 应用层校验再完善，没有唯一索引仍可能产生脏数据（墨菲定律）。
【强制】超过三个表禁止 join
- 需要 join 的字段数据类型必须绝对一致。
- 多表关联时被关联字段必须有索引。
- 双表 join 也需注意索引与 SQL 性能。
【强制】varchar 上建索引必须指定索引长度
- 不必对全字段建索引；按文本区分度决定长度。
- 一般字符串长度为 20 的索引，区分度可达 90% 以上。
- 可用
count(distinct left(列名, 索引长度)) / count(*)估算区分度。
【强制】页面搜索严禁左模糊或全模糊
- 需要模糊搜索请走搜索引擎。
- 索引具有 B-Tree 最左前缀特性，左值未确定则无法使用该索引。
【推荐】order by 利用索引有序性
order by字段放在组合索引最后，避免filesort。- 正例：
where a=? and b=? order by c，索引a_b_c - 反例：
where a>10 order by b，索引a_b无法排序。
【推荐】利用覆盖索引查询，避免回表
- 正例：
explain的extra出现using index即覆盖索引效果。
【推荐】延迟关联或子查询优化超大分页
- MySQL 会取
offset+N行再丢弃前offset行；offset很大时效率极低。 - 控制总页数，或对超大页改写 SQL。
- 正例：
sql
SELECT t1.*
FROM 表1 AS t1,
(SELECT id FROM 表1 WHERE 条件 LIMIT 100000, 20) AS t2
WHERE t1.id = t2.id;【推荐】SQL 性能优化目标
至少 range，要求 ref，最好 const：
- const：单表最多一行匹配（主键或唯一索引），优化阶段即可读到数据。
- ref：使用普通索引（normal index）。
- range：索引范围扫描。
反例：type=index 为索引全扫描，比 range 还慢，接近全表扫描。
【推荐】组合索引：区分度最高的列在最左
- 正例：
where a=? and b=?且a接近唯一，单建idx_a即可。 - 存在非等号与等号混合时，等号列前置。如
where c>? and d=?，即使c区分度更高，也应建idx_d_c。
【推荐】避免隐式类型转换导致索引失效
【参考】创建索引的常见误区
- 索引宁滥勿缺——认为每个查询都要建索引。
- 吝啬索引——认为索引拖慢写入。
- 抵制唯一索引——一律在应用层「先查后插」。
三、SQL 语句
【强制】统计行数使用 count(*)
- 不要用
count(列名)或count(常量)替代。 count(*)是 SQL92 标准，与 NULL 无关；count(列名)不统计该列为 NULL 的行。
【强制】count(distinct col) 语义
- 统计该列除 NULL 外的不重复行数。
count(distinct col1, col2)中若一列全为 NULL，即使另一列不同也返回 0。
【强制】sum(col) 注意 NPE
- 列全为 NULL 时
count(col)为 0，但sum(col)为 NULL。 - 正例：
SELECT IFNULL(SUM(column), 0) FROM table;
【强制】判断 NULL 使用 ISNULL() 或 IS NULL
- NULL 与任何值比较结果均为 NULL，不是 true/false。
NULL <> NULL、NULL = NULL、NULL <> 1结果均为 NULL。- 反例：
select * from t where column1 is null and column3 is not null在 null 前换行影响可读性；ISNULL(column)更简洁，执行效率通常更好。
【强制】分页查询时若 count 为 0 应直接返回
- 避免执行后续分页语句。
【强制】不得使用外键与级联
- 外键与级联更新适用于单机低并发，不适合分布式、高并发；级联更新强阻塞，有更新风暴风险；外键影响插入速度。关联逻辑在应用层实现。
【强制】禁止使用存储过程
- 难以调试、扩展与移植。
【强制】数据订正（删除/修改）前先 select
- 确认无误再执行更新，避免误删。
【强制】多表查询/变更时对列名加表别名限定
- 正例：
SELECT t1.name FROM table_first AS t1, table_second AS t2 WHERE t1.id = t2.id; - 反例：多表关联未限定表名，后续某表新增同名列会导致
Column 'name' in field list is ambiguous。
【推荐】表别名使用 AS，命名为 t1、t2、t3…
【推荐】in 操作能避免则避免
- 若无法避免，集合元素控制在 1000 个以内。
【参考】字符集与计数
国际化场景使用 utf8mb4。注意 LENGTH 与 CHARACTER_LENGTH 区别：
sql
SELECT LENGTH('轻松工作'); -- 12（字节）
SELECT CHARACTER_LENGTH('轻松工作'); -- 4（字符）