MySQL批量修改字段数据类型

问题

有一张表RULE，里面有120个以JE开头的字段，类型为tinytext，发现字段长度不够，需要修改成text类型。

1.查出所需要的列名

SELECT
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT
FROM
	information_schema.COLUMNS 
WHERE
	table_name = '表名' 
	AND table_schema = '数据库名' 

表名修改为RULE，数据库名修改为glb，添加条件查出JE开头的字段

SELECT
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT
FROM
	information_schema.COLUMNS 
WHERE
	table_name = 'RULE' 
	AND table_schema = 'glb' 
	AND COLUMN_NAME LIKE '%JE%';

2.确定修改SQL

修改一个字段的sql为：

ALTER TABLE GLB_REPROT_RULE MODIFY JE1 TEXT;

3.通过CONCAT函数批量生成修改SQL

SELECT
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT,
	CONCAT( 'ALTER TABLE RULE MODIFY ', COLUMN_NAME, ' TINYTEXT;' ) 
FROM
	information_schema.COLUMNS 
WHERE
	table_name = 'RULE' 
	AND table_schema = 'glb' 
	AND COLUMN_NAME LIKE '%JE%';

注意增加空格和分号

4.执行生成的sql

注意

修改后注释会丢失，需要重新填写注释。本例子的注释格式与字段名格式相似，可以借助变量实现注释。

-- 定义并初始化变量
SELECT
	@rownum := 0;
-- 生成sql
SELECT
	@rownum := @rownum + 1 rownum,
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT,
	CONCAT( 'ALTER TABLE RULE MODIFY ', COLUMN_NAME, ' TINYTEXT COMMENT "金额', @rownum, '";' ) 
FROM
	information_schema.COLUMNS 
WHERE
	table_name = 'RULE' 
	AND table_schema = 'glb' 
	AND COLUMN_NAME LIKE '%JE%';

注意：注释需要用""包住，否则会出错