文章目录

>实例需求解决方案 >详解问题1.查出所需要的列名2.确定修改SQL3.通过CONCAT函数批量生成修改SQL4.执行生成的sql注意

>实例

需求

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

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

解决方案

-- 定义并初始化变量-- SELECT-- @rownum := 0;-- 生成sqlSELECT-- @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, '";' ) FROMinformation_schema.COLUMNS WHEREtable_name like '%alarm_setting%' AND table_schema = 'csf' ;-- AND COLUMN_NAME LIKE '%JE%'

效果

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

>详解

以下内容来自: MySQL批量修改字段数据类型

问题

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

1.查出所需要的列名

SELECTCOLUMN_NAME,DATA_TYPE,COLUMN_COMMENTFROMinformation_schema.COLUMNS WHEREtable_name = '表名' AND table_schema = '数据库名'

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

SELECTCOLUMN_NAME,DATA_TYPE,COLUMN_COMMENTFROMinformation_schema.COLUMNS WHEREtable_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

SELECTCOLUMN_NAME,DATA_TYPE,COLUMN_COMMENT,CONCAT( 'ALTER TABLE RULE MODIFY ', COLUMN_NAME, ' TINYTEXT;' ) FROMinformation_schema.COLUMNS WHEREtable_name = 'RULE' AND table_schema = 'glb' AND COLUMN_NAME LIKE '%JE%';

注意增加空格和分号

4.执行生成的sql

注意

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

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

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