MySQL有效地将所有记录从一个表复制到另一个表

Question

有没有一种更有效、更省力的方法将所有记录从一个表复制到另一个表

：

INSERT INTO product_backup SELECT * FROM product

通常，product表将保存大约 50,000 条记录。两个表的结构相同，有 31 列。我想指出这不是我的数据库设计，我继承了一个遗留系统。

Answer 1

你只缺少一件事。特别是，如果您使用的是 InnoDB，您是否希望在 SELECT 语句中显式添加 ORDER BY 子句以确保按主键（聚集索引）顺序插入行：

INSERT INTO product_backup SELECT * FROM product ORDER BY product_id

如果不需要备份表上的辅助索引，请考虑将其删除。这也将节省服务器上的一些负载。

最后，如果您使用的是 InnoDB，请减少所需的行锁数量，并显式锁定两个表：

LOCK TABLES product_backup WRITE;
LOCK TABLES product READ;
INSERT INTO product_backup SELECT * FROM product ORDER BY product_id;
UNLOCK TABLES;

锁定

的东西可能不会产生太大的区别，因为行锁定非常快（虽然没有表格锁那么快），但既然你问了。

Answer 2

mysqldump -R --add-drop-table db_name table_name > filepath/file_name.sql

这将转储指定的表，并在导入现有表时删除删除现有表。 然后做，

mysql db_name < filepath/file_name.sql

Answer 3

DROP目标表：

DROP TABLE DESTINATION_TABLE;
CREATE TABLE DESTINATION_TABLE AS (SELECT * FROM SOURCE_TABLE);

Answer 4

我认为这对于 50k 表来说是不值得的，但是：如果您有数据库转储，则可以从中重新加载表。当您想在另一个表中加载一个表时，您可以使用 sed 命令更改转储中的表名称：在这里你有一些提示：http://blog.tsheets.com/2008/tips-tricks/mysql-restoring-a-single-table-from-a-huge-mysqldump-file.html

另一种选择（取决于您的设计）是在原始表插入上使用触发器，以便复制的表也获取数据。

更好的选择是创建另一个MySQL实例，并以主从配置或每日转储主/负载从方式运行它。