mysql 按年份合并查询表，建议分区-526互联

如果你的表是按年份命名的，例如 table_2021, table_2022, table_2023, table_2024, table_2025，你可以考虑使用分区查询来优化查询性能

SELECT *
FROM table_2021
UNION ALL
SELECT *
FROM table_2022
-- 继续添加其他年份的查询条件

-- 如果用order排序，必须两个表字段包含着addtime
SELECT * FROM (SELECT * FROM `fa_video_comment` UNION All SELECT * FROM `fa_xhs_comment`) AS `sql` ORDER BY addtime

-- 如果你表含大量的数据，建议分区表
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    order_number VARCHAR(20),
    customer_id INT,
    order_date DATE,
    -- 其他列
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    -- 其他分区
);
--根据 order_date 列的年份进行了分区，并创建了多个分区。这样可以让查询只针对特定的分区进行，提高查询性能

--然后进行查询
SELECT *
FROM orders PARTITION (p2020, p2021) -- 指定要查询的分区，这里是2020和2021两个分区
WHERE customer_id = 111

Thinkphp也有文档，UNION第二个参数是true，填写true就是不去重，具体 thinkphp5文档查看

Db::field('name')
      ->table('think_user_0')
      ->union('SELECT name FROM table_2021')
      ->union('SELECT name FROM table_2022')
      ->select();

Db::field('name')
      ->table('think_user_0')
      ->union('SELECT name FROM table_2021',true)
      ->union('SELECT name FROM table_2022',true)
      ->select();