数据库表结构设计规范

数据库表结构设计规范

字段命名、类型、主键、索引、注释

---

表命名

【Standard】表名使用小写蛇形命名，采用「业务前缀_实体名」形式，禁止使用数据库保留字。

规则	说明
命名格式	<业务域>_<实体名>，全部小写，单词间以下划线分隔
单数复数	表名统一使用复数形式，表达「集合」语义
保留字	禁止使用 order、user、group、key 等保留字，必须加业务前缀规避
长度	表名不超过 32 个字符

示例：

-- 正确
CREATE TABLE order_order (
  id BIGINT PRIMARY KEY,
  ...
);
 
CREATE TABLE sys_user (
  id BIGINT PRIMARY KEY,
  ...
);
 
CREATE TABLE pay_refund_record (
  id BIGINT PRIMARY KEY,
  ...
);

反模式：

-- 错误：使用保留字、驼峰、无前缀
CREATE TABLE order ( ... );   -- 保留字
CREATE TABLE sysUser ( ... ); -- 驼峰
CREATE TABLE user ( ... );    -- 保留字且无业务前缀

---

字段设计

【Requirement】字段名使用小写蛇形命名；必须显式指定类型、NOT NULL（有默认值时）、COMMENT；禁止使用数据库保留字作为字段名。

2.1 命名规则

• 布尔字段以 is_、has_、can_ 开头，类型使用 TINYINT(1) 而非 BOOLEAN
• 时间字段统一以 _time 或 _at 结尾，使用 DATETIME 而非 TIMESTAMP（规避 2038 问题）
• 金额字段以 _amount、_fee 结尾，使用 DECIMAL，禁止使用 FLOAT/DOUBLE
• 状态字段以 _status 结尾，枚举值以 _type 结尾
• 外键字段以 <被引用表名单数>_id 命名，如 user_id、order_id

2.2 类型选择

场景	推荐类型	说明
主键 ID	BIGINT	64 位整型，支持雪花算法，禁止自增 INT（数据量超 2 亿溢出）
短字符串	VARCHAR(N)	N 为实际最大长度 + 冗余，禁止随意 VARCHAR(255)
长文本	TEXT / LONGTEXT	不允许建索引，不允许作为查询条件
金额	DECIMAL(18,2)	财务字段 DECIMAL(18,4)，禁止浮点
时间	DATETIME(3)	毫秒精度，UTC 存储
枚举	TINYINT	配合注释说明枚举值，禁止使用 MySQL ENUM 类型
JSON	JSON（MySQL 5.7+）	结构不固定时使用，禁止滥用存储结构化数据

2.3 必填与默认值

• 所有字段必须显式声明 NOT NULL 或允许 NULL
• 字符串字段默认空串 ''，数值字段默认 0，布尔字段默认 0
• 时间字段 create_time / update_time 必须有默认值

示例：

