MySQL表名作为变量批处理

存储过程定义

CREATE PROCEDURE batchInsert(in number int) BEGIN declare i int default 1; 
DECLARE dbName VARCHAR(99); 
start TRANSACTION; 
while i <= number DO 
#循环修改表名 test_1,test_2,test_3,test_4.......... SET dbName = CONCAT("test_", i);
SET @STMT :=CONCAT("ALTER TABLE ",dbName," MODIFY COLUMN test varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL; "); 
PREPARE STMT FROM @STMT; 
EXECUTE STMT; 
set i = i+1; 
end while; 
COMMIT; 
end;

执行

call batchInsert(100);