在MYSQL中复制表而不一次复制一行

I want to duplicate a very large table, but I do not want to copy it row by row. Is there a way to duplicate it?

我想复制一个非常大的表，但我不想逐行复制它。有没有办法复制它？

For example, you can TRUNCATE w/o deleting row/row, so i was wondering if there is something similar for copying entire tables

例如，你可以TRUNCATE没有删除行/行，所以我想知道复制整个表是否有类似的东西

UPDATE: row by row insert is very painful (because of 120M rows). Anyway to avoid that?

更新：逐行插入非常痛苦（因为120M行）。无论如何要避免这种情况？

6 个解决方案

#1

MySQL no longer has a reliable "copy table" functionality - many reasons for this related to how data is stored. However, the below does row-by-row insertion but is pretty simple:

MySQL不再具有可靠的“复制表”功能 - 许多原因与数据的存储方式有关。但是，下面会逐行插入，但非常简单：

CREATE TABLE `new_table` LIKE `old_table`;
INSERT INTO `new_table` (SELECT * FROM `old_table`);

#2

You could use INSERT INTO ... SELECT.

你可以使用INSERT INTO ... SELECT。

#3

If you're using MyISAM you can copy the physical files on disk. Restart the service and you'll have the new table with indexes and everything the same.

如果您使用的是MyISAM，则可以复制磁盘上的物理文件。重新启动服务，您将获得带有索引的新表，并且所有内容都相同。

#4

INSERT INTO TABLE2 SELECT * FROM TABLE1

#5

It's nontrivial to copy a large table; ultimately the database is likely to need to rebuild it.

复制一张大桌子并不重要;最终数据库可能需要重建它。

In InnoDB the only way is really to rebuild it, which means insert ... select or such like, however, with 120M rows as it's going to happen in a single transaction, you will probably exceed the size of the rollback area, which will cause the insert to fail.

在InnoDB中，唯一的方法是重建它，这意味着插入...选择或类似，但是，120M行，因为它将在单个事务中发生，你可能会超过回滚区域的大小，这将导致插入失败。

mysqldump followed by renaming the original table then restoring the dump should work, as mysqldump may cause a commit every lots of rows. However it will be slow.

mysqldump后面重命名原始表然后恢复转储应该工作，因为mysqldump可能会导致提交每一行。然而它会很慢。

#6

oracle:

甲骨文：

Create table t as select * from original_table

从original_table创建表t作为select *

#1