后端程序员必备：15个MySQL表设计的经验准则

标签：COMMENT 15 索引 id 程序员 MySQL NULL 主键

15 个 MySQL 表设计的经验准则

在数据库设计中，MySQL 表的设计至关重要。一个良好的表设计可以提高数据库的性能、可维护性和数据的准确性。以下是 15 个 MySQL 表设计的经验准则，希望能为你的数据库设计提供有益的指导。

一、通用字段的设置

在设计表时，尽量包含以下几个通用字段：

id：作为主键，是表中每行数据的唯一标识，必不可少。
create_time：记录数据的创建时间。
modifed_time：用于记录数据的修改时间，每次更新记录时同步更新。
version：数据记录的版本号，通常用于乐观锁，非必需。
modifier：修改人，非必需。
creator：创建人，非必需。

二、字段注释的重要性

设计表时，每个字段都应添加注释，尤其是涉及枚举字段时，要详细列出每个枚举值，并在后续有变更时及时维护注释内容。
例如，对于订单表的正确示例如下：

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 '订单的修改者，通常记录最后修改订单的用户或系统的用户名'
);

三、命名规范

数据库表名、字段名、索引名等应遵循命名规范，具有高可读性，一般要求使用英文命名，让人一眼就能明白字段的含义。
表名、字段名必须使用小写字母或者数字，禁止以数字开头，禁止使用拼音，且一般不使用英文缩写。
主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。

四、选择合适的字段类型

在设计表时，需根据实际需求选择合适的字段类型：
- 尽可能选择存储空间小的字段类型，如数字类型可从 tinyint、smallint、int、bigint 依次选择。
- 对于小数类型如金额，应选择 decimal，避免使用 float 和 double。
- 若存储的字符串长度几乎相等，可使用 char 定长字符串类型。
- varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000。
- 如果存储的值太大，可将字段类型修改为 text，并抽出单独一张表，用主键与之对应。
- 同一表中，所有 varchar 字段的长度加起来不能大于 65535，若有此需求，可使用 TEXT/LONGTEXT 类型。

五、合理的主键设计

主键设计应避免与业务逻辑关联。例如，虽然身份证是唯一的，但不建议将其作为主键。
主键最好是毫无意义的一串独立不重复的数字，如 UUID、自增主键或雪花算法生成的主键等。

六、选择合适的字段长度

在 MySQL 中，varchar 和 char 类型表示字符长度，其他类型表示的长度都为字节长度。例如，char(10)表示字符长度是 10，而 bigint(4)表示显示长度是 4 个字节，但实际长度为 8 个字节。
设计表时，要充分考虑字段长度。如用户名字段长度为 5 - 20 个字符，可设置为 username varchar(32)，且字段长度一般设置为 2 的幂次方。

七、优先考虑逻辑删除

物理删除是将数据从硬盘中删除，可释放存储空间；逻辑删除则是给数据添加一个字段（如 is_deleted）来标记数据已被逻辑删除。
推荐使用逻辑删除而非物理删除的原因如下：
- 物理删除后恢复数据困难。
- 会使自增主键不再连续。
- 核心业务表的数据不适合物理删除，只适合做状态变更。

八、控制表的字段数量

建表时要牢记，一张表的字段不宜过多，一般尽量不超过 20 个字段。若表的字段过多，可能导致数据量大，查询效率降低。
若业务需求确实需要很多字段，可以将大表拆成多张小表，它们的主键相同即可。当表的字段数非常多时，可将表分成两张表，一张作为条件查询表，一张作为详细内容表，以提高性能。

九、尽可能使用 NOT NULL 定义字段

若无特殊理由，建议将字段定义为 NOT NULL。原因如下：
- 可以防止出现空指针问题。
- NULL 值存储需要额外空间，且会使比较运算更为复杂，让优化器难以优化 SQL。
- NULL 值可能导致索引失效。
- 若将字段默认设置成一个空字符串或常量值不影响应用逻辑，可将该字段设置为 NOT NULL。

十、评估字段是否需要加索引

首先评估表的数据量，若数据量只有一百几十行，无需加索引。设计表时，有查询条件的字段一般需要建立索引，但索引不能滥用：
- 索引数量不宜过多，一般单表索引个数不要超过 5 个，过多索引会降低写入速度。
- 区分度不高的字段（如性别）不能加索引。
- 要注意避免索引失效的情况，如使用 MySQL 的内置函数会导致索引失效。索引过多时，可以通过联合索引进行优化，同时要遵循覆盖索引、最左匹配原则等。
例如，对于用户表：

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,
    `modifed_time` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_name` (`name`) USING BTREE,
    UNIQUE KEY un_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

十一、避免使用 MySQL 保留字

库名、表名、字段名等属性应避免使用 MySQL 保留字，如 select、interval、desc 等。若使用保留字，SQL 语句必须用反引号来引用属性名称，会使 SQL 语句书写和 SHELL 脚本中变量的转义变得复杂。

十二、选择合适的存储引擎

一般选择 INNODB 存储引擎，除非读写比率小于 1%，才考虑使用 MyISAM。其他存储引擎一般在 DBA 的指导下使用。

十三、选择合适统一的字符集

数据库库、表、开发程序等应统一字符集，通常中英文环境下使用 utf8。
MySQL 支持的字符集有 utf8、utf8mb4、GBK、latin1 等。其中，utf8 支持中英文混合场景，3 个字节长度；utf8mb4 完全兼容 utf8，4 个字节长度，一般用于存储 emoji 表情；GBK 支持中文，但不支持国际通用字符集，2 个字节长度；latin1 是 MySQL 默认字符集，1 个字节长度。

十四、时间类型的选择

设计表时通常需要添加通用时间字段，如 create_time、modified_time 等。对于时间类型的选择，MySQL 主要有 date、datetime、time、timestamp 和 year。推荐优先使用 datetime 类型来保存日期和时间，因为其存储范围更大，且与时区无关。

十五、安全性考虑

数据加密：对于敏感信息，如用户密码，应进行加密存储。对于手机号、邮箱等信息，建议进行脱敏处理。

标签：COMMENT,15,索引,id,程序员,MySQL,NULL,主键
From： https://blog.csdn.net/yuanmomoya/article/details/143028914