mysql如何分批迁移表数据到新表

文
章
目
录

• 单次迁移：使用INSERT INTO…SELECT…
• 分批迁移：使用INSERT INTO…SELECT…LIMIT
• 总结

本文重点将mysql如何分批迁移表数据到新表的操作，如果一个表的数据太多，量级到百万或千万，我们该如何分批将大量数据迁移到新表中呢？如果之前我们讲过《Java实现mysql表数据定时迁移和归档》有兴趣的也可以看一看，接下来我们讲下如何分批迁移。

单次迁移：使用INSERT INTO…SELECT…

MySQL提供了INSERT INTO...SELECT...语句，它可以将一个表中的数据插入到另一个表中。这对于将一个表中的所有记录复制到另一个表中是非常有用的。

基础语法如下：

INSERT INTO 目标表名 (列1, 列2, 列3, ...)
SELECT 列1, 列2, 列3, ...
FROM 源表名;

如果目标表和源表一样，那就可以直接使用select *迁移所有列，比如：

INSERT INTO t_student_backup SELECT * FROM t_student;

以上语句是一次性将所有数据迁移到新表，数据量过大容易导致长事务，导致其他操作超时，可以使用分批迁移来实现，那么该如何分批迁移呢？我们看下面的案例。

分批迁移：使用INSERT INTO…SELECT…LIMIT

其实就是在select时结合limit来分页迁移，这样就实现了分批效果，假设A表有N条记录，每次迁移10000条。可以使用以下循环来逐批复制数据，当然这里演示的是存储过程的写法：

SET @rownum = 0;
SET @batch_size = 10000;
   
REPEAT
    INSERT INTO B SELECT * FROM A LIMIT @rownum, @batch_size;
    SET @rownum = @rownum + @batch_size;
    UNTIL ROW_COUNT() = 0
END REPEAT;

这里的@rownum用于跟踪当前迁移位置，@batch_size表示每次迁移的记录数。核心思想就在于循环增加rownum索引，直到所有记录都迁移了。

在java中，你就可以通过类似limit分页的写法循环去分批调用INSERT INTO...SELECT...LIMIT，来实现分批迁移数据，我测试了下迁移100W的数大概耗时1分钟左右，当然这也和你的服务器配置和表数据大小有关，性能还可以。

分批迁移的方法比第一种方法更高效，因为它可以避免一次性复制所有记录所带来的内存和性能问题。

总结

以上就是如何分批迁移mysql表数据到新表的全部内容，希望对你有帮助，学习愉快哦！