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
74
75
76
77
78
79
80
81
82
83
84
| -- ----------------------------
| -- VIN码管理菜单补齐脚本（Mycat 兼容版）
| -- 创建日期: 2026-09-26
| -- 库: charging_pile_other
| -- 背景: 20260901_vin_management.sql 的菜单部分使用 SET @变量 语句，Mycat 2.0 不支持该写法，
| -- 导致测试库 charging_pile_other.sys_menu 中从未写入 VIN码管理菜单及按钮权限；
| -- 本脚本以子查询方式等价补齐，全部语句幂等，可重复执行。
| -- 说明: 执行顺序 -> 本脚本 -> 20260926_vin_allocate_auth.sql（额度分配按钮依赖 VIN码管理列表菜单）
| -- ----------------------------
| USE charging_pile_other;
|
| -- 1) 一级菜单：VIN码管理
| INSERT INTO `sys_menu`
| (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
| SELECT 'VIN码管理', 0, 11, '/vinManagement', NULL, 'M', '0', '0', '/vinManagement', 'car', 'admin', NOW(), 'VIN码管理父级菜单'
| FROM `sys_menu` anchor
| WHERE anchor.menu_id = 1
| AND NOT EXISTS (SELECT 1 FROM `sys_menu` x WHERE x.parent_id = 0 AND x.path = '/vinManagement');
|
| -- 2) 列表菜单：VIN码管理
| INSERT INTO `sys_menu`
| (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `remark`)
| SELECT 'VIN码管理', p.menu_id, 1, 'list', 'vin', 'C', '0', '0', '/vinManagement/list', 'car', 'admin', NOW(), 'VIN码管理列表'
| FROM `sys_menu` p
| WHERE p.parent_id = 0 AND p.path = '/vinManagement'
| AND NOT EXISTS (SELECT 1 FROM `sys_menu` x WHERE x.parent_id = p.menu_id AND x.path = 'list');
|
| -- 3) 按钮权限：新增
| INSERT INTO `sys_menu`
| (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
| SELECT '新增', c.menu_id, 1, '#', '', '', 1, 0, 'F', '0', '0', '/vinManagement/add', '#', 'admin', NOW(), '', NULL, 'VIN新增'
| FROM `sys_menu` c
| WHERE c.perms = '/vinManagement/list'
| AND NOT EXISTS (SELECT 1 FROM `sys_menu` x WHERE x.perms = '/vinManagement/add');
|
| -- 4) 按钮权限：编辑
| INSERT INTO `sys_menu`
| (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
| SELECT '编辑', c.menu_id, 2, '#', '', '', 1, 0, 'F', '0', '0', '/vinManagement/edit', '#', 'admin', NOW(), '', NULL, 'VIN编辑'
| FROM `sys_menu` c
| WHERE c.perms = '/vinManagement/list'
| AND NOT EXISTS (SELECT 1 FROM `sys_menu` x WHERE x.perms = '/vinManagement/edit');
|
| -- 5) 按钮权限：删除
| INSERT INTO `sys_menu`
| (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
| SELECT '删除', c.menu_id, 3, '#', '', '', 1, 0, 'F', '0', '0', '/vinManagement/delete', '#', 'admin', NOW(), '', NULL, 'VIN删除'
| FROM `sys_menu` c
| WHERE c.perms = '/vinManagement/list'
| AND NOT EXISTS (SELECT 1 FROM `sys_menu` x WHERE x.perms = '/vinManagement/delete');
|
| -- 6) 按钮权限：启停
| INSERT INTO `sys_menu`
| (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
| SELECT '启停', c.menu_id, 4, '#', '', '', 1, 0, 'F', '0', '0', '/vinManagement/status', '#', 'admin', NOW(), '', NULL, 'VIN启停'
| FROM `sys_menu` c
| WHERE c.perms = '/vinManagement/list'
| AND NOT EXISTS (SELECT 1 FROM `sys_menu` x WHERE x.perms = '/vinManagement/status');
|
| -- 7) 按钮权限：导入
| INSERT INTO `sys_menu`
| (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
| SELECT '导入', c.menu_id, 5, '#', '', '', 1, 0, 'F', '0', '0', '/vinManagement/import', '#', 'admin', NOW(), '', NULL, 'VIN导入'
| FROM `sys_menu` c
| WHERE c.perms = '/vinManagement/list'
| AND NOT EXISTS (SELECT 1 FROM `sys_menu` x WHERE x.perms = '/vinManagement/import');
|
| -- 8) 按钮权限：导出
| INSERT INTO `sys_menu`
| (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
| SELECT '导出', c.menu_id, 6, '#', '', '', 1, 0, 'F', '0', '0', '/vinManagement/export', '#', 'admin', NOW(), '', NULL, 'VIN导出'
| FROM `sys_menu` c
| WHERE c.perms = '/vinManagement/list'
| AND NOT EXISTS (SELECT 1 FROM `sys_menu` x WHERE x.perms = '/vinManagement/export');
|
| -- 9) 管理员角色授权（VIN码管理父菜单、列表菜单及全部按钮）
| -- 注意: OR 与 AND 的优先级，务必用括号把"菜单范围"整体框住，否则父菜单分支会绕过幂等校验（重复执行报主键冲突）
| INSERT INTO `sys_role_menu` (`role_id`, `menu_id`)
| SELECT 1, m.menu_id
| FROM `sys_menu` m
| WHERE ((m.parent_id = 0 AND m.path = '/vinManagement')
| OR m.perms IN ('/vinManagement/list', '/vinManagement/add', '/vinManagement/edit', '/vinManagement/delete',
| '/vinManagement/status', '/vinManagement/import', '/vinManagement/export', '/vinManagement/allocate'))
| AND NOT EXISTS (SELECT 1 FROM `sys_role_menu` x WHERE x.role_id = 1 AND x.menu_id = m.menu_id);
|
|
