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

Question

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

例如: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 ; 

使update语句在每个数据库中执行。

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中使用存储过程。过程语言是原始的，我看到人们试图在存储过程中完成的任务，实际上在任何其他编程语言中都可以更容易地完成。