MySQL高级讲座篇之：`INSERT INTO…SELECT…`：大数据批量导入的实现原理。

各位观众老爷们，大家好！我是今天的主讲人，咱们今天聊聊MySQL里一个非常好用的技巧：INSERT INTO...SELECT...，保证让你听完之后，感觉数据批量导入就像切西瓜一样简单！

开场白：数据，数据，还是数据！

在互联网时代，数据就是命根子！各种业务场景下，我们都需要处理海量的数据。如果你还只会用一条一条的INSERT语句往数据库里塞数据，那效率简直低到令人发指！想象一下，你要导入几百万条数据，一条一条执行，得跑到猴年马月啊？

所以，我们需要更高效的方法，INSERT INTO...SELECT...就是其中一个非常棒的选择。它可以让你从一个或多个表中查询数据，然后直接插入到另一个表中，一气呵成，简直不要太爽！

一、 INSERT INTO...SELECT... 的基本语法

先来个简单的热身，看看INSERT INTO...SELECT...的基本语法：

INSERT INTO 目标表 (列1, 列2, ...)
SELECT 列1, 列2, ...
FROM 源表
WHERE 条件;

• 目标表: 你要把数据插入到哪个表？
• (列1, 列2, ...): 目标表中要插入数据的列。 注意，这里的列的顺序和数量要和SELECT子句中的列一一对应！如果你目标表的所有列都要插入数据，可以省略这部分，但要保证SELECT子句返回的列的数量和类型和目标表完全一致。
• SELECT 列1, 列2, ... FROM 源表: 从哪个表查询数据？ 可以是单表，也可以是多表关联查询。
• WHERE 条件: 筛选条件，决定哪些数据会被插入。

举个栗子！

假设我们有两个表：users 和 temp_users。 users 表存储了正式的用户信息，temp_users 表存储了临时用户的信息，我们需要把temp_users表中的一部分数据导入到users表。

users 表结构:

列名	数据类型
id	INT
username	VARCHAR(255)
email	VARCHAR(255)
created_at	TIMESTAMP

temp_users 表结构:

列名	数据类型
id	INT
username	VARCHAR(255)
email	VARCHAR(255)
created_at	TIMESTAMP
status	ENUM(‘active’, ‘inactive’)

现在，我们要把 temp_users 表中 status 为 ‘active’ 的用户数据导入到 users 表：

INSERT INTO users (id, username, email, created_at)
SELECT id, username, email, created_at
FROM temp_users
WHERE status = 'active';

简单明了，有没有？

二、 列名不一致怎么办？

有时候，源表和目标表的列名并不完全一致，甚至数据类型也可能不一样。 别慌！我们可以用 AS 关键字来解决列名不一致的问题，用 CAST 或 CONVERT 函数来处理数据类型不一致的情况。

再举个栗子！

假设 temp_users 表的 username 列叫做 user_name，created_at 列的数据类型是字符串，我们需要把它们导入到 users 表。

INSERT INTO users (id, username, email, created_at)
SELECT id, user_name AS username, email, CAST(created_at AS TIMESTAMP) AS created_at
FROM temp_users
WHERE status = 'active';

这里，我们用 user_name AS username 把 temp_users 表的 user_name 列重命名为 username，用 CAST(created_at AS TIMESTAMP) 把字符串类型的 created_at 转换成了 TIMESTAMP 类型。

三、 如何处理自增主键？

如果目标表有自增主键，通常情况下，我们不需要指定自增主键的值，数据库会自动生成。 但是，如果你想从源表中导入自增主键的值，也是可以的，但需要注意以下几点：

1. 确保目标表的自增主键允许手动插入值。 有些数据库默认不允许手动插入自增主键的值，需要修改表的结构。
2. 确保源表的主键值不会和目标表已有的主键值冲突。 否则，会插入失败。

举个栗子！

假设 users 表的 id 列是自增主键，并且允许手动插入值。 我们想把 temp_users 表的所有数据（包括 id）导入到 users 表：

INSERT INTO users (id, username, email, created_at)
SELECT id, username, email, created_at
FROM temp_users;

注意： 在执行这条语句之前，最好先查询一下 users 表的最大 id 值，然后确保 temp_users 表的 id 值都大于这个最大值，避免冲突。

四、 分批导入，化整为零

当数据量特别大时，一次性导入可能会导致数据库压力过大，甚至崩溃。 这时候，我们可以采用分批导入的策略，把大数据分割成小批量的任务，逐步导入。

如何实现分批导入？

最常用的方法是使用 LIMIT 和 OFFSET 结合来实现分页查询，然后循环执行 INSERT INTO...SELECT... 语句。

举个栗子！

假设我们要把 huge_table 表的数据导入到 target_table 表，每次导入 1000 条数据：

SET @batch_size = 1000;
SET @offset = 0;
SET @total_count = (SELECT COUNT(*) FROM huge_table);

WHILE @offset < @total_count DO
  INSERT INTO target_table (column1, column2, ...)
  SELECT column1, column2, ...
  FROM huge_table
  LIMIT @batch_size OFFSET @offset;

  SET @offset = @offset + @batch_size;
END WHILE;

这段代码的逻辑是：

