如何自动实现14个order_items类表的UNION查询（缺失字段设为NULL）

当然可以实现自动化，核心是利用数据库的系统元数据表获取表和字段的结构信息，动态生成符合要求的SQL语句。以下是针对主流数据库的具体实现：

1. MySQL 实现 #

通过information_schema.COLUMNS元数据表获取表结构，执行以下SQL即可自动生成目标UNION语句：

SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT ',
        GROUP_CONCAT(
            CASE WHEN EXISTS (
                SELECT 1 FROM information_schema.COLUMNS 
                WHERE TABLE_SCHEMA = t.TABLE_SCHEMA 
                  AND TABLE_NAME = t.TABLE_NAME 
                  AND COLUMN_NAME = c.COLUMN_NAME
            ) THEN CONCAT('`', c.COLUMN_NAME, '`')
            ELSE CONCAT('NULL AS `', c.COLUMN_NAME, '`') 
            END
            SEPARATOR ', '
        ),
        ' FROM `', t.TABLE_NAME, '`'
    )
    SEPARATOR ' UNION '
) AS union_sql
FROM (
    -- 筛选所有符合命名规则的表
    SELECT TABLE_SCHEMA, TABLE_NAME 
    FROM information_schema.TABLES 
    WHERE TABLE_NAME LIKE '%_order_items'
) t
CROSS JOIN (
    -- 汇总所有表的全量字段
    SELECT DISTINCT COLUMN_NAME 
    FROM information_schema.COLUMNS 
    WHERE TABLE_NAME LIKE '%_order_items'
) c
GROUP BY t.TABLE_SCHEMA;

说明： #

• 自动匹配所有名称以_order_items结尾的表
• 对每个表自动生成SELECT语句：存在的字段直接引用，缺失的字段用NULL AS 字段名填充
• 最终输出完整的UNION拼接SQL，直接复制执行即可
• 若不需要去重，建议将' UNION '改为' UNION ALL '（性能更优）

2. PostgreSQL 实现 #

利用information_schema.columns元数据表，脚本如下：

WITH target_tables AS (
    -- 获取目标表列表
    SELECT table_schema, table_name 
    FROM information_schema.tables 
    WHERE table_name LIKE '%_order_items'
),
all_columns AS (
    -- 获取全量字段集合
    SELECT DISTINCT column_name 
    FROM information_schema.columns 
    WHERE table_name LIKE '%_order_items'
)
SELECT string_agg(
    format(
        'SELECT %s FROM %I.%I',
        string_agg(
            CASE WHEN EXISTS (
                SELECT 1 FROM information_schema.columns 
                WHERE table_schema = tt.table_schema 
                  AND table_name = tt.table_name 
                  AND column_name = ac.column_name
            ) THEN quote_ident(ac.column_name)
            ELSE format('NULL AS %I', ac.column_name)
            END,
            ', '
        ),
        tt.table_schema,
        tt.table_name
    ),
    ' UNION '
) AS union_sql
FROM target_tables tt
CROSS JOIN all_columns ac
GROUP BY tt.table_schema;

3. 注意事项 #

• 执行脚本的账号需要具备访问information_schema的权限
• 若字段名包含特殊字符，脚本已通过反引号（MySQL）或quote_ident（PostgreSQL）处理，避免语法错误
• 建议先将生成的SQL复制出来核对字段映射，确认无误后再执行

内容的提问来源于stack exchange，提问作者Fabio Manniti