MySQL UNION 操作符详细说明

1. UNION 操作符简介

2. 基本语法

3. 使用规则和限制

4. UNION vs UNION ALL

5. 示例演示

6. 注意事项

MySQL UNION 操作符详细说明

MySQL 中的 UNION 操作符用于合并两个或多个 SELECT 语句的结果集，生成一个单一的结果集。它常用于从多个表或查询中提取数据并进行整合。以下是其详细说明，我将从基本概念、语法、规则、示例和注意事项等方面逐步解释。

1. UNION 操作符简介

UNION 操作符将多个 SELECT 语句的结果合并为一个结果集。
默认情况下，UNION 会自动去除重复行（即结果中只保留唯一行）。如果不需要去重，可以使用 UNION ALL。
它适用于需要组合相关数据但存储在不同表或查询中的场景，例如合并销售记录或用户数据。

2. 基本语法

UNION 的基本语法结构如下：

SELECT column1, column2, ... FROM table1
UNION [ALL]
SELECT column1, column2, ... FROM table2
[UNION [ALL]
SELECT column1, column2, ... FROM table3 ...]
[ORDER BY ...]
[LIMIT ...];

关键词解释：
- SELECT：每个 UNION 必须包含至少两个 SELECT 语句。
- UNION 或 UNION ALL：UNION 去重，UNION ALL 保留所有行（包括重复）。
- ORDER BY：可选项，用于对整个结果集排序（必须放在所有 SELECT 语句后）。
- LIMIT：可选项，限制返回的行数。
注意：所有 SELECT 语句的列数和顺序必须一致。

3. 使用规则和限制

列数和顺序：
- 每个 SELECT 语句必须返回相同数量的列，记为 $n$（其中 $n$ 是列数）。
- 对应列的数据类型必须兼容（例如，整数列不能与字符串列直接合并）。
列名处理：
- 结果集的列名默认使用第一个 SELECT 语句的列名。
- 如果列名不同，后续 SELECT 的列名会被忽略。
重复行处理：
- UNION：自动去除重复行（基于所有列的值比较）。
- UNION ALL：保留所有行，不检查重复，性能更高。
其他限制：
- UNION 不能用于合并 INSERT, UPDATE, 或 DELETE 语句。
- 如果使用 ORDER BY 或 LIMIT，它们必须放在最后一个 SELECT 语句后。
- 子查询中可以使用 UNION。

4. UNION vs UNION ALL

区别：
- UNION：执行去重操作，适用于需要唯一结果的场景。例如，合并用户 ID 列表时去除重复。
- UNION ALL：不执行去重，适用于需要完整数据或性能优先的场景。例如，合并日志记录时保留所有条目。
性能考虑：
- UNION 由于需要去重，可能比 UNION ALL 慢，尤其当数据量大时。
- 在不需要去重时，优先使用 UNION ALL 以提高效率。

5. 示例演示

以下是一个简单示例，展示如何合并两个表的数据：

场景：有两个表 employees 和 contractors，都包含 name 和 salary 列。需要合并所有人员的姓名和工资。

-- 创建示例表（实际使用时无需此步）
CREATE TABLE employees (name VARCHAR(50),salary INT
);
INSERT INTO employees VALUES ('Alice', 50000), ('Bob', 60000);CREATE TABLE contractors (name VARCHAR(50),salary INT
);
INSERT INTO contractors VALUES ('Bob', 60000), ('Charlie', 70000);-- 使用 UNION 合并并去重
SELECT name, salary FROM employees
UNION
SELECT name, salary FROM contractors
ORDER BY salary DESC;

结果解释：
- 原始数据：employees 有 ('Alice', 50000), ('Bob', 60000)；contractors 有 ('Bob', 60000), ('Charlie', 70000)。
- UNION 输出：去重后结果为 ('Alice', 50000), ('Bob', 60000), ('Charlie', 70000)。注意 'Bob' 只出现一次。
- 如果使用 UNION ALL：结果为 ('Alice', 50000), ('Bob', 60000), ('Bob', 60000), ('Charlie', 70000)。

6. 注意事项

性能优化：
- 对大表使用 UNION 时，确保 SELECT 语句高效（例如，添加索引）。
- 优先用 UNION ALL 除非需要去重。
排序和分页：
- ORDER BY 必须放在最后，影响整个结果集。例如：
```
SELECT name FROM table1
UNION
SELECT name FROM table2
ORDER BY name;
```
- LIMIT 同样放在最后，限制总行数。
数据类型兼容：
- 如果列类型不兼容（如 INT 和 VARCHAR），MySQL 会尝试隐式转换，但可能导致错误或数据丢失。建议手动转换，例如：
```
SELECT CAST(age AS CHAR) AS info FROM users
UNION
SELECT name FROM employees;
```
错误处理：
- 常见错误包括列数不匹配（Error 1222）或类型不兼容（Error 1265）。检查每个 SELECT 语句的结构。
适用场景：
- 适合数据报表、分析查询。
- 不适合高频事务处理，因为它可能影响性能。