MySQL UNION 操作符的使用小结_Mysql

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