如何在MySQL存储过程中将逗号分隔的值循环为select语句

Question

我必须将逗号分隔的值发送到select语句中，在该语句中它将通过@sql语句更新值。我在所有数据库中都有公共表，我需要在过程中通过一个update语句更新表列。

例如:下面的Input Param will be ('DataBase1','Database2',....., 'Database10')是示例过程：

DELIMITER &&  
    CREATE PROCEDURE update_stmt (IN DBName varchar(100))  
    BEGIN  
    Declare DBName = @DB;

    **comma seperated values loop and placed into the @DB**
    use @DB;
    SELECT concat(update @DB.sample SET COL = 0 where ID = \'',ID,'\','; ) as stmt FROM 
      Test.Sample into @s; 
       SET @sql = @s
       PREPARE stmt from @sql;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;
       
    END &&  
    DELIMITER ; 

因此更新语句将在每个数据库中执行。

Answer 1

这是另一个方法。我不试图拆分逗号分隔的字符串，我使用它与FIND_IN_SET()匹配INFORMATION_SCHEMA.TABLES中的模式名称，这将筛选到实际存在的列表中的模式，以及该模式中实际存在的表。

然后使用游标在匹配的行上循环，这样就不必拆分任何字符串，这在存储过程中是很尴尬的。

我假设您也希望指定要更新的行的id，所以我将其添加到过程参数中。

还请注意在创建@sql时使用引号。您可以连接字符串，但这些字符串必须与任何其他字符串文本一样以引号分隔。变量不能在引用的字符串中。在MySQL中没有任何特性可以扩展字符串文本中的变量。

DELIMITER &&

CREATE PROCEDURE update_stmt (IN schema_name_list VARCHAR(100), IN in_id INT)
BEGIN
  DECLARE done INT DEFAULT false;
  DECLARE schema_name VARCHAR(64);
  DECLARE cur1 CURSOR FOR 
    SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_NAME = 'sample' AND FIND_IN_SET(TABLE_SCHEMA, schema_name_list);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

  SET @id = in_id;

  OPEN cur1;

  schema_loop: LOOP
    FETCH cur1 INTO schema_name;
    IF done THEN
      LEAVE schema_loop;
    END IF;
    SET @sql = CONCAT('UPDATE `', schema_name, '`.sample SET col = 0 WHERE id = ?');
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @id;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur1;

END &&

DELIMITER ; 

坦率地说，我很少在MySQL中使用存储过程。过程语言是原始的，我看到人们试图在存储过程中执行的任务可以在几乎所有其他编程语言中更容易地完成。