Mysql 批量修改字段存储过程

-- 删除batchUpdateColumn
DROP PROCEDURE IF EXISTS batchUpdateColumn;
-- 创建batchUpdateColumn
CREATE PROCEDURE batchUpdateColumn(IN tableName varchar(64),IN oldType varchar(64), IN newType varchar(64))  
  BEGIN
-- 定义变量，游标
DECLARE tn varchar(64);
DECLARE cn varchar(64);
DECLARE done INT;
-- 定义结果集，查出表名与字段
      DECLARE rs CURSOR FOR (SELECT TABLE_NAME as tn,COLUMN_NAME as cn FROM INFORMATION_SCHEMA.columns WHERE TABLE_SCHEMA = tableName AND COLUMN_TYPE = oldType);
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
      OPEN rs;  
-- 开始循环
read_loop: LOOP
FETCH  NEXT from rs INTO tn,cn;
            IF done THEN
                LEAVE read_loop;
             END IF;
-- 拼接修改字段sql
SET @tempsql = CONCAT('ALTER TABLE ',tn,' MODIFY COLUMN ',cn,' ', newType);
-- 执行sql
PREPARE stmt FROM @tempsql; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;
END LOOP;
      CLOSE rs;
 END;


-- 调用存储过程
call batchUpdateColumn('lyf','varchar(10)','varchar(30)');