后端程序员必备：15个MySQL表设计的经验准则
MySQL数据库设计对于后端开发人员至关重要。合理的表设计不仅能提升系统性能，还能避免各种数据问题。以下15条准则旨在为后端程序员提供基础而又重要的MySQL表设计经验，帮助大家在实际开发中少踩坑。
1. 基础通用字段
设计表时，建议添加以下通用字段：
- id：主键，一个表必须有主键，且最好为独立的自增主键。
- create_time：创建时间，记录初次插入的时间，必备。
- modified_time：修改时间，每次更新记录时应更新该字段。
- version：数据记录的版本号，用于实现乐观锁（非必需）。
- creator / modifier：记录创建和最后修改记录的用户（非必需）。
这些字段不仅能提升表结构的一致性，还便于日后维护和审计。
2. 字段注释的规范性
在表设计时，为每个字段添加注释，尤其是涉及枚举类型的字段。注释帮助后期维护和代码理解，明确字段的含义及其潜在的值取范围。
反例：
CREATE TABLE order_tab (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT UNIQUE,
user_id BIGINT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
payment_status VARCHAR(20) DEFAULT 'not_paid',
version INT DEFAULT 0,
created_time DATETIME,
updated_time DATETIME,
creator VARCHAR(255),
modifier VARCHAR(255)
);
正例：
CREATE TABLE order_tab (
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '订单项的唯一标识符，自增主键',
order_id BIGINT UNIQUE COMMENT '订单的唯一标识符，在整个系统中唯一',
user_id BIGINT NOT NULL COMMENT '用户的唯一标识符，关联到用户表',
total_amount DECIMAL(10, 2) NOT NULL COMMENT '订单的总金额，精确到小数点后两位',
status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '订单的状态，例如：PENDING（待处理）、COMPLETED（已完成）等',
payment_status VARCHAR(20) DEFAULT 'not_paid' COMMENT '订单的支付状态，如：not_paid（未支付）、paid（已支付）等',
version INT DEFAULT 0 COMMENT '乐观锁版本号，用于并发控制',
created_time DATETIME COMMENT '订单的创建时间',
updated_time DATETIME COMMENT '订单的最后一次更新时间',
creator VARCHAR(255) COMMENT '订单的创建者，通常记录创建订单的用户或系统的用户名',
modifier VARCHAR(255) COMMENT '订单的修改者，通常记录最后修改订单的用户或系统的用户名'
);
3. 统一的命名规范
表名、字段名和索引名都应使用易读、英文小写单词表示，并避免使用拼音或数字开头。主键索引名建议使用pk_字段名，唯一索引用uk_字段名，普通索引用idx_字段名。
例如：字段名采用account_no而不是zhanghao。
4. 选择合适的字段类型
设计表时，尽量选择存储空间小的字段类型：
- 整型字段：从
TINYINT、SMALLINT、INT到BIGINT。 - 小数类型：对于金额等需精确计算的数值使用
DECIMAL，避免使用FLOAT和DOUBLE。 - 字符串：根据实际长度选择
CHAR（定长）或VARCHAR（变长）。VARCHAR不宜超过5000字符；长度需求大的数据建议使用TEXT，并将大字段拆分到单独的表中。
5. 主键设计需合理
主键应选择独立的、无业务关联的字段，不建议用身份证号等业务字段作为主键。可以选择UUID、自增主键或基于雪花算法生成的主键。
6. 确定字段的合理长度
字段长度表示字符数或字节数，例如VARCHAR(32)适用于用户名字段（通常为5到20个字符）。建议字段长度设为2的幂，如32、64、128等。
7. 优先选择逻辑删除而非物理删除
物理删除指数据在存储中彻底删除，而逻辑删除则通过is_deleted等字段标记删除状态。推荐逻辑删除，尤其在核心业务数据中，以便数据恢复和保持自增主键的连续性。
逻辑删除示例：
UPDATE account_info_tab SET is_deleted = 1 WHERE account_no ='666';
8. 控制表的字段数量
一张表的字段数量尽量控制在20个以内，以避免数据量过大导致查询效率低。如果字段较多，建议分拆为多张表。
9. 尽量定义字段为NOT NULL
为防止空指针问题，并提升查询性能，除非有特殊需求，字段都应定义为NOT NULL，可以通过默认值或常量来填充字段。
10. 评估并添加必要的索引
根据表的数据量和查询需求设置索引：
- 索引数量不宜过多（单表索引个数不超过5个）。
- 区分度低的字段（如性别）不适合建立索引。
- 可通过联合索引优化多列条件查询。
索引示例：
CREATE TABLE user_info_tab (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`age` int(11) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`create_time` datetime NOT NULL,
`modified_time` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY un_user_id (`user_id`),
KEY idx_name (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
11. 避免使用MySQL保留字
避免库名、表名或字段名使用MySQL的保留字（如SELECT、DESC等），否则需要使用反引号引用，会增加代码复杂性。
12. 默认使用InnoDB存储引擎
表设计时默认选择InnoDB存储引擎，除非读写比率极低（<1%），可考虑MyISAM。其他引擎需在DBA指导下使用。
13. 统一字符集的选择
字符集推荐使用utf8或utf8mb4以支持中英文及emoji。其他字符集如GBK仅适合中文环境，latin1适合仅支持英文的场景。
14. 时间字段类型的选择
常用的时间类型：
- DATE：日期（
YYYY-MM-DD，占3字节） - TIME：时间（
HH:MM:SS，占3字节） - DATETIME：日期时间（
YYYY-MM-DD HH:MM:SS，占8字节，与时区无关） - TIMESTAMP：时间戳（占4字节，与时区有关）
- YEAR：年份（占1字节）
推荐优先使用DATETIME，其存储范围大且与时区无关。
15. 数据安全考虑
- 数据加密：对于用户密码等敏感信息应使用加密存储。
- 数据脱敏：如手机号、邮箱等建议存储时脱敏，确保数据隐私。
以上即为MySQL表设计的15条经验准则，遵循这些准则能够显著提升数据库的性能和稳定性，也能让后期的维护更加便捷。希望对你在实际项目中有所帮助！
评论区