MySQL v5.6.51中如何动态UNION ALL多表LastInvoiceDate列值

MySQL 5.6 版本的原生视图不支持动态SQL拼接，无法通过普通视图实现自动识别新增符合条件表的效果，在不能修改现有库表结构的前提下，可使用以下两种方案实现需求：

方案1：存储过程实现（推荐，一次创建后续直接调用） #

通过查询information_schema.COLUMNS系统视图自动扫描当前库中所有包含LastInvoiceDate列的表，动态拼接查询逻辑，无需每次新增表后手动修改代码。

1. 先执行以下语句创建存储过程：

DELIMITER //
CREATE PROCEDURE QueryAllTableLastInvoiceDate()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE cur_table VARCHAR(255);
    -- 定义游标拉取所有符合列条件的表名
    DECLARE table_cur CURSOR FOR
        SELECT TABLE_NAME
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND COLUMN_NAME = 'LastInvoiceDate';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 创建临时表存储汇总结果
    DROP TEMPORARY TABLE IF EXISTS tmp_invoice_date_agg;
    CREATE TEMPORARY TABLE tmp_invoice_date_agg (
        Tablename VARCHAR(255) NOT NULL,
        LastInvoiceDate DATE DEFAULT NULL
    );

    OPEN table_cur;
    table_loop: LOOP
        FETCH table_cur INTO cur_table;
        IF done = 1 THEN
            LEAVE table_loop;
        END IF;
        -- 动态拼接SQL，若需要返回表内所有行的LastInvoiceDate，去掉MAX()即可
        SET @exec_sql = CONCAT(
            'INSERT INTO tmp_invoice_date_agg ',
            'SELECT ''', cur_table, ''' AS Tablename, MAX(LastInvoiceDate) AS LastInvoiceDate ',
            'FROM `', cur_table, '`'
        );
        PREPARE stmt FROM @exec_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE table_cur;

    -- 返回汇总结果并清理临时表
    SELECT * FROM tmp_invoice_date_agg;
    DROP TEMPORARY TABLE IF EXISTS tmp_invoice_date_agg;
END //
DELIMITER ;

2. 后续需要取数时，直接执行调用语句即可：

CALL QueryAllTableLastInvoiceDate();

返回结果和给出的示例结构完全一致，新增的包含LastInvoiceDate列的表会被自动扫描纳入查询范围。

方案2：动态生成SQL脚本（无存储过程创建权限时使用） #

如果当前账号没有创建存储过程的权限，可以先执行以下语句自动生成汇总查询的SQL：

SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT ''', TABLE_NAME, ''' AS Tablename, MAX(LastInvoiceDate) AS LastInvoiceDate FROM `', TABLE_NAME, '`'
    ) SEPARATOR ' UNION ALL '
) AS generated_sql
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLUMN_NAME = 'LastInvoiceDate';

执行后会输出拼接完成的完整UNION ALL查询语句，复制该语句直接执行就能得到目标结果。该方案不需要创建任何数据库对象，缺点是每次查询都需要先生成SQL再执行，无法像视图或存储过程一样一步调用。

注意：不要尝试通过定时器、触发器等机制自动更新普通视图的定义，这类方案会引入额外的维护风险，且在锁表、DDL异常时很容易出现查询错误，上述两种方案完全不修改现有业务表结构，适配无结构修改权限的场景。

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