sql 给多张表添加相同字段名

因业务要求，需要对现有的数据库中所有含is_deleted字段的表添加delete_time字段。

1.查询出所有含is_deleted字段的表(ps：多个库多个表)

SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_COMMENT,TABLE_NAME,TABLE_SCHEMA,(CONCAT(TABLE_SCHEMA,'.',TABLE_NAME)) as '库名+表名' 
FROM information_schema.COLUMNS 
WHERE 
TABLE_NAME in (
SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA  in ('库名','xx1','xx2','xx3')
) 
and COLUMN_NAME='is_deleted' 
and TABLE_SCHEMA  in ('库名','xx1','xx2','xx3')
ORDER BY TABLE_SCHEMA

使用sql说明

-- 查询库的表
-- SELECT TABLE_NAME,TABLE_TYPE,TABLE_COMMENT,CREATE_TIME,UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '库名'
SELECT TABLE_NAME,TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA in ('库名','xx1','xx2','xx3')

-- 查询表的字段
-- SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_NAME='表名'
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_COMMENT,TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME='xxx'

ps:查询出所有含is_deleted的表后，因为有些表中含有delete_time，需要对其去重。

2.sql说明(在表中的is_deleted字段后插入delete_time字段 类型长度备注···)

ALTER TABLE 库名.表名  ADD COLUMN `delete_time` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '数据删除时间戳：默认为0，未删除' AFTER `is_deleted`;

3.查询表中不包含 key的表

-- 查询表中不包含 key的表
SELECT
TABLE_SCHEMA,TABLE_NAME,
(CONCAT(TABLE_SCHEMA,'.',TABLE_NAME)) as '库名+表名' 
FROM (
-- 查询所有表
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA  in ('库名')
) a
WHERE 
(CONCAT(TABLE_SCHEMA,'.',TABLE_NAME)) not in  (
-- 查询所有包含key的表
SELECT
(CONCAT(TABLE_SCHEMA,'.',TABLE_NAME))
FROM information_schema.COLUMNS 
WHERE 
TABLE_NAME in (
SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA  in ('库名')
) 
and COLUMN_NAME = 'key' 
and TABLE_SCHEMA  in ('wit_ccp_nmg')
ORDER BY  TABLE_SCHEMA
)
-- 效率 locate > position > like
-- binary 区分大小写
and binary locate('copy',TABLE_NAME)=0 -- 匹配到数量

EXCEL方式：

1.将查询出来的表名复制到excel中

2.拼接sql语句(“xx”&A1&"xx")，然后复制拼接后的sql语句放入数据库中执行

="ALTER TABLE "&A1&"  ADD COLUMN `delete_time` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '数据删除时间戳：默认为0，未删除' AFTER `is_deleted`;"

直接使用sql方式：

-- 循环表名拼接sql语句
待定

其他脚本语句方式：

写for循环拼接
ps:java方式查询符合的表和筛选不需要插入的表都直接写到方法中，然后拼接成一个字符串形式的sql语句或在xml中用for循环该表集合拼接sql。
(查询出表集合-筛选-拼接sql后执行该sql或在mybatis中xml循环拼接-执行)
  StringBuffer xxx = new StringBuffer();
  for (int i = 0; i < list; i++) {
    xxx.append("ALTER TABLE "+list.table+"  ADD COLUMN `delete_time` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '数据删除时间戳：默认为0，未删除' AFTER `is_deleted`;");
  }


<!--未测试-->
<updateid="addColumn" parameterType="java.util.ArrayList">
  <foreach collection="list" item="o" separator=";">
    ALTER TABLE #{o.table}  ADD COLUMN `delete_time` bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '数据删除时间戳：默认为0，未删除' AFTER `is_deleted`;
  </foreach>
</update>