MySQL的，拆分一个字符串，并插入到表 [英] MySql, split a string and insert into table

问题描述

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

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.

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

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

如何为它编写存储过程?

How can I write a stored procedure for it?

推荐答案

您可以构建一个INSERT查询(因为该语句允许插入多个记录)并使用

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.

这篇关于MySQL的，拆分一个字符串，并插入到表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！