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>