- mysql有若干库(约10个)
- 每个库有若干表(约50~100个)
- 需要检测这些表中有没有某个字段例如(isEnable),没有的则添加这个字段
- 先查出库中所有的表名
- 在查出库中,带有目标字段(isEnable)的表
- 两个表做差集(就是需要添加字段的表)
添加字段sql
ALTER TABLE `mytest1`.`no_have_need_filed_2` ADD COLUMN `isEnable` VARCHAR ( 255 ) CHARACTER
SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '目标字段';
库中所有的表名
-- 库中所有的表名
SELECT
a.table_name
FROM
information_schema.`TABLES` AS a
WHERE
a.TABLE_SCHEMA = 'mytest1';
have_need_filed_1
have_need_filed_2
no_have_need_filed_1
no_have_need_filed_2
no_have_need_filed_3
库中有指定字段的表名
-- 库中带有 isEnable 的表名
SELECT
b.table_name
FROM
information_schema.`COLUMNS` AS b
WHERE
b.TABLE_SCHEMA = 'mytest1'
AND b.column_name = 'isEnable'
have_need_filed_1
have_need_filed_2
两个结果求差集
SELECT
allTablesName
from
(-- 库中所有的表名
SELECT
a.table_name as allTablesName
FROM
information_schema.`TABLES` AS a
WHERE
a.TABLE_SCHEMA = 'mytest1'
) as allTables
left JOIN
(-- 库中带有 isEnable 的表名
SELECT
b.table_name as haveFiledTablesName
FROM
information_schema.`COLUMNS` AS b
WHERE
b.TABLE_SCHEMA = 'mytest1'
AND b.column_name = 'isEnable'
) as haveFiledTables on allTables.allTablesName = haveFiledTables.haveFiledTablesName
WHERE haveFiledTables.haveFiledTablesName is null
no_have_need_filed_1
no_have_need_filed_2
no_have_need_filed_3
求差集后,优化显示执行sql,右边添加字段,显示执行的sql语句
SELECT
allTablesName,
@rank:= CONCAT('ALTER TABLE `mytest1`.`', allTablesName, '` ADD COLUMN `isEnable` VARCHAR ( 255 ) CHARACTER
SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT \'目标字段\';') as `自定义显示列`
from
(-- 库中所有的表名
SELECT
a.table_name as allTablesName
FROM
information_schema.`TABLES` AS a
WHERE
a.TABLE_SCHEMA = 'mytest1'
) as allTables
left JOIN
(-- 库中带有 isEnable 的表名
SELECT
b.table_name as haveFiledTablesName
FROM
information_schema.`COLUMNS` AS b
WHERE
b.TABLE_SCHEMA = 'mytest1'
AND b.column_name = 'isEnable'
) as haveFiledTables on allTables.allTablesName = haveFiledTables.haveFiledTablesName
WHERE haveFiledTables.haveFiledTablesName is null
no_have_need_filed_1 ALTER TABLE `mytest1`.`no_have_need_filed_1` ADD COLUMN `isEnable` VARCHAR ( 255 ) CHARACTER
SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '目标字段';
no_have_need_filed_2 ALTER TABLE `mytest1`.`no_have_need_filed_2` ADD COLUMN `isEnable` VARCHAR ( 255 ) CHARACTER
SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '目标字段';
no_have_need_filed_3 ALTER TABLE `mytest1`.`no_have_need_filed_3` ADD COLUMN `isEnable` VARCHAR ( 255 ) CHARACTER
SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '目标字段';
-
上面的解决方法只适用于,库少表多的情况。如果库很多依然没有减少工作量。
-
可能使用存储过程结果这个问题