8.1.3 使用INSERT...SELECT语句插入查询结果-MySQL从入门到精通(二)

当前位置:　首页>> 技术小册>> MySQL从入门到精通(二)

### 8.1.3 使用`INSERT...SELECT`语句插入查询结果

在MySQL中，`INSERT...SELECT`语句是一种强大的数据操作方式，它允许用户将从一个或多个表中选择（SELECT）的数据直接插入到另一个表中。这种操作在数据迁移、数据复制、数据汇总以及快速填充新表时尤为有用。通过组合`INSERT INTO`和`SELECT`语句，MySQL允许开发者执行复杂的数据操作，而无需编写额外的脚本或程序来逐行处理数据。

#### 8.1.3.1 `INSERT...SELECT`基本语法

`INSERT...SELECT`语句的基本语法如下：

```sql
INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;
```

- `target_table`：目标表，即你想要插入数据的表。
- `column1, column2, column3, ...`：目标表中的列名，用于指定`SELECT`语句中哪些列的数据将被插入。如果省略列名列表，则默认插入`SELECT`查询结果中的所有列，且这些列的顺序和类型必须与`target_table`中的列相匹配。
- `source_table`：源表，即从中选择数据的表。
- `WHERE condition`：可选条件，用于过滤`source_table`中的行，只有满足条件的行才会被选中并插入到`target_table`中。

#### 8.1.3.2 示例场景

假设我们有两个表：`employees`（员工表）和`new_department_employees`（新部门员工表）。`employees`表包含了公司所有员工的信息，而`new_department_employees`表用于存储新成立部门的员工信息。现在，我们需要将某些符合条件的员工从`employees`表转移到`new_department_employees`表中。

**employees表结构示例**：

| id | name    | department_id | salary |
|----|---------|---------------|--------|
| 1  | Alice   | 101           | 5000   |
| 2  | Bob     | 102           | 6000   |
| 3  | Charlie | 103           | 5500   |
| ...| ...     | ...           | ...    |

**new_department_employees表结构示例（初始为空）**：

| id | name    | department_id | salary |
|----|---------|---------------|--------|

#### 8.1.3.3 示例操作

假设我们要将`department_id`为102的所有员工转移到`new_department_employees`表中。可以使用以下`INSERT...SELECT`语句实现：

```sql
INSERT INTO new_department_employees (id, name, department_id, salary)
SELECT id, name, department_id, salary
FROM employees
WHERE department_id = 102;
```

执行这条语句后，`new_department_employees`表将包含所有`department_id`为102的员工信息。

#### 8.1.3.4 高级用法

##### 1. 插入部分列

如果只需要插入源表的部分列到目标表，可以在`INSERT INTO`语句中明确指定这些列。例如，如果我们只关心员工的`name`和`salary`，可以这样做：

```sql
INSERT INTO new_department_employees (name, salary)
SELECT name, salary
FROM employees
WHERE department_id = 102;
```

注意，在这种情况下，`new_department_employees`表中未被明确指定的列（如`id`和`department_id`）将根据其默认值（如果有的话）或表的定义（例如，如果某列设置了自增属性）来填充。

##### 2. 使用子查询

`INSERT...SELECT`语句中的`SELECT`部分可以是复杂的子查询，允许你进行更高级的数据筛选和转换。例如，只插入工资高于某个特定值的员工：

```sql
INSERT INTO new_department_employees (id, name, salary)
SELECT id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
```

这个例子中，我们使用了一个子查询来计算`employees`表中所有员工的平均工资，然后只插入工资高于这个平均值的员工到`new_department_employees`表中。

##### 3. 插入到多个表

虽然`INSERT...SELECT`语句直接不支持一次性将数据插入到多个表，但你可以通过编写多个这样的语句或使用事务（如果数据库支持）来模拟这一行为。事务可以确保多个`INSERT...SELECT`操作要么全部成功，要么在遇到错误时全部回滚，从而保持数据的一致性。

##### 4. 处理重复数据

在将数据插入到目标表时，可能会遇到重复数据的问题。MySQL提供了几种处理重复数据的方法，比如使用`REPLACE INTO`（这实际上是一个删除再插入的操作，可能会影响性能），或者在`INSERT INTO`语句中结合使用`ON DUPLICATE KEY UPDATE`子句来更新已存在的记录。但请注意，`INSERT...SELECT`本身并不直接提供避免重复插入的内置机制，这需要根据实际情况选择合适的策略。

#### 8.1.3.5 性能注意事项

- **索引优化**：在`source_table`和`target_table`上适当添加索引可以显著提高`INSERT...SELECT`操作的性能，尤其是在处理大量数据时。然而，索引也会增加插入操作的开销，因为MySQL需要维护索引的一致性。
- **事务和锁**：在涉及大量数据操作时，考虑使用事务来确保数据的一致性。同时，了解MySQL的锁机制，以避免长时间持有锁导致的性能问题。
- **批量插入**：如果可能，尝试将多个`INSERT...SELECT`操作合并为单个操作，或者使用批量插入技巧（如MySQL的`LOAD DATA INFILE`或`INSERT INTO ... VALUES (), (), ...`语法）来提高效率。
- **评估数据量和表结构**：在执行`INSERT...SELECT`操作之前，评估源表和目标表的数据量以及表结构。大表之间的数据迁移可能需要额外的规划，比如分阶段迁移或调整服务器配置。

通过合理使用`INSERT...SELECT`语句，MySQL用户可以高效地管理和迁移数据，从而优化数据库的性能和可维护性。希望本章的内容能够帮助你更好地理解和运用这一强大的数据操作工具。

该分类下的相关小册推荐：

MySQL从入门到精通(三)

MySQL从入门到精通(一)

MySQL必会核心问题

SQL零基础到熟练应用(增删改查)

MySQL从入门到精通(四)

MySQL从入门到精通(五)

MySQL8.0入门与实践

细说MySQL(零基础到高级应用)

MySQL 实战 45 讲