手摸手系列之批量修改MySQL数据库所有表中某些字段的类型

在迁移老项目的数据库时,使用Navicat Premium的数据传输功能同步了表结构和数据。但是,发现某些字段的数据类型出现了错误,例如,租户ID从Oracle的NUMBER类型变成了MySQL的decimal(10),正确的应该是bigInt(20)。此外,逻辑删除标记DEL_FLAG也出错,应该是int(1),但现在是decimal类型。由于涉及到数百个表,手动更改显然不现实。下面来看看如何实现批量修改这些字段的数据类型。

1. 查询系统表,找出数据库中哪些表的哪些字段是哪种类型。

比如,查询数据库waimao中所有表中TENANT_ID字段是decimal类型的:

SELECT
	* 
FROM
	information_schema.`COLUMNS` 
WHERE
	TABLE_SCHEMA = 'waimao' 
	AND COLUMN_NAME = 'TENANT_ID' 
	AND DATA_TYPE = 'decimal'

在这里插入图片描述

2. 把需要修改的字段使用CONCAT拼接成可以执行的更新语句
SELECT
	TABLE_NAME,
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT,
	CONCAT( 'ALTER TABLE ', TABLE_NAME, ' MODIFY COLUMN ', COLUMN_NAME, ' bigint(', 20, ') COMMENT "租户ID";' )  
FROM
	information_schema.`COLUMNS` 
WHERE
	TABLE_SCHEMA = 'waimao' 
	AND COLUMN_NAME = 'TENANT_ID' 
	AND DATA_TYPE = 'decimal'

在这里插入图片描述

3. 将查询结果CONCAT拼接的语句全部复制到查询窗口运行

在这里插入图片描述

4. 同理,将DEL_FLAGdecimal改为int类型

在这里插入图片描述

5. 还需要将DEL_FLAG的默认值设置为0,同时设置注释

更改默认值并设置注释的SQL语句如下:

ALTER TABLE 表名 MODIFY DEL_FLAG INT DEFAULT 0 COMMENT 'Your comment here';
SELECT
	TABLE_NAME,
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT,
	COLUMN_DEFAULT,
	CONCAT( 'ALTER TABLE ', TABLE_NAME, ' MODIFY ', COLUMN_NAME, ' INT DEFAULT 0 COMMENT "删除标记(0正常 1已删除)";' )  
FROM
	information_schema.`COLUMNS` 
WHERE
	TABLE_SCHEMA = 'waimao' 
	AND COLUMN_NAME = 'DEL_FLAG' 
	AND DATA_TYPE = 'int'
	AND COLUMN_COMMENT = ''

在这里插入图片描述

6. 随便打开一张表查看效果

在这里插入图片描述
可以看到TENANT_IDDEL_FLAG已经成功更新。

  • 4
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值