如何合并MySQL中所有表？附城市经济数据数据库架构咨询

嘿，我来帮你搞定这个数据库设计的问题！你的核心痛点是单城市单表的设计导致跨表批量查询完全没法玩，下面给你两个解决方案——一个是一劳永逸的架构优化，另一个是临时救急的表合并方法，你可以根据自己的情况选：

你现在每个城市建一张表的思路属于反范式设计，虽然单个城市的时序查询方便，但一旦涉及跨城市的批量查询就彻底卡壳，而且后续新增城市、新增统计字段都会特别麻烦。正确的做法是用一张统一的表来存所有城市的时序数据：

建议的表结构 #

创建一张city_statistics表（名字可以自己改），字段设计如下：

CREATE TABLE city_statistics (
    city_name VARCHAR(50) NOT NULL, -- 如果城市有更多属性（比如省份），可以额外建一张cities字典表用city_id关联，更规范
    year INT NOT NULL,
    GDP DECIMAL(15,2) NULL,
    pop INT NULL,
    cpi DECIMAL(5,2) NULL,
    PRIMARY KEY (city_name, year) -- 联合主键，保证每个城市每年只有一条数据
);

两个需求的SQL实现 #

需求1：查询所有城市2017年的GDP、人口、CPI #

SELECT city_name, GDP, pop, cpi 
FROM city_statistics 
WHERE year = 2017;

需求2：查询city_1在2000-2017全时段的所有数据 #

SELECT * 
FROM city_statistics 
WHERE city_name = 'city_1' 
AND year BETWEEN 2000 AND 2017;

以后不管新增城市还是新增统计指标，直接在这张表里插数据或者加字段就行，彻底解决你现在的痛点。

如果暂时没法重构表结构，你可以用动态SQL自动生成合并语句，不用手动逐个写。以MySQL为例，步骤如下：

1. 生成合并所有表的SQL语句 #

执行下面的查询，会自动拼接出所有城市表的UNION ALL语句：

SELECT CONCAT(
    'SELECT * FROM ', 
    GROUP_CONCAT(table_name SEPARATOR ' UNION ALL SELECT * FROM ')
) AS merge_sql
FROM information_schema.tables 
WHERE table_schema = '你的数据库名称' -- 替换成你实际的数据库名
AND table_name LIKE 'city_%'; -- 假设你的城市表都是city_开头，比如city_1、city_2，根据实际情况调整匹配规则

执行后会得到类似这样的结果：

SELECT * FROM city_1 UNION ALL SELECT * FROM city_2 UNION ALL SELECT * FROM city_3 ...

2. 创建合并后的新表 #

把上面生成的SQL语句套进CREATE TABLE ... AS里，就能得到合并后的统一表：

CREATE TABLE merged_city_data AS
SELECT * FROM city_1 UNION ALL SELECT * FROM city_2 ...; -- 替换成上面生成的内容

⚠️ 注意：所有城市表的字段数量、类型必须完全一致，否则UNION ALL会报错。

3. 需求1的查询SQL #

合并完成后，查询所有城市2017年的数据就简单了：

SELECT * FROM merged_city_data WHERE year = 2017;

强烈推荐你用重构表结构的方案，这是长期来看最合理、最易维护的选择。临时合并表只是权宜之计，后续新增城市还是要重复操作，而统一表结构可以一劳永逸解决所有问题。

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