📚 课程目录第4章数据库表设计（上）

用户表、角色表、权限表的结构与字段设计

数据库表设计，是整个权限模型的“地基”。

我做过好几个权限系统，最怕的就是前期表结构没想清楚，后面改起来像拆积木——牵一块动全身。这一章，咱们把 用户表（user）、角色表（role）、权限表（permission） 这三个核心表的结构彻底聊透。

核心原则：每张表只关注自己的职责，字段宁少勿多，索引提前规划。

用户表（user）—— 谁在用系统

用户表的核心字段，说白了就是回答三个问题：你是谁？你怎么登录？你当前啥状态？

我习惯把用户的基本信息（姓名、工号、手机号）和登录认证信息（密码、盐值、多因子凭据）放在同一张表里，除非你有成千上万的字段才考虑拆分。下面是一个实际项目里用过的结构：

字段名	类型	说明
user_id	BIGINT UNSIGNED PK	自增主键，无业务意义
login_name	VARCHAR(64) UNIQUE	唯一登录名，通常用邮箱或手机号
password_hash	VARCHAR(256)	bcrypt 哈希，别存明文
real_name	VARCHAR(64)	真实姓名，用于显示
email	VARCHAR(128)	找回密码、通知用
mobile	VARCHAR(20)	可选，短信通知
status	TINYINT	0-禁用，1-正常，9-锁定
last_login_ip	VARCHAR(45)	IPv4/IPv6 记录，安全审计
created_at	DATETIME	创建时间
updated_at	DATETIME	最后修改时间

我曾经踩过的坑：把用户的部门和岗位信息直接塞在 user 表里。后来组织调整，部门层级变四级树结构，结果 user 表里一堆冗余字段，还得加触发器同步。建议所有组织关系（部门、岗位、职级）单独用关联表。

关于 login_name 的唯一约束，我建议用 业务唯一索引，不要只依赖业务代码判断。数据库层的唯一性是你最后的防线。

角色表（role）—— 给用户贴标签

角色就是一组权限的集合。一个用户可以拥有多个角色，一个角色也可以归属多个用户——这是典型的多对多关系。我们先看角色表本身：

字段名	类型	说明
role_id	INT UNSIGNED PK	自增主键
role_name	VARCHAR(64) UNIQUE	角色名，比如“系统管理员”
role_key	VARCHAR(64) UNIQUE	角色标识符，比如 `admin`，代码里硬编码用这个
description	VARCHAR(256)	角色说明，辅助管理员理解
sort_order	SMALLINT	排序，列表页展示顺序
status	TINYINT	0-禁用，1-启用
created_at	DATETIME	略
updated_at	DATETIME	略

注意 role_key 字段，很多人觉得有了 role_name 就够了。但实际开发中，代码里经常需要判断“当前用户是不是管理员”，你如果写 if user.hasRole('系统管理员')，万一名字改了怎么办？role_key 就是用来做这种 程序内硬编码判断 的，业务名随便改，key 不变。

用户与角色的关联表，我习惯叫 user_role：

CREATE TABLE user_role (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, role_id),
  INDEX idx_role_id (role_id),
  FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES role(role_id) ON DELETE CASCADE
);

一个小建议：联合主键用 (user_id, role_id)，因为最常见的查询是“根据用户查角色”。再加一个 role_id 的索引，方便查“某个角色下有哪些用户”。

权限表（permission）—— 能不能做这件事

权限表是粒度最细的一层。常见的做法是 权限编码（permission_code） + 资源路径（resource） + 操作类型（action）。比如：

字段名	类型	说明
perm_id	INT UNSIGNED PK	自增主键
perm_code	VARCHAR(128) UNIQUE	权限标识，比如 `user:create`
perm_name	VARCHAR(64)	权限名称，如“创建用户”
resource	VARCHAR(64)	资源标识，如 `user`
action	VARCHAR(32)	操作，如 `create`, `read`, `update`, `delete`
type	TINYINT	1-菜单权限，2-按钮权限，3-API权限
parent_id	INT UNSIGNED	父权限id，做树形结构
sort_order	SMALLINT	排序
created_at	DATETIME	略

这里的 perm_code 是精华。我习惯用 模块:操作 的风格，比如 order:export 表示“导出订单”。前端按钮级权限控制全靠这个字段匹配。

角色和权限的关联表 role_permission 结构和 user_role 类似：

