StackHub

Appearance

第 4 章 ⏱ 12 分钟阅读

第 4 章:数据库设计 ​

学习目标 ​

  • 设计 RBAC 数据库表结构
  • 编写规范的建表 SQL
  • 理解索引、字符集、引擎的选择策略

一、表清单 ​

TaskFlow 涉及 9 张表,分三类:

类型表名作用
业务表sys_user / sys_role / sys_menu / sys_dept / sys_dict核心数据
关系表sys_user_role / sys_role_menu / sys_role_dept多对多关联
审计表sys_operation_log / sys_login_log日志审计

⚠️ 坑 1:数据库设计的第一原则是"满足业务,不预留"。不要在第一版就预留"以后用得上"的字段,后期重构的成本远低于现在空字段的维护成本。

二、用户表(sys_user) ​

sql
CREATE TABLE `sys_user` (
    `id`               BIGINT       NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    `username`         VARCHAR(50)  NOT NULL                COMMENT '登录名',
    `password`         VARCHAR(100) NOT NULL                COMMENT '密码(BCrypt)',
    `nickname`         VARCHAR(50)                         COMMENT '昵称',
    `email`            VARCHAR(100)                        COMMENT '邮箱',
    `phone`            VARCHAR(20)                         COMMENT '手机号',
    `avatar`           VARCHAR(255)                        COMMENT '头像URL',
    `gender`           TINYINT        DEFAULT 0            COMMENT '0=未知 1=男 2=女',
    `status`           TINYINT        DEFAULT 1            COMMENT '0=禁用 1=正常 2=锁定',
    `dept_id`          BIGINT                              COMMENT '部门ID',
    `last_login_time`  DATETIME                            COMMENT '最后登录时间',
    `last_login_ip`    VARCHAR(50)                         COMMENT '最后登录IP',
    `create_by`        BIGINT                              COMMENT '创建人',
    `create_time`      DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by`        BIGINT                              COMMENT '更新人',
    `update_time`      DATETIME      DEFAULT CURRENT_TIMESTAMP
                                  ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `is_deleted`       TINYINT        DEFAULT 0            COMMENT '逻辑删除',
    `version`          INT            DEFAULT 0            COMMENT '乐观锁',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`, `is_deleted`),
    KEY `idx_dept` (`dept_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

关键设计:

  • password 长度 VARCHAR(100) 够 BCrypt 存($2a$10$xxx,60 字符)
  • 唯一键包含 is_deleted 是软删场景必踩坑:不带上,删了的用户名无法重复使用
  • version 字段用于 MyBatis Plus 乐观锁,防止并发覆盖
java
// 软删字段踩坑示例
// 错误:删除用户后,无法重建同名用户
ALTER TABLE sys_user DROP INDEX uk_username;

// 正确:联合唯一键包含 is_deleted
UNIQUE KEY `uk_username` (`username`, `is_deleted`)

三、角色表(sys_role) ​

sql
CREATE TABLE `sys_role` (
    `id`           BIGINT       NOT NULL AUTO_INCREMENT,
    `name`         VARCHAR(50)  NOT NULL                COMMENT '角色名',
    `code`         VARCHAR(50)  NOT NULL                COMMENT '角色编码,ROLE_XXX',
    `description`  VARCHAR(255)                         COMMENT '描述',
    `sort`         INT          DEFAULT 0                COMMENT '排序',
    `status`       TINYINT      DEFAULT 1                COMMENT '0=禁用 1=正常',
    `data_scope`   TINYINT      DEFAULT 4                COMMENT '数据权限:1-全部 2-本部门及下级 3-本部门 4-仅本人 5-自定义',
    `create_by`    BIGINT,
    `create_time`  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `update_by`    BIGINT,
    `update_time`  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_deleted`   TINYINT      DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_code` (`code`, `is_deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

角色编码约束(code 字段):

  • 必须 ROLE_ 前缀(ROLE_ADMIN、ROLE_USER)
  • 校验注解:@Pattern(regexp = "^ROLE_[A-Z_]+$")

四、菜单表(sys_menu) ​

sql
CREATE TABLE `sys_menu` (
    `id`          BIGINT       NOT NULL AUTO_INCREMENT,
    `parent_id`   BIGINT       DEFAULT 0                 COMMENT '父菜单ID,0=顶级',
    `name`        VARCHAR(50)  NOT NULL                  COMMENT '菜单名',
    `type`        TINYINT      NOT NULL                  COMMENT '1=目录 2=菜单 3=按钮',
    `permission`  VARCHAR(100)                          COMMENT '权限标识(按钮才有)',
    `path`        VARCHAR(200)                          COMMENT '路由路径',
    `component`   VARCHAR(200)                          COMMENT '前端组件路径',
    `icon`        VARCHAR(50)                           COMMENT '图标',
    `sort`        INT          DEFAULT 0                 COMMENT '排序',
    `visible`     TINYINT      DEFAULT 1                 COMMENT '0=隐藏 1=显示',
    `status`      TINYINT      DEFAULT 1                 COMMENT '0=禁用 1=正常',
    `create_time` DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_deleted`  TINYINT      DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_parent` (`parent_id`),
    KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='菜单权限表';

菜单三类型说明:

type含义示例
1目录"系统管理"(左侧一级)
2菜单"用户管理"(/system/user)
3按钮"用户新增"(permission=user:create)

五、部门表(sys_dept) ​

sql
CREATE TABLE `sys_dept` (
    `id`         BIGINT      NOT NULL AUTO_INCREMENT,
    `parent_id`  BIGINT      DEFAULT 0,
    `name`       VARCHAR(50) NOT NULL,
    `code`       VARCHAR(50)                          COMMENT '部门编码',
    `leader`     VARCHAR(50)                          COMMENT '负责人',
    `phone`      VARCHAR(20),
    `email`      VARCHAR(100),
    `sort`       INT         DEFAULT 0,
    `status`     TINYINT     DEFAULT 1,
    `create_time` DATETIME   DEFAULT CURRENT_TIMESTAMP,
    `update_time` DATETIME   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_deleted` TINYINT     DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

树形结构:通过 parent_id 自关联实现。parent_id=0 表示顶级。

⚠️ 坑 2:不要在同一张表里存"父级路径"(如 path=/1/2/3/)。这违背范式,且每次部门变动都要更新所有祖先节点。直接用 parent_id 递归查就行,效率问题加缓存解决。

六、关系表(中间表) ​

sql
-- 用户-角色
CREATE TABLE `sys_user_role` (
    `user_id` BIGINT NOT NULL,
    `role_id` BIGINT NOT NULL,
    PRIMARY KEY (`user_id`, `role_id`),
    KEY `idx_role` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关系';

-- 角色-菜单
CREATE TABLE `sys_role_menu` (
    `role_id` BIGINT NOT NULL,
    `menu_id` BIGINT NOT NULL,
    PRIMARY KEY (`role_id`, `menu_id`),
    KEY `idx_menu` (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色菜单关系';

-- 角色-部门(数据权限自定义时用)
CREATE TABLE `sys_role_dept` (
    `role_id` BIGINT NOT NULL,
    `dept_id` BIGINT NOT NULL,
    PRIMARY KEY (`role_id`, `dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色部门关系';

中间表设计原则:

  • 联合主键 (user_id, role_id) 防重复
  • 反向索引 idx_role 用于"查这个角色下的所有用户"

七、日志表 ​

sql
CREATE TABLE `sys_operation_log` (
    `id`              BIGINT       NOT NULL AUTO_INCREMENT,
    `user_id`         BIGINT                                COMMENT '操作用户',
    `username`        VARCHAR(50)                           COMMENT '用户名(冗余便于查询)',
    `module`          VARCHAR(50)                           COMMENT '模块',
    `action`          VARCHAR(100)                          COMMENT '操作',
    `method`          VARCHAR(200)                          COMMENT '方法签名',
    `request_url`     VARCHAR(255),
    `request_method`  VARCHAR(10)                           COMMENT 'GET/POST',
    `request_params`  TEXT                                  COMMENT '请求参数JSON',
    `ip`              VARCHAR(50),
    `cost`            BIGINT                                COMMENT '耗时ms',
    `status`          TINYINT      DEFAULT 1                COMMENT '1=成功 0=失败',
    `error_msg`       TEXT,
    `create_time`     DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_module` (`module`),
    KEY `idx_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志';

CREATE TABLE `sys_login_log` (
    `id`         BIGINT       NOT NULL AUTO_INCREMENT,
    `username`   VARCHAR(50)  NOT NULL,
    `ip`         VARCHAR(50),
    `user_agent` VARCHAR(500),
    `status`     TINYINT      NOT NULL                       COMMENT '1=成功 0=失败',
    `message`    VARCHAR(255)                               COMMENT '失败原因',
    `login_time` DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_username` (`username`),
    KEY `idx_time` (`login_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录日志';

日志表特点:

  • 只插入,不更新不删除(后期清理用 DELETE WHERE create_time < ?)
  • 不强制外键(性能 + 分表方便)
  • 用 TEXT 存 JSON 参数,免去动态字段

⚠️ 坑 3:日志表千万别用外键约束。日志后期会按月分表,加了外键分表 SQL 写起来更麻烦,而且插入性能下降 10-20%。

八、必备审计字段 ​

每张业务表都加这四个字段:

java
public abstract class BaseEntity {
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;

    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;     // 创建时间

    @TableField(fill = FieldFill.INSERT)
    private Long createBy;                // 创建人

    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;     // 更新时间

    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Long updateBy;                // 更新人

    @TableLogic
    @TableField(select = false)
    private Integer deleted;               // 逻辑删除
}

九、本章小结 ​

要点关键
业务表user / role / menu / dept / dict
关系表user_role / role_menu / role_dept
日志表operation_log / login_log
必备字段id / 4 审计字段 / 软删 / 乐观锁
字符集utf8mb4(支持 emoji)
引擎InnoDB(支持事务、行锁)
软删is_deleted 联合唯一键

动手练习 ​

  1. 写建表 SQL:在本地 MySQL 执行本章所有建表脚本,验证表都建成功
  2. 设计字典表:为 "性别"、"用户状态" 两个枚举,设计 sys_dict 数据
  3. 加索引:对 sys_operation_log 添加 (user_id, create_time) 复合索引,解释为什么

下一章:第 5 章:搭建脚手架 →

实战第 4 / 13 章

查看全部章节 →
← 上一章
第 02 章
需求分析
← 前一章
第 03 章
架构设计
下一章 →
第 05 章
搭建脚手架
再下一章 →
第 06 章
用户模块

本站基于 VitePress 构建 · 由 StackHub 团队维护

Copyright © 2026-present StackHub