mysql-design（MySQL 设计）

MySQL 数据库设计规范

触发条件

• 设计数据库表结构
• 定义字段类型和长度
• 创建索引
• 命名表和字段

Part 1: 命名规范

库名命名

表名命名

字段命名

索引命名

Part 2: 字段类型规范

主键

`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键'

字符串类型

数值类型

时间类型

布尔类型

`is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否删除(0否 1是)'
`is_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用(0否 1是)'

Part 3: 通用字段

每张表必备字段

`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '创建人',
`update_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '更新人',
`deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志(0未删除 1已删除)',
PRIMARY KEY (`id`)

可选字段

`remark` VARCHAR(500) DEFAULT NULL COMMENT '备注',
`version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
`tenant_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '租户ID',

Part 4: 索引规范

索引原则

必建索引

-- 主键索引
PRIMARY KEY (`id`)

-- 外键索引
INDEX `idx_dept_id` (`dept_id`)

-- 状态索引（常用于筛选）
INDEX `idx_status` (`status`)

-- 创建时间索引（常用于排序）
INDEX `idx_create_time` (`create_time`)

-- 唯一索引（业务唯一字段）
UNIQUE INDEX `uk_username` (`username`)

组合索引

-- 组合索引：部门 + 状态
INDEX `idx_dept_status` (`dept_id`, `status`)

-- 组合索引：用户 + 创建时间
INDEX `idx_user_time` (`user_id`, `create_time`)

索引避坑

Part 5: 表设计规范

用户表示例

CREATE TABLE `sys_user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` VARCHAR(50) NOT NULL COMMENT '用户名',
  `password` VARCHAR(100) NOT NULL COMMENT '密码',
  `nickname` VARCHAR(50) NOT NULL COMMENT '昵称',
  `mobile` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
  `gender` TINYINT NOT NULL DEFAULT 0 COMMENT '性别(0未知 1男 2女)',
  `avatar` VARCHAR(500) DEFAULT NULL COMMENT '头像URL',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(1正常 0禁用)',
  `dept_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '部门ID',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '创建人',
  `update_by` BIGINT UNSIGNED DEFAULT NULL COMMENT '更新人',
  `deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志(0未删除 1已删除)',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_username` (`username`),
  INDEX `idx_dept_id` (`dept_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

关联表示例

CREATE TABLE `sys_user_role` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `role_id` BIGINT UNSIGNED NOT NULL COMMENT '角色ID',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `uk_user_role` (`user_id`, `role_id`),
  INDEX `idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户角色关联表';

Part 6: 字段默认值

NULL vs NOT NULL

Part 7: 字符集与引擎

字符集

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

存储引擎

ENGINE=InnoDB

Part 8: SQL 编写规范

SELECT

-- ✅ 指定字段
SELECT id, username, nickname FROM sys_user WHERE status = 1;

-- ❌ 避免 SELECT *
SELECT * FROM sys_user;

-- ✅ 分页查询
SELECT id, username FROM sys_user WHERE status = 1 LIMIT 0, 20;

-- ✅ 批量插入
INSERT INTO sys_user (username, nickname) VALUES ('user1', '用户1'), ('user2', '用户2');

UPDATE

-- ✅ 带 WHERE 条件
UPDATE sys_user SET nickname = '新昵称' WHERE id = 1;

-- ✅ 乐观锁
UPDATE sys_user SET nickname = '新昵称', version = version + 1 WHERE id = 1 AND version = 1;

-- ❌ 避免全表更新
UPDATE sys_user SET status = 0;

DELETE

-- ✅ 逻辑删除
UPDATE sys_user SET deleted = 1 WHERE id = 1;

-- ✅ 物理删除（慎用）
DELETE FROM sys_user_role WHERE user_id = 1;

Part 9: 性能优化

查询优化

表设计优化

Part 10: 设计检查清单

• 表名使用小写 + 下划线
• 字段名使用小写 + 下划线
• 主键使用 BIGINT AUTO_INCREMENT
• 包含通用字段（create_time, update_time, deleted）
• 外键字段添加索引
• 唯一业务字段添加唯一索引
• 状态字段添加索引
• 使用 utf8mb4 字符集
• 使用 InnoDB 引擎
• 每个字段添加 COMMENT
• 表添加 COMMENT
• 索引数量不超过 5 个