>实例

需求

已知，存在以上特征的五张表，表名有部分相同，每张表中的字段除数量不同外，其他特征都差不多，默认都为null。现在存在一个需求：

• 将这五张表中，所有使用了double或者int的字段修改为decimal类型，其他不变

解决方案

-- 定义并初始化变量
-- SELECT
-- 	@rownum := 0;
-- 生成sql
SELECT
	-- @rownum := @rownum + 1 rownum,
	TABLE_NAME,
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT,
	CONCAT( 'ALTER TABLE ',TABLE_NAME,' modify column ', COLUMN_NAME, ' decimal default null "', COLUMN_COMMENT, '";' ) 
FROM
	information_schema.COLUMNS 
WHERE
	table_name like '%alarm_setting%' 
	AND table_schema = 'csf' ;
	-- AND COLUMN_NAME LIKE '%JE%'

效果

如上，就生成了所有需要的sql，然后对生成的sql稍加处理就可以使用了。

>详解

以下内容来自: 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%';

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