将逗号分隔的输入参数发送到存储过程中，并在MYSQL中循环每个参数

Question

我有一个用逗号分隔输入参数的简单存储过程。我将发送Json或逗号分隔值数据库名称。比如DB1、DB2、DB3等。当我发送那个参数时，它会在每个数据库中加载那个表。我正在寻找循环条件和分割值。

EXEC   student_info (DBName = 'DB1,DB2,DB3,DB')
    DELIMITER &&  
    ALTER PROCEDURE student_info (IN DBName varchar(100))  
    BEGIN  
     **Splitting comma separated**
     **loop condition**  
        INSERT INTO @DBName.tbl_student(Name,Class)
        SELECT Name,Class FROM DB.student_info  ;  
    END &&  
    DELIMITER ;  

最好的方法是什么?

Answer 1

您需要使用预处理语句(参见:PREPARE)

drop procedure if exists student_info;
DELIMITER &&  
CREATE PROCEDURE student_info(IN dbname varchar(100))  
BEGIN  
    declare a CHAR(16);
    declare s VARCHAR(400);
    DECLARE cur1 CURSOR FOR
    with recursive cte as (
      select 
         @dbname as s1,
         substring_index(substring_index(@dbname,',',1),',',-1) as s2, 
         1 as x
      union all
      select 
         s1, 
         substring_index(substring_index(s1,',',x+1),',',-1), 
         x+1
      from cte 
      where x< (select length(s1)-length(replace(s1,',',''))+1)
      )
      select s2 from cte;
    open cur1;
     
    read_loop: loop
        fetch cur1 into a;
        set @s = CONCAT('INSERT INTO ',a,'.tbl_student(Name,Class) SELECT Name,Class FROM DB.student_info  ');
        PREPARE stmt1 FROM  @s;
        execute stmt1 ;
        deallocate prepare stmt1;
    END loop;
    close cur1;
END &&  
DELIMITER ;  

我用这个测试:

drop table if exists DB1.tbl_student;
drop table if exists DB2.tbl_student;
drop table if exists DB3.tbl_student;
drop table if exists DB.tbl_student;
drop table if exists DB.student_info;
create table DB.tbl_student(name varchar(100),class varchar(100));
create table DB1.tbl_student(name varchar(100),class varchar(100));
create table DB2.tbl_student(name varchar(100),class varchar(100));
create table DB3.tbl_student(name varchar(100),class varchar(100));
create table DB.student_info(name varchar(100),class varchar(100));
insert into DB.student_info values ('Willem', 'Mathematics');
insert into DB.student_info values ('John', 'Mathematics');
insert into DB.student_info values ('Trudy', 'Mathematics');
CALL   student_info ('DB1,DB2,DB3,DB');

在此之后，检查的数据将从student_info复制到不同的tbl_student表。

注意:我不会创建这些表，并复制数据，但我会使用视图:

create view db1.tbl_student as select name,class from db.student_info;

创建视图时，不需要将数据复制到另一个数据库，因为当表db.student_info中的记录被更新/删除或插入时，该视图将自动更新。

注:上面是在MySQL 8.x上测试的。使用了一些在早期版本的MySQL(如WITH)中不可用的功能。

Answer 2

(对原始问题的回答，在编辑之前)

使用FIND_IN_SET代替循环

SELECT Name,Class 
FROM student_info 
where FIND_IN_SET(ID, studentID);