数据库批量修改语句生成查询语句

背景

在接老师开发商业性质的项目的时候，接受了一个批量修改数据库的任务，花费了自己蛮多心血特此记录一下。

任务要求

任务详情：
数据库的表结构不统一，字段列名缺失以及长度不匹配。
在现有的数据库中，每张表都有 CREATER，CREATER_ID，CREATE_DATE，UPDATER，UPDATER_ID，UPDATE_DATE等字段，编写一个工具，用于：

1. 确保一致性：每张表内的这些字段名字，类型，长度都相同
2. 统一被修改：批量修改这些字段的名字，类型，长度
   

解决思路&方案

刚开始时想复杂了，脚本学习较少，时间紧迫，于是选择从sql查询语句入手，一直往IF EXISTS钻牛角尖，但批量多表操作的拼接折磨了我很久，后续将问题简单化为将对应数据库没有字段的表添加字段列，再将所有表对应字段列的属性以及长度更新校验更正为任务要求的。

拼接代码：
注意：除CREATER其它字段sql全部省略,下为核心拼接代码

1
2
3
4
5

START TRANSACTION;
SELECT DISTINCT CONCAT('ALTER TABLE ',table_name,' ADD COLUMN CREATER VARCHAR(32);') FROM information_schema.tables WHERE table_schema = DATABASE() AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM information_schema.columns WHERE table_schema = DATABASE() AND column_name LIKE 'CREATER');

SELECT CONCAT('ALTER TABLE ',table_name,' MODIFY  COLUMN CREATER VARCHAR(32);') from information_schema.`TABLES`WHERE table_schema='agency_test';
COMMIT;

sql注意事项

SQL语句进行列名字段查询时，注意取反编写，不可直接写 column_name not like ‘字段名’，column_name like ‘字段名’逻辑为会选取发现每个表的列名与like的字段进行对比，只要有和对应字段名相同的列名及选出该表，not like会导致只要表中有与like的字段不匹配的表便会选出该表，只要表含多个列名基本便会选出导致与需求不服。

操作方法

1. 在要修改的数据库新建查询

2. 在查询里黏贴sql查询代码生成脚本的内容并运行

3. 保存该查询结果为.txt文件（或sql文件）

4. 在该文件内容开头另起一行添加 START TRANSACTION; 内容结尾另一起行添加 COMMIT;

5. 将该内容黏贴至新的查询（或运行sql文件注意指定运行sql的数据库）

6. 注意：sql查询代码生成脚本未指定生成查询的关联数据库，请在需要使用的数据库使用该查询，若想指定对应数据库请将中所有table_schema=DATABASE()修改为table_schema=’指定数据库’

• ← Previous Post
• Next Post →

...

...

00:00

00:00