MySQL多表联合查询与数据合并方法

MySQL合并多个表的常见方法有联合查询（JOIN）和数据合并（UNION或INSERT SELECT）。1. JOIN用于逻辑连接表，按关联列组合行，包括INNER JOIN（仅匹配行）、LEFT JOIN（左表全显示）、RIGHT JOIN（右表全显示）及通过LEFT+RIGHT JOIN模拟的FULL JOIN。2. UNION用于合并结果集，要求列数和类型一致，UNION ALL保留重复行，性能更优；UNION自动去重。3. 数据可合并到新表（CREATE TABLE ... SELECT）或已有表（INSERT INTO ... SELECT）。4. 优化JOIN性能需关注索引、避免函数干扰、定期更新统计信息。选择JOIN类型依据需求：INNER适合双表匹配，LEFT/RIGHT适合单边全显示，FULL适合双边全显示；UNION适合需去重场景，UNION ALL适合允许重复的高效合并。

MySQL合并多个表，其实通常指的是联合查询或者数据合并。联合查询更像是在逻辑上把多个表连接起来，而数据合并则是物理上将数据复制到一张新表或者已存在的表中。

联合查询（JOIN）和数据合并（UNION或INSERT SELECT）是两种主要策略，选择哪种取决于你的需求。

JOIN：联合查询

什么是JOIN？

JOIN操作允许你基于两个或多个表之间的相关列，将行连接起来。这是一种动态的方式，数据仍然存储在各自的表中，查询时才组合结果。

JOIN的类型：

• INNER JOIN： 返回两个表中匹配的行。只有在两个表中都有对应值的行才会被包含在结果集中。
• LEFT JOIN (或 LEFT OUTER JOIN)： 返回左表的所有行，以及右表中与左表匹配的行。如果右表中没有匹配的行，则右表的列返回 NULL。
• RIGHT JOIN (或 RIGHT OUTER JOIN)： 返回右表的所有行，以及左表中与右表匹配的行。如果左表中没有匹配的行，则左表的列返回 NULL。
• FULL JOIN (或 FULL OUTER JOIN)： 返回左表和右表的所有行。如果某个表中没有匹配的行，则另一表的列返回 NULL。MySQL原生不支持FULL JOIN，但可以通过UNION ALL结合LEFT JOIN和RIGHT JOIN模拟。

示例：

假设我们有两个表：customers 和 orders。

customers 表：

orders 表：

使用 INNER JOIN 查询：

SELECT
    customers.customer_name,
    orders.order_id,
    orders.order_date
FROM
    customers
INNER JOIN
    orders ON customers.customer_id = orders.customer_id;

结果：

性能考虑：

JOIN操作的性能取决于多个因素，包括表的大小、索引的使用情况和JOIN的类型。确保在JOIN列上创建索引，并根据查询需求选择合适的JOIN类型。大的表做JOIN操作时要特别注意，避免全表扫描。

UNION：数据合并

什么是UNION？

UNION操作用于将两个或多个SELECT语句的结果集合并成一个结果集。每个SELECT语句必须返回相同数量和类型的列。

UNION vs UNION ALL：

• UNION： 去除重复的行。
• UNION ALL： 不去除重复的行，保留所有行。

示例：

假设我们有两个表：table1 和 table2，结构相同。

table1 表：

table2 表：

使用 UNION 查询：

SELECT id, name FROM table1
UNION
SELECT id, name FROM table2;

结果：

使用 UNION ALL 查询：

SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;

结果：

数据合并到新表或已存在表：

可以使用CREATE TABLE ... SELECT 或 INSERT INTO ... SELECT 语句将UNION的结果合并到新表或已存在的表中。

-- 创建新表并合并数据
CREATE TABLE combined_table AS
SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;

-- 合并数据到已存在表
INSERT INTO existing_table (id, name)
SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;

注意事项：

• 确保SELECT语句返回的列的数量和类型匹配。
• 如果使用UNION ALL，结果集中可能包含重复的行。
• 合并到已存在表时，确保目标表具有与源表兼容的结构。

多表联合查询时遇到性能瓶颈怎么办？

优化多表联合查询的性能，需要综合考虑多个方面。索引是关键，确保JOIN字段上有索引。避免在WHERE子句中使用函数或表达式，这会导致索引失效。表的大小也会影响性能，可以考虑对大表进行分区或分表。查询优化器会根据统计信息选择最佳的执行计划，定期更新统计信息可以帮助优化器做出更好的决策。最后，如果查询过于复杂，可以考虑重构查询，或者使用物化视图来预先计算结果。

如何选择合适的JOIN类型？

选择JOIN类型取决于你的业务需求。INNER JOIN返回两个表中匹配的行，适合需要同时存在于两个表中的数据。LEFT JOIN返回左表的所有行，以及右表中与左表匹配的行，适合需要左表所有数据，并可选地包含右表匹配数据的情况。RIGHT JOIN与LEFT JOIN类似，但返回右表的所有行。FULL JOIN返回两个表的所有行，适合需要两个表所有数据的情况。

UNION ALL 和 UNION 的使用场景有什么区别？

UNION ALL保留所有行，包括重复行，而UNION会去除重复行。UNION ALL的性能通常比UNION更好，因为它不需要进行去重操作。如果你的数据中不存在重复行，或者允许重复行存在，那么应该使用UNION ALL。如果需要确保结果集中不包含重复行，那么应该使用UNION。UNION ALL适合日志数据合并、数据归档等场景，而UNION适合需要去重的统计分析等场景。