如何高效查询按日期后缀命名的多张历史分表？

核心思路是自动生成对应日期范围的UNION ALL查询语句，避免手动拼接，以下是三种不同场景的可落地方案：

方案1：数据库端动态SQL（适用所有支持动态SQL的数据库，无需额外代码依赖） #

直接在数据库中通过SQL语句自动生成并执行查询，以MySQL为例，示例代码如下：

-- 1. 自定义查询的起止日期
SET @start_date = '2021-09-28';
SET @end_date = '2021-10-02';

-- 2. 自动拼接符合条件的表的UNION ALL查询语句
SET @query_sql = (
    SELECT GROUP_CONCAT(
        'SELECT 字段1,字段2,字段3 FROM ', table_name 
        SEPARATOR ' UNION ALL '
    )
    FROM information_schema.TABLES 
    WHERE table_name REGEXP '^History_tbl_[0-9]{4}_[0-9]{2}_[0-9]{2}$'
    AND STR_TO_DATE(RIGHT(table_name, 10), '%Y_%m_%d') BETWEEN @start_date AND @end_date
);

-- 3. 执行生成的查询语句
PREPARE exec_stmt FROM @query_sql;
EXECUTE exec_stmt;
DEALLOCATE PREPARE exec_stmt;

注意：把字段1,字段2,字段3替换为你实际需要查询的字段，不要直接用*，避免不同表字段顺序/类型不一致导致报错。

方案2：代码层拼接SQL（适用权限受限、无法使用存储过程/动态SQL的场景） #

如果你的数据库账号没有执行动态SQL的权限，可以在业务代码中生成对应日期范围的表名，自动拼接查询语句，以Python为例：

from datetime import datetime, timedelta

def generate_history_sql(start_dt: str, end_dt: str, fields: str) -> str:
    start = datetime.strptime(start_dt, "%Y-%m-%d")
    end = datetime.strptime(end_dt, "%Y-%m-%d")
    query_parts = []
    current = start
    while current <= end:
        table_name = f"History_tbl_{current.strftime('%Y_%m_%d')}"
        query_parts.append(f"SELECT {fields} FROM {table_name}")
        current += timedelta(days=1)
    return " UNION ALL ".join(query_parts)

# 调用示例
sql = generate_history_sql(
    start_dt="2021-09-28",
    end_dt="2021-10-02",
    fields="id, create_time, content, user_id"
)
# 直接执行生成的sql即可

该方案无需额外的数据库权限，适配所有数据库类型，灵活性最高。

方案3：预创建统一查询视图（适合经常需要查询跨天数据的场景） #

如果你的数据库支持视图，可以提前创建一个包含所有历史表的统一视图，之后查询直接调用视图即可，无需每次拼接：

-- 创建统一视图，新增表时需要同步更新视图定义
CREATE OR REPLACE VIEW v_history_all AS
SELECT id, create_time, content, user_id FROM History_tbl_2021_09_28
UNION ALL
SELECT id, create_time, content, user_id FROM History_tbl_2021_09_29
UNION ALL
SELECT id, create_time, content, user_id FROM History_tbl_2021_09_30
UNION ALL
SELECT id, create_time, content, user_id FROM History_tbl_2021_10_01
UNION ALL
SELECT id, create_time, content, user_id FROM History_tbl_2021_10_02;

后续查询直接执行：

SELECT * FROM v_history_all WHERE create_time BETWEEN '2021-09-28' AND '2021-10-02';

提示：绝大多数商业数据库的查询优化器支持谓词下推，会自动跳过不在查询日期范围内的表，不会扫描全量数据，性能和单独查对应表几乎一致。你可以搭配定时任务，每天新的日表生成后自动更新视图定义，无需手动维护。

通用注意事项 #

• 所有方案都要求日表的字段顺序、字段类型完全一致，建议所有查询都明确写查询字段，不要用*
• 如果查询范围超过30天，建议增加分页逻辑，避免一次性返回过多数据导致超时

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