1
2
3
4
5

关系图：

  用户(User) ──M:N──> 角色(Role) ──M:N──> 权限(Permission)

  user ──> user_role ──> role ──> role_permission ──> permission

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

-- 1. 用户表（核心字段，完整用户表参考 MySQL 建表设计规范）
CREATE TABLE `user` (
    `id`         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `username`   VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '用户名',
    `password`   CHAR(60)         NOT NULL DEFAULT '' COMMENT '密码哈希 (bcrypt)',
    `status`     TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：0=禁用，1=正常',
    `created_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

-- 2. 角色表
CREATE TABLE `role` (
    `id`          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `code`        VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '角色编码 (唯一标识)',
    `name`        VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '角色名称',
    `description` VARCHAR(128)     NOT NULL DEFAULT '' COMMENT '角色描述',
    `sort`        INT              NOT NULL DEFAULT 0 COMMENT '排序值',
    `status`      TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：0=禁用，1=正常',
    `created_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_role_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色表';

-- 3. 权限表（菜单 + 按钮 + API 统一管理）
CREATE TABLE `permission` (
    `id`          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `parent_id`   BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '父权限 ID, 0=顶级',
    `code`        VARCHAR(64)      NOT NULL DEFAULT '' COMMENT '权限编码，如 user:list, user:create',
    `name`        VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '权限名称',
    `type`        TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '类型：1=菜单，2=按钮，3=API',
    `path`        VARCHAR(128)     NOT NULL DEFAULT '' COMMENT '前端路由路径 (菜单)',
    `icon`        VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '菜单图标',
    `sort`        INT              NOT NULL DEFAULT 0 COMMENT '排序值',
    `status`      TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：0=禁用，1=正常',
    `created_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_permission_code` (`code`),
    INDEX `idx_permission_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='权限表';

-- 4. 用户 - 角色关联表
CREATE TABLE `user_role` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `user_id`    BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户 ID',
    `role_id`    BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '角色 ID',
    `created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_user_role` (`user_id`, `role_id`),
    INDEX `idx_user_role_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户 - 角色关联表';

-- 5. 角色 - 权限关联表
CREATE TABLE `role_permission` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `role_id`       BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '角色 ID',
    `permission_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '权限 ID',
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_role_permission` (`role_id`, `permission_id`),
    INDEX `idx_role_permission_permission_id` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色 - 权限关联表';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 查询用户所有权限编码（用于后端鉴权）
SELECT DISTINCT p.code
FROM user_role ur
JOIN role_permission rp ON ur.role_id = rp.role_id
JOIN permission p ON rp.permission_id = p.id
JOIN role r ON ur.role_id = r.id
WHERE ur.user_id = ?
  AND r.status = 1
  AND p.status = 1;

-- 查询用户的菜单树（用于前端渲染侧边栏）
SELECT p.id, p.parent_id, p.name, p.path, p.icon, p.sort
FROM user_role ur
JOIN role_permission rp ON ur.role_id = rp.role_id
JOIN permission p ON rp.permission_id = p.id
JOIN role r ON ur.role_id = r.id
WHERE ur.user_id = ?
  AND r.status = 1
  AND p.status = 1
  AND p.type = 1  -- 菜单类型
ORDER BY p.sort;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

CREATE TABLE `category` (
    `id`         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `parent_id`  BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '父分类 ID, 0=顶级',
    `name`       VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '分类名称',
    `level`      TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '层级：1=一级，2=二级...',
    `sort`       INT              NOT NULL DEFAULT 0 COMMENT '排序值',
    `status`     TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：0=禁用，1=正常',
    `created_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    INDEX `idx_category_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='分类表';

-- 查询直接子节点
SELECT * FROM category WHERE parent_id = ? ORDER BY sort;

-- 查询完整子树（MySQL 8.0+ CTE 递归）
WITH RECURSIVE sub_tree AS (
    SELECT id, parent_id, name, level, 1 AS depth
    FROM category
    WHERE id = ?  -- 起始节点

    UNION ALL

    SELECT c.id, c.parent_id, c.name, c.level, st.depth + 1
    FROM category c
    JOIN sub_tree st ON c.parent_id = st.id
)
SELECT * FROM sub_tree ORDER BY depth, sort;

-- 查询祖先链（从当前节点向上到根）
WITH RECURSIVE ancestors AS (
    SELECT id, parent_id, name, level
    FROM category
    WHERE id = ?  -- 当前节点

    UNION ALL

    SELECT c.id, c.parent_id, c.name, c.level
    FROM category c
    JOIN ancestors a ON c.id = a.parent_id
)
SELECT * FROM ancestors ORDER BY level;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

CREATE TABLE `category` (
    `id`         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `parent_id`  BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '父分类 ID',
    `name`       VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '分类名称',
    `path`       VARCHAR(512)     NOT NULL DEFAULT '' COMMENT '路径：/1/3/7/',
    `level`      TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '层级',
    `sort`       INT              NOT NULL DEFAULT 0 COMMENT '排序值',
    `created_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    INDEX `idx_category_parent_id` (`parent_id`),
    INDEX `idx_category_path` (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='分类表 (路径枚举)';

-- 示例数据
-- id=1, path='/1/',       name='电子产品'
-- id=3, path='/1/3/',     name='手机'
-- id=7, path='/1/3/7/',   name='智能手机'

-- 查询所有子孙节点（LIKE 前缀匹配，可走索引）
SELECT * FROM category WHERE path LIKE '/1/3/%' ORDER BY level, sort;

-- 查询祖先链（面包屑导航）
-- 已知当前节点 path = '/1/3/7/'，提取祖先 ID: 1, 3, 7
SELECT * FROM category WHERE id IN (1, 3, 7) ORDER BY level;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

-- 节点表
CREATE TABLE `category` (
    `id`         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `name`       VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '分类名称',
    `created_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='分类表';

-- 闭包关系表
CREATE TABLE `category_closure` (
    `id`          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `ancestor`    BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '祖先节点 ID',
    `descendant`  BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '后代节点 ID',
    `depth`       INT UNSIGNED     NOT NULL DEFAULT 0 COMMENT '层级深度 (自引用=0)',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_closure_ancestor_descendant` (`ancestor`, `descendant`),
    INDEX `idx_closure_descendant` (`descendant`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='分类闭包关系表';

-- 每个节点有一条自引用记录（ancestor = descendant, depth = 0）
-- 新增节点时，插入闭包关系：
INSERT INTO category_closure (ancestor, descendant, depth)
    SELECT ancestor, ?, depth + 1  -- ? = 新节点 ID
    FROM category_closure
    WHERE descendant = ?;          -- ? = 父节点 ID
-- 再插入自引用
INSERT INTO category_closure (ancestor, descendant, depth) VALUES (?, ?, 0);

-- 查询所有子孙
SELECT c.* FROM category c
JOIN category_closure cc ON c.id = cc.descendant
WHERE cc.ancestor = ? AND cc.depth > 0
ORDER BY cc.depth;

-- 查询所有祖先
SELECT c.* FROM category c
JOIN category_closure cc ON c.id = cc.ancestor
WHERE cc.descendant = ? AND cc.depth > 0
ORDER BY cc.depth DESC;

-- 查询直接子节点
SELECT c.* FROM category c
JOIN category_closure cc ON c.id = cc.descendant
WHERE cc.ancestor = ? AND cc.depth = 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

决策树：

  是否使用 MySQL 8.0+？
  ├── 是  → 层级 ≤ 5 且结构较稳定？
  │         ├── 是 → 邻接表 + CTE 递归（最简方案）
  │         └── 否 → 路径枚举（查子树快）或闭包表（查询灵活）
  └── 否  → MySQL 5.7 无 CTE
            ├── 查子树频繁 → 路径枚举
            ├── 查询灵活度要求高 → 闭包表
            └── 层级固定且 ≤ 3 → 邻接表 + 应用层递归

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 评论表（多态关联）
CREATE TABLE `comment` (
    `id`          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `user_id`     BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '评论者用户 ID',
    `target_type` VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '目标类型：article/video/product',
    `target_id`   BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '目标 ID',
    `content`     VARCHAR(500)     NOT NULL DEFAULT '' COMMENT '评论内容',
    `parent_id`   BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '父评论 ID(回复), 0=顶级评论',
    `like_count`  INT UNSIGNED     NOT NULL DEFAULT 0 COMMENT '点赞数 (冗余)',
    `status`      TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：0=隐藏，1=正常',
    `created_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    INDEX `idx_comment_target` (`target_type`, `target_id`, `status`),
    INDEX `idx_comment_user_id` (`user_id`),
    INDEX `idx_comment_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='评论表 (多态关联)';

-- 查询某篇文章的评论
SELECT * FROM comment
WHERE target_type = 'article' AND target_id = 123 AND status = 1
ORDER BY created_at DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- 标签表
CREATE TABLE `tag` (
    `id`         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `name`       VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '标签名称',
    `created_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_tag_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='标签表';

-- 标签关联表（多态）
CREATE TABLE `taggable` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `tag_id`      BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '标签 ID',
    `target_type` VARCHAR(32)     NOT NULL DEFAULT '' COMMENT '目标类型：article/product',
    `target_id`   BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '目标 ID',
    `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_taggable` (`tag_id`, `target_type`, `target_id`),
    INDEX `idx_taggable_target` (`target_type`, `target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='标签关联表 (多态)';

-- 查询文章的所有标签
SELECT t.name FROM tag t
JOIN taggable tg ON t.id = tg.tag_id
WHERE tg.target_type = 'article' AND tg.target_id = 123;

-- 查询某标签下的所有文章 ID
SELECT target_id FROM taggable
WHERE tag_id = ? AND target_type = 'article';

1
2
3
4
5
6
7

订单状态机：

  待支付(0) ──支付──→ 已支付(1) ──发货──→ 已发货(2) ──确认收货──→ 已完成(3)
     │                   │
     ├──取消──→ 已取消(4)  ├──退款──→ 退款中(5) ──退款完成──→ 已退款(6)
     │                   │
     └──超时──→ 已关闭(7)  └──超时未发货──→ 已取消(4)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 状态变更日志（通用，可用于订单/审批/工单等）
CREATE TABLE `status_log` (
    `id`            BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `target_type`   VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '目标类型：order/approval',
    `target_id`     BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '目标 ID',
    `from_status`   TINYINT          NOT NULL DEFAULT 0 COMMENT '原状态',
    `to_status`     TINYINT          NOT NULL DEFAULT 0 COMMENT '新状态',
    `operator_id`   BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '操作人 ID',
    `operator_type` VARCHAR(16)      NOT NULL DEFAULT 'user' COMMENT '操作人类型：user/system/admin',
    `remark`        VARCHAR(256)     NOT NULL DEFAULT '' COMMENT '备注',
    `created_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    PRIMARY KEY (`id`),
    INDEX `idx_status_log_target` (`target_type`, `target_id`),
    INDEX `idx_status_log_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='状态变更日志表';

-- 查询订单的状态流转历史
SELECT from_status, to_status, remark, created_at
FROM status_log
WHERE target_type = 'order' AND target_id = ?
ORDER BY created_at;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 状态转移配置表（可选）
CREATE TABLE `status_transition` (
    `id`          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `entity_type` VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '实体类型：order',
    `from_status` TINYINT          NOT NULL DEFAULT 0 COMMENT '原状态',
    `to_status`   TINYINT          NOT NULL DEFAULT 0 COMMENT '目标状态',
    `action`      VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '触发动作：pay/ship/cancel',
    `description` VARCHAR(64)      NOT NULL DEFAULT '' COMMENT '描述',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_transition` (`entity_type`, `from_status`, `action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='状态转移配置表';

-- 示例数据
INSERT INTO status_transition (entity_type, from_status, to_status, action, description) VALUES
('order', 0, 1, 'pay',     '待支付 → 已支付'),
('order', 0, 4, 'cancel',  '待支付 → 已取消'),
('order', 1, 2, 'ship',    '已支付 → 已发货'),
('order', 1, 5, 'refund',  '已支付 → 退款中'),
('order', 2, 3, 'receive', '已发货 → 已完成');

-- 校验状态转移是否合法
SELECT COUNT(*) FROM status_transition
WHERE entity_type = 'order' AND from_status = ? AND action = ?;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 所有业务表增加 tenant_id 字段
CREATE TABLE `product` (
    `id`         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `tenant_id`  BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '租户 ID',
    `name`       VARCHAR(128)     NOT NULL DEFAULT '' COMMENT '商品名称',
    `price`      DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '售价 (元)',
    `status`     TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态',
    `created_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    INDEX `idx_product_tenant_status` (`tenant_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品表 (多租户)';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

// Laravel 全局 Scope 实现 tenant_id 自动注入
class TenantScope implements Scope
{
    public function apply(Builder $builder, Model $model)
    {
        $builder->where('tenant_id', auth()->user()->tenant_id);
    }
}

// 在模型中使用
class Product extends Model
{
    protected static function booted()
    {
        static::addGlobalScope(new TenantScope);

        // 创建时自动设置 tenant_id
        static::creating(function ($model) {
            $model->tenant_id = auth()->user()->tenant_id;
        });
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

// GORM 通过 Callback 实现
func TenantPlugin(db *gorm.DB) {
    // 查询时注入
    db.Callback().Query().Before("gorm:query").Register("tenant:query", func(db *gorm.DB) {
        tenantID := GetCurrentTenantID(db.Statement.Context)
        if tenantID > 0 {
            db.Statement.AddClause(clause.Where{
                Exprs: []clause.Expression{
                    clause.Eq{Column: "tenant_id", Value: tenantID},
                },
            })
        }
    })
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE TABLE `operation_log` (
    `id`            BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `user_id`       BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '操作人 ID',
    `username`      VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '操作人用户名 (冗余)',
    `module`        VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '功能模块：user/order/product',
    `action`        VARCHAR(16)      NOT NULL DEFAULT '' COMMENT '操作类型：create/update/delete/login',
    `target_type`   VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '目标类型',
    `target_id`     BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '目标 ID',
    `description`   VARCHAR(256)     NOT NULL DEFAULT '' COMMENT '操作描述',
    `request_method`VARCHAR(8)       NOT NULL DEFAULT '' COMMENT 'HTTP 方法：GET/POST...',
    `request_url`   VARCHAR(256)     NOT NULL DEFAULT '' COMMENT '请求 URL',
    `request_ip`    VARCHAR(45)      NOT NULL DEFAULT '' COMMENT '请求 IP(兼容 IPv6)',
    `user_agent`    VARCHAR(256)     NOT NULL DEFAULT '' COMMENT '浏览器 UA',
    `old_value`     JSON             NULL     COMMENT '变更前的值 (JSON)',
    `new_value`     JSON             NULL     COMMENT '变更后的值 (JSON)',
    `duration_ms`   INT UNSIGNED     NOT NULL DEFAULT 0 COMMENT '请求耗时 (毫秒)',
    `created_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    PRIMARY KEY (`id`),
    INDEX `idx_oplog_user_id` (`user_id`),
    INDEX `idx_oplog_module_action` (`module`, `action`),
    INDEX `idx_oplog_target` (`target_type`, `target_id`),
    INDEX `idx_oplog_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='操作日志表';

1
2
3
4
5
6
7
8
9

-- 记录用户信息修改
INSERT INTO operation_log
    (user_id, username, module, action, target_type, target_id,
     description, old_value, new_value)
VALUES
    (1, 'admin', 'user', 'update', 'user', 100,
     '修改用户昵称',
     '{"nickname": "旧昵称"}',
     '{"nickname": "新昵称"}');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 按月分区示例
CREATE TABLE `operation_log` (
    -- ...同上
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    PRIMARY KEY (`id`, `created_at`)
    -- ...索引同上
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 删除过期分区
ALTER TABLE operation_log DROP PARTITION p202601;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE `sys_config` (
    `id`          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `group`       VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '配置分组：site/payment/sms',
    `key`         VARCHAR(64)      NOT NULL DEFAULT '' COMMENT '配置键',
    `value`       TEXT             NOT NULL COMMENT '配置值',
    `type`        VARCHAR(16)      NOT NULL DEFAULT 'string' COMMENT '值类型：string/number/boolean/json',
    `description` VARCHAR(128)     NOT NULL DEFAULT '' COMMENT '配置说明',
    `created_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_config_key` (`group`, `key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统配置表';

-- 示例数据
INSERT INTO sys_config (`group`, `key`, `value`, `type`, `description`) VALUES
('site',    'site_name',     '我的商城',              'string',  '站点名称'),
('site',    'site_logo',     '/uploads/logo.png',     'string',  '站点 Logo'),
('payment', 'alipay_enabled','true',                  'boolean', '是否开启支付宝'),
('sms',     'sms_provider',  'aliyun',                'string',  '短信服务商'),
('sms',     'sms_sign',      '我的商城',              'string',  '短信签名');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- 字典类型表
CREATE TABLE `dict_type` (
    `id`          BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `code`        VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '字典编码：gender/order_status',
    `name`        VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '字典名称',
    `description` VARCHAR(128)     NOT NULL DEFAULT '' COMMENT '描述',
    `status`      TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：0=禁用，1=正常',
    `created_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_dict_type_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='字典类型表';

-- 字典数据表
CREATE TABLE `dict_data` (
    `id`           BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `dict_type_id` BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '字典类型 ID',
    `label`        VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '显示标签',
    `value`        VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '字典值',
    `sort`         INT              NOT NULL DEFAULT 0 COMMENT '排序',
    `status`       TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：0=禁用，1=正常',
    `remark`       VARCHAR(128)     NOT NULL DEFAULT '' COMMENT '备注',
    `created_at`   DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at`   DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    INDEX `idx_dict_data_type_id` (`dict_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='字典数据表';

-- 示例数据
INSERT INTO dict_type (code, name) VALUES
('gender',       '性别'),
('order_status', '订单状态');

INSERT INTO dict_data (dict_type_id, label, value, sort) VALUES
(1, '未知', '0', 0),
(1, '男',   '1', 1),
(1, '女',   '2', 2),
(2, '待支付', '0', 0),
(2, '已支付', '1', 1),
(2, '已发货', '2', 2),
(2, '已完成', '3', 3),
(2, '已取消', '4', 4);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

CREATE TABLE `attachment` (
    `id`            BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
    `user_id`       BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '上传者 ID',
    `target_type`   VARCHAR(32)      NOT NULL DEFAULT '' COMMENT '关联类型：product/article/avatar',
    `target_id`     BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '关联 ID',
    `file_name`     VARCHAR(128)     NOT NULL DEFAULT '' COMMENT '原始文件名',
    `file_path`     VARCHAR(256)     NOT NULL DEFAULT '' COMMENT '存储路径',
    `file_url`      VARCHAR(256)     NOT NULL DEFAULT '' COMMENT '访问 URL',
    `file_size`     INT UNSIGNED     NOT NULL DEFAULT 0 COMMENT '文件大小 (字节)',
    `file_type`     VARCHAR(16)      NOT NULL DEFAULT '' COMMENT '文件类型：image/video/document',
    `mime_type`     VARCHAR(64)      NOT NULL DEFAULT '' COMMENT 'MIME 类型',
    `storage`       VARCHAR(16)      NOT NULL DEFAULT 'local' COMMENT '存储方式：local/oss/cos/s3',
    `sort`          INT              NOT NULL DEFAULT 0 COMMENT '排序值',
    `created_at`    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    INDEX `idx_attachment_target` (`target_type`, `target_id`),
    INDEX `idx_attachment_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='附件表';