MySQL UNION 操作符详细说明

目录

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 语句的结构。
• 适用场景：
  • 适合数据报表、分析查询。
  • 不适合高频事务处理，因为它可能影响性能。

通过以上说明，您应该能理解 UNION 操作符的核心用法。在实际应用中，根据需求选择 UNION 或 UNION ALL，并注意优化查询。