CREATE TABLE order_order (
  id              BIGINT       NOT NULL COMMENT '主键ID',
  order_no        VARCHAR(32)  NOT NULL DEFAULT '' COMMENT '订单号',
  user_id         BIGINT       NOT NULL DEFAULT 0 COMMENT '用户ID',
  total_amount    DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '订单总金额',
  order_status    TINYINT      NOT NULL DEFAULT 0 COMMENT '订单状态：0-待支付 1-已支付 2-已取消',
  is_deleted      TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '是否删除：0-否 1-是',
  remark          VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
  create_time     DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  update_time     DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

反模式：

-- 错误：使用 FLOAT 存金额、ENUM 类型、无注释、TIMESTAMP
CREATE TABLE bad_table (
  amount FLOAT,                          -- 金额用浮点，精度丢失
  status ENUM('PENDING','PAID'),          -- 改枚举需 DDL
  create_time TIMESTAMP,                  -- 2038 问题
  name VARCHAR(255)                       -- 无注释，长度随意
);

---

主键策略

【Standard】主键统一使用 BIGINT，采用雪花算法（Snowflake）全局唯一 ID，禁止使用业务字段作为主键。

规则	说明
类型	BIGINT 自增或雪花 ID，禁止 INT
来源	分布式场景必须使用雪花算法，单机可使用自增 AUTO_INCREMENT
业务主键	订单号、用户编码等业务唯一字段单独建唯一索引，不充当物理主键
联合主键	禁止使用联合主键，中间表使用独立自增 ID + 唯一索引

示例：

-- 雪花 ID 主键
CREATE TABLE order_order (
  id BIGINT NOT NULL COMMENT '主键ID(雪花算法)',
  ...
  PRIMARY KEY (id)
);
 
-- 中间表：独立主键 + 唯一索引
CREATE TABLE order_order_item (
  id          BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  order_id    BIGINT NOT NULL COMMENT '订单ID',
  product_id  BIGINT NOT NULL COMMENT '商品ID',
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_product (order_id, product_id)
);

反模式：

-- 错误：联合主键、业务字段做主键
CREATE TABLE order_order (
  order_no VARCHAR(32) NOT NULL,   -- 业务字段做主键
  user_id BIGINT,
  PRIMARY KEY (order_no, user_id)  -- 联合主键
);

---

索引设计

【Requirement】索引必须基于查询场景设计；单表索引不超过 5 个；联合索引遵循最左前缀原则；禁止在低基数字段上建普通索引。

4.1 索引命名

索引类型	命名规则	示例
主键	PRIMARY	自动
唯一索引	uk_<字段名> / uk_<表缩写>_<字段>	uk_order_no
普通索引	idx_<字段名>	idx_user_id
联合索引	idx_<字段1>_<字段2>	idx_user_status
全文索引	ft_<字段名>	ft_content

4.2 设计原则

1. 【Default】单表索引数量 ≤ 5 个：索引过多会拖慢写入性能，每个索引都需维护
2. 【Standard】联合索引字段顺序按区分度从高到低：区分度高的字段放前面
3. 【Requirement】ORDER BY / GROUP BY 字段优先加入索引：避免 filesort
4. 【Caution】低基数字段（如 is_deleted、status 取值 < 10）不单独建索引：区分度不足，优化器不使用
5. 【Standard】LIKE 'xxx%' 可走索引，LIKE '%xxx' 不走索引：左模糊查询必须用全文索引或 ES
6. 【Requirement】字符串索引长度必须指定前缀长度：idx_name (name(20))，避免索引过大

示例：

-- 正确：基于查询场景的联合索引
-- 查询场景：WHERE user_id = ? AND order_status = ? ORDER BY create_time DESC
CREATE TABLE order_order (
  id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  order_status TINYINT NOT NULL,
  create_time DATETIME(3) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_user_status_time (user_id, order_status, create_time)
);
 
-- 字符串前缀索引
ALTER TABLE sys_user ADD INDEX idx_name (name(20));

反模式：

-- 错误：冗余索引、低基数索引、索引顺序错误
CREATE TABLE order_order (
  ...
  KEY idx_user (user_id),                 -- 冗余，联合索引已覆盖
  KEY idx_status (order_status),          -- 低基数，优化器不用
  KEY idx_time_user (create_time, user_id) -- 顺序错误，最左前缀失效
);

---

注释规范

【Requirement】库、表、字段必须添加 COMMENT 注释；状态/枚举字段必须列出全部取值含义。

5.1 表注释

格式：COMMENT='<业务含义>'，不含特殊符号。

CREATE TABLE order_order ( ... ) COMMENT='订单主表';

5.2 字段注释

• 普通字段：COMMENT '<业务含义>'
• 枚举/状态字段：COMMENT '<业务含义>：0-<含义> 1-<含义> ...'
• 布尔字段：COMMENT '<业务含义>：0-否 1-是'
• 时间字段：COMMENT '<业务含义>'，无需说明时区（统一 UTC）

5.3 枚举值规范

状态字段的枚举值必须连续或有明确间隔，注释中必须穷举：

order_status TINYINT NOT NULL DEFAULT 0 
  COMMENT '订单状态：0-待支付 1-已支付 2-已发货 3-已完成 4-已取消 5-已退款'

反模式：

-- 错误：无注释、枚举未穷举
CREATE TABLE bad_table (
  id BIGINT,                   -- 无注释
  status TINYINT,              -- 无注释，不知道取值含义
  name VARCHAR(50),            -- 无注释
  create_time DATETIME         -- 无注释
);