6.8.3 表结构设计规范与命名约定

表结构设计规范与命名约定#

  • 设计目标：保证一致性、可维护性与可扩展性，降低运维与开发沟通成本，便于自动化管理与审计。
  • 规范原则
  • 结构扁平化，避免过度拆表与复杂关联。
  • 读写模式驱动设计，优先满足核心查询与写入路径。
  • 预留扩展字段，避免频繁变更表结构。
  • 明确业务边界，减少跨域强依赖与循环引用。
  • 所有表必须有主键，优先使用自增或雪花 ID。

原理草图：表结构与命名规范落地流程#

文章图片

命名约定#

  • 通用规则
  • 全小写、下划线分隔（snake_case）。
  • 表名、字段名使用英文名词或名词短语，避免拼音与缩写滥用。
  • 禁用 MySQL 保留字与关键字，必要时用前缀规避。

  • 命名可读、语义清晰，保持一致的复数/单数风格。

  • 表命名

  • 业务主表：业务_实体（如 order, user_profile）。
  • 关系表：实体1_实体2_rel（如 user_role_rel）。
  • 明细表：主表_detail（如 order_detail）。
  • 日志表：业务_log（如 login_log）。

  • 归档表：原表名_yyyymm 或 原表名_archive。

  • 字段命名

  • 主键：id；外键：实体_id。
  • 时间：created_at、updated_at、deleted_at。
  • 状态：status（配合枚举值定义）。
  • 逻辑删除：is_deleted（0/1）。

结构设计规范#

  • 字段设计
  • 必填字段设为 NOT NULL，提供合理默认值。
  • 避免 NULL 混用导致索引与统计复杂化。

  • 控制单表列数，建议核心表不超过 50 列。

  • 主键与索引

  • 主键短且稳定，避免使用业务字段作为主键。

  • 唯一约束命名：uk_表名_字段；普通索引：idx_表名_字段。

  • 关系设计

  • 避免强外键约束带来的写入开销，优先应用层保证一致性。

  • 1:N 使用外键字段；N:N 使用关系表。

  • 变更与兼容

  • 新增字段尽量放在表末尾，避免频繁改动列顺序。
  • 字段废弃使用逻辑标记，不立即删除。

示例：规范化建表与索引命名#

-- 业务主表：order
CREATE TABLE order (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  is_deleted TINYINT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_order_user_id (user_id),
  KEY idx_order_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 明细表：order_detail
CREATE TABLE order_detail (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_order_detail_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 关系表：user_role_rel
CREATE TABLE user_role_rel (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_user_role_rel_user_id_role_id (user_id, role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

示例：命名检查与保留字排查#

-- 检查库内所有表字段是否存在保留字风险（示例输出需人工判断）
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'app_db'
  AND column_name IN ('order','group','select');

-- 查看约束与索引命名规范
SHOW INDEX FROM order;
SHOW CREATE TABLE order\G

命令与解释：结构变更流程#

# 1) 生成变更SQL（示例：新增字段）
cat > /tmp/alter_add_column.sql <<'SQL'
ALTER TABLE order
  ADD COLUMN payment_method TINYINT NOT NULL DEFAULT 0 COMMENT '0未支付/1支付宝/2微信'
  AFTER status;
SQL

# 2) 预估影响：查看表大小与锁影响
mysql -uroot -p -e "SELECT table_name, data_length, index_length FROM information_schema.tables WHERE table_schema='app_db' AND table_name='order';"

# 3) 在线执行（示例：PT-OSC，需预装）
pt-online-schema-change \
  --user=root --password='***' --host=127.0.0.1 \
  --alter "ADD COLUMN payment_method TINYINT NOT NULL DEFAULT 0 COMMENT '0未支付/1支付宝/2微信' AFTER status" \
  D=app_db,t=order --execute

# 4) 校验结果
mysql -uroot -p -e "SHOW COLUMNS FROM app_db.order LIKE 'payment_method';"
  • 说明：
  • 第 2 步用于评估变更对大表的影响。
  • 第 3 步使用 PT-OSC 避免长时间锁表。
  • 第 4 步确认变更结果。

排错清单（常见问题与定位）#

  • 报错：ERROR 1064 (42000)
  • 原因：字段名或表名为保留字。

  • 处理：更换命名或使用反引号（不推荐长期使用）。

  • 报错：ERROR 1071 (42000) Specified key was too long

  • 原因：索引字段长度过大，尤其是 utf8mb4。

  • 处理：缩短字段长度，或使用前缀索引。

  • 报错：ERROR 1215 (HY000) Cannot add foreign key constraint

  • 原因：字段类型不一致或缺少索引。
  • 处理：统一字段类型与长度，确保父表字段有索引。

练习#

  1. 按命名规范为“用户地址”设计主表与明细表，包含主键、状态字段与时间字段。
  2. 将 user_profile 表新增 nickname 字段，要求使用在线变更工具完成。
  3. 编写 SQL 检查当前库中所有索引是否符合 idx_表名_字段 命名规则。

常见反模式#

  • 表名或字段名含义不清、缩写过度。
  • 以业务状态拼接多个布尔字段导致组合爆炸。
  • 频繁使用 TEXT/BLOB 存储结构化数据。
  • 过度拆表导致复杂 JOIN 与性能下降。

建议的规范文档模板#

  • 表用途与业务说明
  • 字段定义与默认值
  • 索引与约束
  • 数据量级与增长预估
  • 典型查询与更新路径