主页 / dba / 问题 / 302399
Accepted
Ali Hassan
Asked: 2021-11-11 01:29:34 +0800 CST

将数百万行批量复制到另一个表mySQL

  • 772

Table A它总是在更新（记录被插入或更新）。 Table A包含数百万条记录。我想将其中一些记录复制到一个新表中Table B。 Table A并Table B具有完全相同的架构。

如何将记录从 复制Table A到Table B？我不想考虑不断更新的数据Table A。我只想复制第一次查询时存在的数据Table A。我正在尝试批量复制数据。所以每次我查询一批 500 条记录Table A并将它们复制到Table B. 下次我查询Table A以使用offset. 无法保证新记录集恰好是下一批 500 条记录，因为Table A它总是在更新。任务是能够确保我们以顺序方式获取批次，并保证我们有接下来的 500 条记录。

INSERT INTO `Table B` FROM SELECT * FROM `Table A` WHERE ...

不起作用。因为正如我提到Table A的有很多数据并且运行这个查询超时。需要分批进行。创建临时表也需要批量复制。

我尝试使用 mySQL 视图，但它们也有同样的问题。视图从基础表中获取数据。如果基础表得到更新，则视图会获取更新的数据。

mysql bulkcopy

2 个回答

  1. Best Answer

    简短回答： 不要使用OFFSET. 相反，“记住你离开的地方”。

    长答案： 请参阅我的 2 个博客：

    http://mysql.rjweb.org/doc.php/pagination -- 分页方式OFFSET可以跳过或重复行。而且效率低下。

    http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks - 高效分块 - 最好通过PRIMARY KEY. 它讨论了如何处理多列 PK。

    替代答案： 用于mysqldump仅转储表并避免CREATEs. 然后使用mysql将该转储重新加载到另一个表中。

    • 1

  2. 您是否考虑过表分区？似乎您正在尝试手动执行 MySQL 会自动为您执行的操作。

    在任何情况下，您都需要将数据放入新分区表或将旧数据放入存档表。我同意 Rick James 的观点——找到一个键（主键可以很好地解决这个问题）并使用该键移动小块而不是限制和偏移。

    你没有说你会经常这样做（在这种情况下，请阅读分区）或者这是一次性的事情。如果这是一次性的事情，您甚至可以编写一个脚本：

    INSERT INTO foo SELECT * FROM bar WHERE id between 0 and 5000;
INSERT INTO foo SELECT * FROM bar WHERE id between 5001 and 10000;
INSERT INTO foo SELECT * FROM bar WHERE id between 10001 and 15000;
INSERT INTO foo SELECT * FROM bar WHERE id between 15001 and 20000;
...

    您可能想从bar表中删除，并且您想确保这些行确实已复制到foo表中，因此您可以尝试：

    INSERT INTO foo SELECT * FROM bar WHERE id between 0 and 5000;
DELETE FROM bar WHERE id in (SELECT id FROM foo);
INSERT INTO foo SELECT * FROM bar WHERE id between 5001 and 10000;
DELETE FROM bar WHERE id in (SELECT id FROM foo);
INSERT INTO foo SELECT * FROM bar WHERE id between 10001 and 15000;
DELETE FROM bar WHERE id in (SELECT id FROM foo);
INSERT INTO foo SELECT * FROM bar WHERE id between 15001 and 20000;
DELETE FROM bar WHERE id in (SELECT id FROM foo);
...

    如果 5000 行仍然太大，请尝试 500、100 甚至 10。您可以在编写整个脚本之前尝试不同的数字。请记住，您移动的数据不需要再次移动，因此您可以从 0 重新开始而不会受到伤害。这个脚本可以运行很长时间；如果您真的想产生最小的影响，请SELECT sleep(1)在每个INSERT. 请记住，在此示例中id确实需要在某种索引中。

    祝你好运，弗兰克

    • 1

相关问题