mysql存储过程循环分割字符串示例：

业务逻辑： 在sys_menu表中 inster添加菜单数据，在sys_roles_menus表中给菜单分配权限 

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
-- 创建菜单的存储过程 并为菜单赋权限  默认权限admin roleId = 1   
CREATE  PROCEDURE `kq`(IN roleId INT)
begin
  declare i int default 0;
  declare j int default 0;
	
  -- 记录所有的inster 之后的自增主键id	
  declare k   varchar(255) default '';
	
  -- 定义用于分割字符串的变量	
  declare _next TEXT DEFAULT NULL;
  declare _nextlen INT DEFAULT NULL;
  declare _value TEXT DEFAULT NULL;


INSERT INTO `test`.`sys_menu`(`pid`, `sub_count`, `type`, `title`, `name`, `component`, `menu_sort`, `icon`, `path`, `i_frame`, `cache`, `hidden`, `permission`, `create_by`, `update_by`, `create_time`, `update_time`) VALUES ( NULL, 4, 0, '人员温度管理', NULL, NULL, 999, 'anq', 'kq', b'0', b'0', b'0', NULL, 'admin', 'admin', now(), null);
-- 拿到inster 之后的自增主键id  以下数据依赖它的id
set i = LAST_INSERT_ID();
-- 记录每一条的主键id k 用于sys_roles_menus表的角色分配
set k = i; 
INSERT INTO `test`.`sys_menu`( `pid`, `sub_count`, `type`, `title`, `name`, `component`, `menu_sort`, `icon`, `path`, `i_frame`, `cache`, `hidden`, `permission`, `create_by`, `update_by`, `create_time`, `update_time`) VALUES (i, 0, 1, '异常记录', 'error', 'kq/error/index', 1, 'Steve-Jobs', 'error', b'0', b'0', b'0', 'errorRecord:list', 'admin', 'admin', now(), null);
set k = CONCAT(k , ',' ,LAST_INSERT_ID()); 
INSERT INTO `test`.`sys_menu`( `pid`, `sub_count`, `type`, `title`, `name`, `component`, `menu_sort`, `icon`, `path`, `i_frame`, `cache`, `hidden`, `permission`, `create_by`, `update_by`, `create_time`, `update_time`) VALUES (i, 0, 1, '测温异常 ', 'temperature', 'kq/temperature/index', 2, 'user', 'temperature', b'0', b'0', b'0', 'temperatureError:list', 'admin', 'admin', now(), null);
set k = CONCAT(k , ',' ,LAST_INSERT_ID());
INSERT INTO `test`.`sys_menu`( `pid`, `sub_count`, `type`, `title`, `name`, `component`, `menu_sort`, `icon`, `path`, `i_frame`, `cache`, `hidden`, `permission`, `create_by`, `update_by`, `create_time`, `update_time`) VALUES (i, 0, 1, '出入记录', 'inOut', 'kq/inOut/index', 3, 'link', 'inOut', b'0', b'0', b'0', 'inOutRecord:list', 'admin', 'admin', now(), null);
set k = CONCAT(k , ',' ,LAST_INSERT_ID()); 
INSERT INTO `test`.`sys_menu`(`pid`, `sub_count`, `type`, `title`, `name`, `component`, `menu_sort`, `icon`, `path`, `i_frame`, `cache`, `hidden`, `permission`, `create_by`, `update_by`, `create_time`, `update_time`) VALUES (i, 2, 1, '考勤统计', 'errorPerson', 'kq/errorPerson/index', 4, 'permission', 'errorPerson', b'0', b'0', b'0', 'kqCount:list', 'admin', 'admin', now(), null);
set j = LAST_INSERT_ID();
set k = CONCAT(k , ',' ,j);
INSERT INTO `test`.`sys_menu`(`pid`, `sub_count`, `type`, `title`, `name`, `component`, `menu_sort`, `icon`, `path`, `i_frame`, `cache`, `hidden`, `permission`, `create_by`, `update_by`, `create_time`, `update_time`) VALUES (j, 0, 2, '缺勤列表', NULL, NULL, 999, NULL, NULL, b'0', b'0', b'0', 'notPerson:list', 'admin', 'admin', now(), null);
set k = CONCAT(k , ',' ,LAST_INSERT_ID()); 
INSERT INTO `test`.`sys_menu`(`pid`, `sub_count`, `type`, `title`, `name`, `component`, `menu_sort`, `icon`, `path`, `i_frame`, `cache`, `hidden`, `permission`, `create_by`, `update_by`, `create_time`, `update_time`) VALUES (j, 0, 2, '异常列表', NULL, NULL, 999, NULL, NULL, b'0', b'0', b'0', 'errorPerson:list', 'admin', 'admin', now(), null);
set k = CONCAT(k , ',' ,LAST_INSERT_ID()); 


-- k = '151,152,153,154,155,156,157'
-- 循环分割 k 字符串
iterator:
LOOP
  IF LENGTH(TRIM(k)) = 0 OR k IS NULL THEN
    LEAVE iterator;
  END IF;

  SET _next = SUBSTRING_INDEX(k,',',1);

  SET _nextlen = LENGTH(_next);

  SET _value = TRIM(_next);
	
-- 拿到分割好的字符串插入表中
  INSERT INTO `test`.`sys_roles_menus`(`menu_id`, `role_id`) VALUES (_value, roleId );

  SET k = INSERT(k,1,_nextlen + 1,'');
END LOOP;
commit;
END





-- 调用存储过程插入菜单 传入你要分配的角色id   默认admin角色id 是1  
call kq(1);

-- 删除存储过程
DROP PROCEDURE kq;