CREATE TABLE role_permission (
  role_id INT UNSIGNED NOT NULL,
  perm_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (role_id, perm_id),
  INDEX idx_perm_id (perm_id),
  FOREIGN KEY (role_id) REFERENCES role(role_id) ON DELETE CASCADE,
  FOREIGN KEY (perm_id) REFERENCES permission(perm_id) ON DELETE CASCADE
);

三表关系的宏观图

你想想看，这三张表是怎么串起来的？用户通过 user_role 得到角色，角色再通过 role_permission 拿到权限。整个过程就是“用户 → 角色 → 权限”的两步跳转。下面这张图把逻辑画清楚了：

设计时最容易忽略的三个细节

外键级联策略：删除用户时，关联的 user_role 记录要自动删除。用 ON DELETE CASCADE 最省心。但删除角色时，要特别小心——如果是核心角色，我建议先禁用（status=0）而不是物理删除，等确认无影响再清理。
时间戳别偷懒：每张表都加 created_at 和 updated_at。你永远不知道哪天需要排查“这个角色是什么时候加进去的”。
权限编码全局唯一：不要在不同模块里用同样的 perm_code，比如订单模块的 export 和报表模块的 export 要写成 order:export 和 report:export。不然权限分配时会一片混乱。

我曾经见过一个项目，权限表里没有 parent_id，结果前端菜单权限用硬编码写死了三层嵌套。后来领导一句话要改菜单结构……那场面，简直是灾难。哪怕你现在只需要扁平权限，也建议保留 parent_id 字段，给未来留条活路。

建表顺序的建议

我个人习惯的建表顺序：先建权限表 → 再建角色表 → 最后建用户表。为什么？因为 role_permission 依赖 permission 和 role，user_role 又依赖 user 和 role。把基础数据（权限）先准备好，再创建角色，最后导入用户，流程最顺畅。

你可以用下面的 SQL 模板一次性跑通：

-- 1. 权限表
CREATE TABLE permission (
  perm_id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  perm_code    VARCHAR(128) NOT NULL UNIQUE,
  perm_name    VARCHAR(64) NOT NULL,
  resource     VARCHAR(64) NOT NULL,
  action       VARCHAR(32) NOT NULL,
  type         TINYINT NOT NULL DEFAULT 1,
  parent_id    INT UNSIGNED DEFAULT NULL,
  sort_order   SMALLINT NOT NULL DEFAULT 0,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. 角色表
CREATE TABLE role (
  role_id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_name    VARCHAR(64) NOT NULL UNIQUE,
  role_key     VARCHAR(64) NOT NULL UNIQUE,
  description  VARCHAR(256) DEFAULT NULL,
  sort_order   SMALLINT NOT NULL DEFAULT 0,
  status       TINYINT NOT NULL DEFAULT 1,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 3. 用户表
CREATE TABLE user (
  user_id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  login_name   VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(256) NOT NULL,
  real_name    VARCHAR(64) DEFAULT NULL,
  email        VARCHAR(128) DEFAULT NULL,
  mobile       VARCHAR(20) DEFAULT NULL,
  status       TINYINT NOT NULL DEFAULT 1,
  last_login_ip VARCHAR(45) DEFAULT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 4. 关联表
CREATE TABLE user_role (
  user_id   BIGINT UNSIGNED NOT NULL,
  role_id   INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, role_id),
  INDEX idx_role_id (role_id),
  FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES role(role_id) ON DELETE CASCADE
);

CREATE TABLE role_permission (
  role_id   INT UNSIGNED NOT NULL,
  perm_id   INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (role_id, perm_id),
  INDEX idx_perm_id (perm_id),
  FOREIGN KEY (role_id) REFERENCES role(role_id) ON DELETE CASCADE,
  FOREIGN KEY (perm_id) REFERENCES permission(perm_id) ON DELETE CASCADE
);

实际项目中，我通常还会在 user 表的 login_name 和 status 上加联合索引 idx_login_status，用来加速登录时的查询。毕竟登录接口的高频 SQL 就是 SELECT * FROM user WHERE login_name = ? AND status = 1。

好了，这三张表的结构已经全部落定。你可以在心里过一遍：一个用户登录后，系统怎么查他有哪些角色？发一条 SELECT role_id FROM user_role WHERE user_id = ?，再根据角色查权限。这比在用户表里直接存一个“角色ID列表”要灵活得多——角色变动不需要动用户表，权限调整也不需要动角色表。

数据库设计这块，多花半小时想清楚，能省下后面重构的三天。收工。