设计 MySQL 表的规则

1. 在设计表格时包括这些常用字段

通常，一个表格应包括以下字段：

• id：主键。每个表都必须有一个主键 — 没有例外。
• create_time： 创建时间 — 必需。
• modified_time：上次修改时间 — 必需。每当更新记录时，都应该更新此消息。
• version：记录的版本号，通常用于乐观锁定 — 可选。
• modifier：上次修改记录的人员 — 可选。
• creator：创建记录的人员 — 可选。

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 'Unique identifier for the order item, auto-incremented primary key',
    order_id BIGINT UNIQUE COMMENT 'Globally unique identifier for the order',
    user_id BIGINT NOT NULL COMMENT 'Unique identifier for the user, linked to the user table',
    total_amount DECIMAL(10, 2) NOT NULL COMMENT 'Total order amount, precise to two decimal places',
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'Order status, e.g., PENDING, COMPLETED, etc.',
    payment_status VARCHAR(20) DEFAULT 'not_paid' COMMENT 'Payment status, e.g., not_paid, paid, etc.',
    version INT DEFAULT 0 COMMENT 'Optimistic lock version number for concurrency control',
    created_time DATETIME COMMENT 'Order creation timestamp',
    updated_time DATETIME COMMENT 'Timestamp of last update',
    creator VARCHAR(255) COMMENT 'Creator of the order — typically the username of the user or system',
    modifier VARCHAR(255) COMMENT 'Last person or system to modify the order'
);

3. 命名约定

对表名、字段名、索引名等使用清晰一致的命名约定。良好的命名可以提高可读性和理解性。

例如，避免使用如下名称：

• acc_no， （坏例子）1_acc_no

相反，请使用：

• account_no，（很好的例子）account_number

附加规则：

• 表和字段名称应仅使用小写字母或数字。
• 名称不要以数字开头。
• 避免使用缩写。
• 索引命名约定：
  • 主键：pk_<field_name>
  • Unique 索引：uk_<field_name>
  • 常规索引：idx_<field_name>

4. 选择合适的字段类型

在设计表格时，请选择最合适的字段类型：

• 使用最小的合适数据类型来节省存储空间 — 例如，按顺序首选 、 、 、 。tinyintsmallintintbigint
• 对于货币值，请使用 或 。decimalfloatdouble
• 如果字符串长度是固定的或几乎固定的，请使用 .char
• 用于可变长度字符串，但将长度保持在 5000 以下。varchar
• 对于非常大的值，请考虑使用它们并将其存储在由主键链接的单独表中。text
• 表中所有字段的总长度不得超过 65535 字节。如果需要，请使用 或 types.varcharTEXTLONGTEXT

5. 设计合理的主键

避免将主键绑定到业务逻辑。例如，不建议使用用户 ID（即使唯一）作为主键。请改用无意义但唯一的标识符，例如：

• 一个 UUID，
• 自动递增的主键
• 由 Snowflake 算法生成的主键。

6. 选择合适的字段长度

我先问你一个问题：在数据库中，字段长度是字符长度还是字节长度？

在 MySQL 中：

• varchar并指定字符长度。char
• 其他类型通常指定字节长度。

例如：

• char(10)表示 10 个字符。
• bigint(4)指显示宽度（而不是存储大小），但无论如何都占用 8 个字节。bigint

在设计表格时，请仔细考虑字段长度。例如，对于预期长度介于 5 到 20 个字符之间的 username 字段，您可以将其定义为 .username varchar(32)

提示：字段长度通常最好设置为 2 的幂（即 2ⁿ）。

7. 首选逻辑删除而不是物理删除

物理删除：数据从磁盘中永久删除，从而释放存储空间。

逻辑删除：添加一个字段，例如将数据标记为已删除。is_deleted

物理删除示例：

DELETE FROM account_info_tab WHERE account_no = '666';

逻辑删除示例：

UPDATE account_info_tab SET is_deleted = 1 WHERE account_no = '666';

为什么更喜欢逻辑删除？

• 物理删除很难恢复数据。
• 自动递增的主键变为非连续。
• 对于核心业务表，最好更新状态字段，而不是物理删除记录。

8. 避免单个表中的字段过多

在设计表格时，尽量限制字段的数量 — 通常不超过 20 个。

字段过多会导致：

• 大行尺寸 /
• 查询性能不佳。

如果业务逻辑需要许多字段，请考虑将大表拆分为具有相同主键的小表。

当表包含大量字段时，请考虑将其分隔为：

• 一个 “query condition” 表（用于快速筛选），
• 一个“详细”表（用于完整内容）， 以提高性能。

9. 尽可能使用NOT NULL

除非有特定原因，否则建议将字段定义为 .NOT NULL

为什么？

• 防止空指针问题。
• NULL占用额外的存储空间。
• 比较更复杂，会阻碍查询优化。NULL
• NULL值可能会导致索引失败。
• 如果可以安全地将字段默认为空字符串或常量，则应将其定义为 .NOT NULL

10. 评估哪些字段需要索引

首先，评估数据集的大小。如果一个表只有几百行，则可能不需要索引。

通常，如果某个字段在查询条件中经常使用，则它应该具有索引。但是索引不应该被过度使用：

• 避免创建过多的索引 — 将每个表的索引保持在 5 个或更少。
  • 索引过多会减慢 INSERT 和 UPDATE作的速度。
• 不要为低基数的字段（例如，性别）编制索引。
• 请注意导致索引失败的情况，例如在索引字段上使用内置 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

由于您可能会通过 or 进行查询，并且是唯一的，因此您可以像这样定义索引：user_idnameuser_id

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;

11. 避免使用 MySQL 保留字

如果数据库名称、表名称或字段名称包含保留字，则必须在 SQL 语句中将其括在反引号 （'） 中。这会使 SQL 编写复杂化并增加出错的风险，尤其是在编写脚本或使用 shell 变量时。

因此，请避免使用 MySQL 保留字，例如 、 、 等。selectintervaldesc

12. 更喜欢 InnoDB 存储引擎

创建表时，必须选择存储引擎。通常，除非您的读写比率小于 1%，否则首选 InnoDB，在这种情况下，可以考虑 MyISAM。

13. 选择合适的时间类型

在设计表格时，我们通常会包含常见的时间相关字段，如 、 等。那么您应该使用哪种 MySQL 时间类型呢？create_timemodified_time

以下是主要选项：

• date：以格式存储日期值。范围：到 .大小：3 字节。yyyy-mm-dd1000-01-019999-12-31
• time：以格式存储时间值。范围：到 .大小：3 字节。hh:mm:ss-838:59:59838:59:59
• datetime：以格式存储日期和时间。范围：到 .大小：8 字节。不依赖于时区。yyyy-mm-dd hh:mm:ss1000-01-01 00:00:009999-12-31 23:59:59
• timestamp：将日期和时间存储为时间戳 （）。范围：到 .大小：4 字节。取决于时区。yyyymmddhhmmss1970-01-01 00:00:012038-01-19 03:14:07
• year：以格式存储年份值。范围：到 .大小：1 字节。yyyy19012155

建议： 首选使用 type 来存储日期和时间，因为它的范围更大并且独立于时区。datetime

14. 安全注意事项

• 数据加密：用户密码等敏感信息应以加密格式存储。
• 数据掩码：对于电话号码或电子邮件地址等个人身份信息 （PII），应用数据掩码以提高隐私性和合规性。

出处：https://dev.to/