1. 定义变量: @batch_size 表示每次导入的批量大小，@offset 表示偏移量，@total_count 表示总记录数。
2. 循环导入: 使用 WHILE 循环，每次从 huge_table 表查询 @batch_size 条数据，然后插入到 target_table 表。
3. 更新偏移量: 每次循环结束后，更新 @offset 的值，使其指向下一批数据的起始位置。

五、 优化技巧，让导入飞起来！

除了分批导入，还有一些其他的优化技巧，可以进一步提升 INSERT INTO...SELECT... 的效率：

1. 禁用索引: 在导入数据之前，可以禁用目标表的索引，导入完成后再重新启用。 这样可以避免在导入过程中频繁更新索引，提高导入速度。

   -- 禁用索引
   ALTER TABLE target_table DISABLE KEYS;
   
   -- 导入数据
   INSERT INTO target_table (column1, column2, ...)
   SELECT column1, column2, ...
   FROM source_table;
   
   -- 启用索引
   ALTER TABLE target_table ENABLE KEYS;

2. 禁用唯一性检查: 如果目标表有唯一性约束，可以在导入数据之前禁用唯一性检查，导入完成后再重新启用。 这样可以避免在导入过程中频繁检查唯一性，提高导入速度。 注意： 禁用唯一性检查可能会导致数据重复，所以在启用之前，一定要确保数据没有重复。

   -- 禁用唯一性检查
   SET unique_checks = 0;
   
   -- 导入数据
   INSERT INTO target_table (column1, column2, ...)
   SELECT column1, column2, ...
   FROM source_table;
   
   -- 启用唯一性检查
   SET unique_checks = 1;

3. 调整 innodb_buffer_pool_size: innodb_buffer_pool_size 是 InnoDB 存储引擎用于缓存数据和索引的内存区域。 如果你的服务器内存足够大，可以适当增加 innodb_buffer_pool_size 的值，提高数据导入的效率。 注意： innodb_buffer_pool_size 的值不宜设置过大，否则可能会导致服务器内存不足。

4. 使用 LOAD DATA INFILE: 如果数据源是文件，可以考虑使用 LOAD DATA INFILE 语句，它比 INSERT INTO...SELECT... 的效率更高。 但是，LOAD DATA INFILE 的使用比较复杂，需要仔细阅读 MySQL 的文档。

六、 错误处理，防患于未然

在数据导入过程中，可能会遇到各种各样的错误，例如数据类型不匹配、主键冲突、违反唯一性约束等等。 为了保证数据导入的可靠性，我们需要做好错误处理。

1. 使用事务: 将 INSERT INTO...SELECT... 语句放在一个事务中，如果导入过程中出现错误，可以回滚事务，保证数据的一致性。

   START TRANSACTION;
   
   INSERT INTO target_table (column1, column2, ...)
   SELECT column1, column2, ...
   FROM source_table;
   
   -- 如果一切顺利，提交事务
   COMMIT;
   
   -- 如果出现错误，回滚事务
   -- ROLLBACK;

2. 记录错误日志: 在导入数据之前，可以创建一个错误日志表，用于记录导入过程中出现的错误。 这样可以方便我们排查问题，修复数据。

   -- 创建错误日志表
   CREATE TABLE import_errors (
     id INT AUTO_INCREMENT PRIMARY KEY,
     timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     error_message TEXT,
     data TEXT
   );
   
   -- 导入数据，捕获错误
   DELIMITER //
   CREATE PROCEDURE import_data()
   BEGIN
     DECLARE EXIT HANDLER FOR SQLEXCEPTION
     BEGIN
       GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
       INSERT INTO import_errors (error_message, data) VALUES (CONCAT(@sqlstate, ' ', @errno, ' ', @text), 'your data');
       ROLLBACK;
     END;
   
     START TRANSACTION;
   
     INSERT INTO target_table (column1, column2, ...)
     SELECT column1, column2, ...
     FROM source_table;
   
     COMMIT;
   END //
   DELIMITER ;
   
   CALL import_data();

七、 总结：INSERT INTO...SELECT... 的优点和缺点

优点：

• 高效: 批量导入数据，比一条一条执行 INSERT 语句效率高得多。
• 灵活: 可以从一个或多个表中查询数据，可以进行各种数据转换和筛选。
• 简单易用: 语法简单，容易上手。

缺点：

• 需要一定的 SQL 基础: 需要掌握 SELECT 语句的基本语法。
• 错误处理需要额外考虑: 需要做好错误处理，保证数据的一致性。

八、 灵魂拷问：INSERT INTO...SELECT... 适用于哪些场景？

• 数据迁移: 将数据从一个表迁移到另一个表。
• 数据备份: 将数据从一个表备份到另一个表。
• 数据同步: 将数据从一个表同步到另一个表。
• 数据清洗: 将数据从一个表清洗后导入到另一个表。
• 数据聚合: 将多个表的数据聚合后导入到另一个表。

九、 结束语： 数据搬运工的快乐！

好了，今天的讲座就到这里了。 希望大家能够掌握 INSERT INTO...SELECT... 的使用技巧，成为一名高效的数据搬运工！ 记住，数据处理是一项充满挑战但也充满乐趣的工作。 掌握了这些技巧，你就可以轻松应对各种数据导入的需求，让数据在你的手中自由流动！ 咱们下次再见！