批量修改mysql 字段类型

转载 https://blog.csdn.net/yangjiegang8/article/details/79605436

delimiter //
DROP PROCEDURE
IF EXISTS batch_alter_column_type ; CREATE PROCEDURE batch_alter_column_type (
tbl_name VARCHAR (128), -- 表名
from_col_type VARCHAR (32), -- 修改之前的字段类型
to_col_type VARCHAR (32) -- 修改之后的字段类型
)
BEGIN

DECLARE col_name VARCHAR (128); -- 当前字段名
DECLARE i_done INT (1) ; -- 游标结束标记
DECLARE SQL_FOR_ALTER VARCHAR (1024) ;
DECLARE mycursor CURSOR FOR SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA. COLUMNS
WHERE
CONVERT (TABLE_NAME USING utf8) COLLATE utf8_bin = CONVERT (tbl_name USING utf8) -- 字符串类型转换
AND CONVERT (data_type USING utf8) COLLATE utf8_bin IN (from_col_type) ; 
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET i_done = 1 ; 
OPEN mycursor ; 
exe_loop : -- 自定义loop名
LOOP
FETCH mycursor INTO col_name ;
IF i_done = 1 THEN
LEAVE exe_loop ;
END
IF ;
SET SQL_FOR_ALTER = CONCAT(
"ALTER TABLE ",
tbl_name,
" MODIFY COLUMN ",
col_name,
" ",
to_col_type
) ; -- 拼接SQL 
SET @SQL = SQL_FOR_ALTER ; 
PREPARE stmt
FROM
@SQL ; EXECUTE stmt ; -- 执行SQL
SET i_done = 0 ; -- 重置标记
END LOOP; 
CLOSE mycursor ;
END//

-- 例子
-- 建表
DROP TABLE
IF EXISTS `tbcountdetail` ; CREATE TABLE `tbcountdetail` (
`id` INT (11) NOT NULL,
`batch` VARCHAR (10) DEFAULT NULL,
`organId` VARCHAR (10) DEFAULT NULL,
`facId` VARCHAR (10) DEFAULT NULL,
`model` VARCHAR (10) DEFAULT NULL,
`serialNo` VARCHAR (10) DEFAULT NULL,
`status` VARCHAR (10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci ; -- 执行
CALL batch_alter_column_type (
'tbcountdetail',
'varchar',
'int(10)'
) ;