mysql 字符串拆分成表,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.

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

How can I write a stored procedure for it?

解决方案

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

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.