-- 删除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)');
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)');