我如何使用从光标返回的值作为mySQL过程中的表名称?
DECLARE cur CURSOR FOR
select table_name, column_name
from information_schema.columns
where table_schema = 'foo' and table_name like 'bar%';
OPEN cur;
loop1: LOOP
FETCH cur
INTO table_val, column_val;
IF no_more_rows THEN
CLOSE cur;
LEAVE loop1;
END IF;
update table_val SET column_val ...
这引发了foo.table_val doesnt exist
的错误。如何将实际表名称传递到Select语句?
将 update table_val SET column_val ...
更改为
SET @sql = CONCAT('UPDATE ', table_val, ' SET ', column_val, ' = whatever WHERE...');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
在这里阅读更多有关它的信息。
但请注意,您无法参数化表和列名。这仅适用于值。
我也将分享我的修复程序。希望它也会帮助某人。
-- set to "$$" as we have ";" inside of Procedure. MySQL will be confused.(is this part of Procedure or should I run it now?)
DELIMITER $$
CREATE PROCEDURE UpdateTable()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE _table_name CHAR(255);
DECLARE cur CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'db_name' AND table_type = "BASE TABLE";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
My_loop: LOOP
FETCH cur INTO _table_name;
SET @my_table_name = _table_name;
IF done THEN
LEAVE My_loop;
END IF;
SET FOREIGN_KEY_CHECKS = 0;
SET @stmt = CONCAT('ALTER TABLE ', @my_table_name, ' CONVERT TO CHARACTER SET utf8;');
PREPARE stmt1 FROM @stmt;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET FOREIGN_KEY_CHECKS = 1;
END LOOP;
CLOSE cur;
END$$
-- set to normal. ";"
DELIMITER ;