MySql，拆分一个字符串并插入表中

I have two inputs for my stored procedure. One is the 'RoledID' and second one is the 'MenuIDs'. 'MenusIDs' is a list of comma separated menus ids that need to be inserted with RoledID. RoleId is just an INT and we need to put this RoledID against each MenuID. My table 'RolesMenus' contains two columns one for MenuID and one for RoleID.

我的存储过程有两个输入。一个是'RoledID',第二个是'MenuID'。 'MenusIDs'是需要使用RoledID插入的逗号分隔菜单ID列表。 RoleId只是一个INT,我们需要将这个RoledID放在每个MenuID上。我的表'RolesMenus'包含两列,一列用于MenuID,另一列用于RoleID。

Now I need to split MenuIDs and insert each MenuID with RoleID.

现在我需要拆分MenuID并使用RoleID插入每个MenuID。

How can I write a stored procedure for it?

我该如何为它编写存储过程?

3 个解决方案

#1

You can build one INSERT query (because statement allows to insert multiple records) and run it with prepared statements, e.g. -

您可以构建一个INSERT查询(因为语句允许插入多个记录)并使用预准备语句运行它,例如 -

SET @MenuIDs = '1,2,3';
SET @RoledID = 100;

SET @values = REPLACE(@MenuIDs, ',', CONCAT(', ', @RoledID, '),('));
SET @values = CONCAT('(', @values, ', ', @RoledID, ')'); -- This produces a string like this -> (1, 100),(2, 100),(3, 100)

SET @insert = CONCAT('INSERT INTO RolesMenus VALUES', @values); -- Build INSERT statement like this -> INSERT INTO RolesMenus VALUES(1, 100),(2, 100),(3, 100)

-- Execute INSERT statement
PREPARE stmt FROM @insert;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

As you see, it can be done without stored procedure.

如您所见,它可以在没有存储过程的情况下完成。

#2

Give this a go. It may need some tweaking if the MenuIDs string does not conform to 'menuId,menuId,menuId'.

放手一搏。如果MenuIDs字符串不符合'menuId,menuId,menuId',则可能需要进行一些调整。

Also I do not know what data type the menuId column is in your target table (INT?) so you may have to put some numeric checking in too (in case '1,2,3,banana,4,5' is passed in as the MenuIds input parameter).

另外我不知道menuId列在目标表(INT?)中的数据类型,所以你可能也需要进行一些数字检查(如果传入'1,2,3,banana,4,5')作为MenuIds输入参数)。

DELIMITER $$

DROP PROCEDURE IF EXISTS `insert_role_menuids`$$

CREATE PROCEDURE `insert_role_menuids`(IN RoleID INT,IN MenuIDs varchar(500))
BEGIN
declare idx,prev_idx int;
declare v_id varchar(10);

set idx := locate(',',MenuIDs,1);
set prev_idx := 1;

WHILE idx > 0 DO
 set v_id := substr(MenuIDs,prev_idx,idx-prev_idx);
 insert into RolesMenus (RoleId,MenuId) values (RoleID,v_id);
 set prev_idx := idx+1;
 set idx := locate(',',MenuIDs,prev_idx);
END WHILE;

set v_id := substr(MenuIDs,prev_idx);
insert into RolesMenus (RoleId,MenuId) values (RoleID,v_id);

END$$
DELIMITER ;

#3

AFAIK MySQL does not have a function to split strings. Here is the MySQL manual for string related functions. In the comments section should be some information about workarounds for splitting string with substring-functions but not really usable: MySQL manual

AFAIK MySQL没有拆分字符串的功能。这是与字符串相关的函数的MySQL手册。在注释部分应该是一些有关使用子字符串函数拆分字符串但不是真正可用的解决方法的信息:MySQL手册

#1