-- 其作用为批量修改一个表的一种字段(列)类型为另一种,例如把VARCHAR(255)修改成INT(128)的,纯属娱乐,风险自负。
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)'
) ;
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)'
) ;