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

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

8.1.3.1 INSERT...SELECT基本语法

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

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表结构示例：

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

8.1.3.3 示例操作

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

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，可以这样做：

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部分可以是复杂的子查询，允许你进行更高级的数据筛选和转换。例如，只插入工资高于某个特定值的员工：

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用户可以高效地管理和迁移数据，从而优化数据库的性能和可维护性。希望本章的内容能够帮助你更好地理解和运用这一强大的数据操作工具。