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
66
67
68
69
70
71
72
73
| -- 角色备注、菜单表、角色-菜单关联；示例「账号管理」下菜单树；ADMIN 绑定全部菜单
|
| ALTER TABLE sys_role
| ADD COLUMN remark VARCHAR(512) NULL COMMENT '备注' AFTER role_name;
|
| CREATE TABLE IF NOT EXISTS sys_menu (
| id BIGINT AUTO_INCREMENT PRIMARY KEY,
| uuid VARCHAR(64) NOT NULL,
| version BIGINT NOT NULL DEFAULT 1,
| create_id BIGINT NULL,
| create_time DATETIME NULL,
| update_id BIGINT NULL,
| update_time DATETIME NULL,
| del_flag INT NOT NULL DEFAULT 0,
| pid BIGINT NOT NULL DEFAULT 0 COMMENT '父级 id，板块根为 0',
| menu_type TINYINT NOT NULL COMMENT '1板块 2页面 3权限',
| menu_name VARCHAR(128) NOT NULL,
| perm_code VARCHAR(128) NULL COMMENT '权限编码（扩展用）',
| sort_order INT NOT NULL DEFAULT 0,
| route_path VARCHAR(255) NULL COMMENT '前端路由，页面级可填',
| UNIQUE KEY uk_sys_menu_uuid (uuid),
| KEY idx_menu_pid (pid)
| );
|
| CREATE TABLE IF NOT EXISTS sys_role_menu (
| id BIGINT AUTO_INCREMENT PRIMARY KEY,
| uuid VARCHAR(64) NOT NULL,
| version BIGINT NOT NULL DEFAULT 1,
| create_id BIGINT NULL,
| create_time DATETIME NULL,
| update_id BIGINT NULL,
| update_time DATETIME NULL,
| del_flag INT NOT NULL DEFAULT 0,
| role_id BIGINT NOT NULL,
| menu_id BIGINT NOT NULL,
| UNIQUE KEY uk_sys_role_menu_uuid (uuid),
| KEY idx_rm_role (role_id),
| KEY idx_rm_menu (menu_id)
| );
|
| -- ========== 示例：账号管理（板块）→ 平台管理员 / 角色管理（页面）→ 操作权限（叶子）==========
| INSERT INTO sys_menu (uuid, version, pid, menu_type, menu_name, perm_code, sort_order, route_path, del_flag)
| VALUES (REPLACE(UUID(), '-', ''), 1, 0, 1, '账号管理', NULL, 100, NULL, 0);
| SET @menu_acct = LAST_INSERT_ID();
|
| INSERT INTO sys_menu (uuid, version, pid, menu_type, menu_name, perm_code, sort_order, route_path, del_flag)
| VALUES (REPLACE(UUID(), '-', ''), 1, @menu_acct, 2, '平台管理员', 'sys:users:page', 10, '/users', 0);
| SET @menu_users = LAST_INSERT_ID();
|
| INSERT INTO sys_menu (uuid, version, pid, menu_type, menu_name, perm_code, sort_order, route_path, del_flag) VALUES
| (REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '查看详情', 'sys:users:detail', 10, NULL, 0),
| (REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '新增用户', 'sys:users:create', 20, NULL, 0),
| (REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '编辑用户', 'sys:users:update', 30, NULL, 0),
| (REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '删除用户', 'sys:users:delete', 40, NULL, 0),
| (REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '启停账号', 'sys:users:status', 50, NULL, 0),
| (REPLACE(UUID(), '-', ''), 1, @menu_users, 3, '重置密码', 'sys:users:password', 60, NULL, 0);
|
| INSERT INTO sys_menu (uuid, version, pid, menu_type, menu_name, perm_code, sort_order, route_path, del_flag)
| VALUES (REPLACE(UUID(), '-', ''), 1, @menu_acct, 2, '角色管理', 'sys:roles:page', 20, '/roles', 0);
| SET @menu_roles = LAST_INSERT_ID();
|
| INSERT INTO sys_menu (uuid, version, pid, menu_type, menu_name, perm_code, sort_order, route_path, del_flag) VALUES
| (REPLACE(UUID(), '-', ''), 1, @menu_roles, 3, '查看详情', 'sys:roles:detail', 10, NULL, 0),
| (REPLACE(UUID(), '-', ''), 1, @menu_roles, 3, '新增角色', 'sys:roles:create', 20, NULL, 0),
| (REPLACE(UUID(), '-', ''), 1, @menu_roles, 3, '编辑角色', 'sys:roles:update', 30, NULL, 0),
| (REPLACE(UUID(), '-', ''), 1, @menu_roles, 3, '删除角色', 'sys:roles:delete', 40, NULL, 0);
|
| -- 系统管理员角色绑定当前库中全部菜单（含上列示例）
| INSERT INTO sys_role_menu (uuid, version, role_id, menu_id, del_flag)
| SELECT REPLACE(UUID(), '-', ''), 1, r.id, m.id, 0
| FROM sys_role r
| JOIN sys_menu m ON m.del_flag = 0
| WHERE r.role_code = 'ADMIN' AND r.del_flag = 0;
|
|
