MySQL 库表设计规范：金额用 decimal、状态用 tinyint、索引守住最左前缀
库表设计一旦上线，改字段类型的代价远高于改业务代码。这份规范覆盖建库参数、命名、标准字段、字段类型、约束、索引，最后给出用户、订单、订单明细三张可直接落地的建表语句。
一、整体设计原则（优先遵守）
- 范式适度：基础遵循第三范式，高频关联查询场景允许适当冗余，避免过度范式导致大量 JOIN。
- 冷热分离：大日志、历史归档数据单独分表 / 分库。
- 索引先行：建表同时规划索引，禁止后期随意新增大索引。
- 兼容扩展：预留扩展字段，禁止硬编码字段含义。
- 字符集统一：推荐
utf8mb4，支持 emoji；排序规则utf8mb4_unicode_ci。 - 引擎统一：业务表默认 InnoDB（事务、行锁、外键可选）；日志临时表可考虑 MyISAM（极少场景）。
❌ 避坑：不要使用 utf8（MySQL 的 utf8 ≠ 标准 utf8，最多 3 字节，不支持表情）。
二、数据库（Schema）设计规范
1. 库命名规范
小写字母 + 下划线，禁止大写、中文、特殊符号；格式：业务模块_db。
示例：order_db、user_db、goods_db。
2. 库参数标准配置（建库语句）
CREATE DATABASE IF NOT EXISTS user_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
3. 分库策略简单区分
- 垂直分库：按业务拆分（用户库、订单库、商品库）。
- 水平分库：单库数据量过大，同一张表拆分到多个库。
三、数据表设计规范
1. 表名规范
小写、下划线，名词复数形式；格式：模块_表名。
示例：user_info、order_main、order_item；禁止：UserInfo、userInfo、用户表。
2. 必备标准字段（所有业务表建议统一带上）
| 字段 | 类型 | 说明 |
|---|---|---|
| id | bigint unsigned NOT NULL | 主键自增主键（雪花 ID 可选，分布式） |
| create_time | datetime NOT NULL DEFAULT CURRENT_TIMESTAMP | 创建时间 |
| update_time | datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | 更新时间，自动刷新 |
| is_deleted | tinyint NOT NULL DEFAULT 0 | 逻辑删除：0 正常，1 删除（禁止物理删除） |
分布式系统：不建议自增主键，改用 BIGINT 雪花算法 ID。
3. 字段类型选型最佳实践
数字类型
- 主键 ID：
bigint unsigned（不要 int，数据量容易溢出）。 - 状态标识：
tinyint0/1/2/3（不要 char/varchar 存状态）。 - 金额：
DECIMAL(m,n)，严禁 float/double（浮点精度丢失），例：decimal(12,2)。 - 普通数量：
int；超大数量用bigint。
字符串类型
- 短标识、编码：
varchar(32)/varchar(64)。 - 名称、标题：
varchar(255)。 - 超长文本、备注：
text（尽量少用，无法建立普通索引）。 - varchar 长度够用即可，不要统一全部 255。
时间类型
- 时间点：
datetime（推荐，可读性强，不受时区影响）。 - 仅日期：
date；仅时分秒：time。 - 不推荐使用
timestamp（时区限制、2038 上限）。
布尔逻辑：不用 bool，统一 tinyint(1)，0 = 否，1 = 是。
4. 字段命名规范
小写下划线，见名知意：user_name、phone、order_amount；状态统一后缀 _status：order_status、pay_status；数量统一后缀 _num：buy_num。
5. 约束规范
- 主键 PRIMARY KEY：单一主键优先 id；尽量避免联合主键。
- 唯一索引 UNIQUE：手机号、用户名、订单号等唯一字段。
- NOT NULL 优先：能不为 NULL 尽量 NOT NULL，设置默认值；NULL 会让索引和比较多出一层判断开销。
- 谨慎使用外键：InnoDB 支持外键，但高并发业务不推荐，由应用程序保证数据一致性。
四、索引设计规范（重中之重）
1. 索引分类
主键索引 PRIMARY KEY(id)；普通索引：单字段 / 联合索引；唯一索引 UNIQUE KEY uk_phone(phone)；前缀索引：超长字符串使用（如 url）。
2. 联合索引最左匹配原则
建立 idx_name_status(user_name, order_status)：
- ✅ 可以命中：
where user_name=?/where user_name=? and order_status=? - ❌ 无法命中：
where order_status=?
3. 索引避坑清单
- 不要在低基数字段单独建索引（如性别、is_deleted）。
- 索引字段禁止函数操作：
where DATE(create_time)='xxx'→ 失效。 - 不要超量建索引：写入（insert/update/delete）会同步维护索引。
- 字符串匹配禁止左模糊
like '%xxx'，索引失效；like 'xxx%'可行。
五、完整建表示例
用户主表 user_info：
CREATE TABLE `user_info` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_name` varchar(64) NOT NULL COMMENT '用户名',
`phone` varchar(32) NOT NULL COMMENT '手机号',
`password` varchar(128) NOT NULL COMMENT '加密密码',
`user_status` tinyint NOT NULL DEFAULT 1 COMMENT '用户状态 0禁用 1正常',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`is_deleted` tinyint NOT NULL DEFAULT 0 COMMENT '逻辑删除 0未删 1已删',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_phone` (`phone`),
KEY `idx_user_status` (`user_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户信息表';
订单主表 order_main：
CREATE TABLE `order_main` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单主键',
`order_no` varchar(64) NOT NULL COMMENT '订单编号',
`user_id` bigint unsigned NOT NULL COMMENT '用户id',
`order_amount` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
`order_status` tinyint NOT NULL DEFAULT 0 COMMENT '订单状态',
`pay_status` tinyint NOT NULL DEFAULT 0 COMMENT '支付状态',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`is_deleted` tinyint NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_userid_status` (`user_id`,`order_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单主表';
订单明细表 order_item（一对多拆分）：主表存订单整体信息，明细表存储多条商品，禁止用逗号拼接商品 ID 存在一个字段。
CREATE TABLE `order_item` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`order_id` bigint unsigned NOT NULL COMMENT '订单主表id',
`goods_id` bigint unsigned NOT NULL COMMENT '商品id',
`goods_name` varchar(255) NOT NULL COMMENT '商品名称快照',
`price` decimal(12,2) NOT NULL COMMENT '下单单价',
`buy_num` int NOT NULL DEFAULT 1 COMMENT '购买数量',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`is_deleted` tinyint NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单明细表';
六、常用设计最佳实践
- 一对多关系：用户 1 → 订单 N，订单表保存
user_id。 - 多对多关系：用户 ↔ 角色，新建中间表
user_role_rel，存储user_id、role_id，联合唯一索引。 - 字典 / 常量：状态含义不要写死在 SQL 注释，建议建立字典表
sys_dict，代码读取。 - 大文本：文章正文、富文本单独一张表，避免主表查询加载大字段。
- 分页查询：尽量基于有序索引字段分页，避免
limit 1000000,10深度分页——偏移量越大，扫描后丢弃的行越多，代价越高。
七、开发规范红线（禁止操作）
- ❌ 表、字段使用中文。
- ❌ 使用 TEXT/BLOB 建立普通索引。
- ❌ 使用 float/double 存储金额。
- ❌ 大量使用 NULL，不设置默认值。
- ❌ 业务数据物理 DELETE。
- ❌ 一条字段存储多条数据（逗号分隔 id）。
- ❌ 随意创建联合索引，不遵守最左前缀。
- ❌ 在线业务直接执行 ALTER TABLE 锁表操作。
八、延伸方案
- 数据量大：水平分表（按 user_id 哈希、时间范围分表）。
- 读写压力高：主从分离，读走从库。
- 海量日志：时序表、按月分区表
PARTITION BY RANGE。