批量修改mysql 字段类型

本是古典何须时尚 2022-01-23 09:59 558阅读 0赞

转载 [https://blog.csdn.net/yangjiegang8/article/details/79605436][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)'  
) ;

[https_blog.csdn.net_yangjiegang8_article_details_79605436]: https://blog.csdn.net/yangjiegang8/article/details/79605436