给数据库特定表补充字段(用sql生成sql)

给数据库特定表补充字段(用sql生成sql)

1.确定操作表

新增一个临时表用来存需要操作表的表名

CREATE TABLE target_tables (table_name VARCHAR(255));

INSERT INTO target_tables (table_name) VALUES
('table1'),
('table2');

2.查询哪些字段需要补充，并拼接出一个alter table语句（执行这整段sql就能获取出一个alter_sql列表，稍作修改就能直接执行）

-- 替换为实际数据库名
SET @db_name = '数据库名字';

-- 生成 ALTER TABLE 语句，用于补充字段
SELECT DISTINCT
    CONCAT(
        'ALTER TABLE ', t.table_name, 
        IF(NOT EXISTS (
            SELECT 1 
            FROM information_schema.COLUMNS 
            WHERE TABLE_SCHEMA = @db_name 
              AND TABLE_NAME = t.table_name 
              AND COLUMN_NAME = 'create_user'
        ), ' ADD COLUMN create_user VARCHAR(50) NULL,', ''),
        IF(NOT EXISTS (
            SELECT 1 
            FROM information_schema.COLUMNS 
            WHERE TABLE_SCHEMA = @db_name 
              AND TABLE_NAME = t.table_name 
              AND COLUMN_NAME = 'create_time'
        ), ' ADD COLUMN create_time DATETIME NULL,', ''),
        IF(NOT EXISTS (
            SELECT 1 
            FROM information_schema.COLUMNS 
            WHERE TABLE_SCHEMA = @db_name 
              AND TABLE_NAME = t.table_name 
              AND COLUMN_NAME = 'update_user'
        ), ' ADD COLUMN update_user VARCHAR(50) NULL,', ''),
        IF(NOT EXISTS (
            SELECT 1 
            FROM information_schema.COLUMNS 
            WHERE TABLE_SCHEMA = @db_name 
              AND TABLE_NAME = t.table_name 
              AND COLUMN_NAME = 'update_time'
        ), ' ADD COLUMN update_time DATETIME NULL,', '')
    ) AS alter_sql
FROM target_tables t
WHERE EXISTS (
    SELECT 1 
    FROM information_schema.tables 
    WHERE TABLE_SCHEMA = @db_name 
      AND TABLE_NAME = t.table_name
);

3.tips:

information_schema 数据库跟 performance_schema 一样，都是 MySQL 自带的信息数据库。其中 performance_schema 用于性能分析，而 information_schema 用于存储数据库元数据(关于数据的数据)，例如数据库名、表名、列的数据类型、访问权限等。

information_schema 中的表实际上是视图，而不是基本表，因此，文件系统上没有与之相关的文件。 这里用到了information_schema中的tables和COLUMNS