MySQL MySQL有效地将所有记录从一张表复制到另一张表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11188427/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL efficiently copy all records from one table to another
提问by crmpicco
Is there a more-efficent, less laborious way of copying all records from one table to another that doing this:
是否有一种更有效、更省力的方法可以将所有记录从一个表复制到另一个表：
INSERT INTO product_backup SELECT * FROM product
Typically, the producttable will hold around 50,000 records. Both tables are identical in structure and have 31 columns in them. I'd like to point out this is notmy database design, I have inherited a legacy system.
通常，该product表将保存大约 50,000 条记录。两个表的结构相同，并且有 31 列。我想指出这不是我的数据库设计，我继承了一个遗留系统。
回答by Marcus Adams
There's just one thing you're missing. Especially, if you're using InnoDB, is you want to explicitly add an ORDER BY clause in your SELECT statement to ensure you're inserting rows in primary key (clustered index) order:
你只缺少一件事。特别是，如果您使用的是 InnoDB，是否要在 SELECT 语句中显式添加 ORDER BY 子句以确保以主键（聚集索引）顺序插入行：
INSERT INTO product_backup SELECT * FROM product ORDER BY product_id
Consider removing secondary indexes on the backup table if they're not needed. This will also save some load on the server.
如果不需要，请考虑删除备份表上的二级索引。这也将节省服务器上的一些负载。
Finally, if you are using InnoDB, reduce the number of row locks that are required and just explicitly lock both tables:
最后，如果您使用的是 InnoDB，请减少所需的行锁数量，并明确锁定两个表：
LOCK TABLES product_backup WRITE;
LOCK TABLES product READ;
INSERT INTO product_backup SELECT * FROM product ORDER BY product_id;
UNLOCK TABLES;
The locking stuff probably won't make a huge difference, as row locking is very fast (though not as fast as table locks), but since you asked.
锁定的东西可能不会有很大的不同，因为行锁定非常快（虽然不如表锁定快），但是既然你问了。
回答by satdev86
mysqldump -R --add-drop-table db_name table_name > filepath/file_name.sql
This will take a dump of specified tables with a drop option to delete the exisiting table when you import it. then do,
这将转储指定的表，并在导入时使用删除选项删除现有表。然后做，
mysql db_name < filepath/file_name.sql
回答by Ice Cream
DROPthe destination table:
DROP目标表：
DROP TABLE DESTINATION_TABLE;
CREATE TABLE DESTINATION_TABLE AS (SELECT * FROM SOURCE_TABLE);
回答by mihaisimi
I don't think this will be worthy for a 50k table but: If you have the database dump you can reload a table from it. As you want to load a table in another one you could change the table name in the dump with a sed command: Here you have some hints: http://blog.tsheets.com/2008/tips-tricks/mysql-restoring-a-single-table-from-a-huge-mysqldump-file.html
我认为这对于 50k 表不值得，但是：如果您有数据库转储，则可以从中重新加载表。当您想在另一个表中加载一个表时，您可以使用 sed 命令更改转储中的表名：这里有一些提示：http: //blog.tsheets.com/2008/tips-tricks/mysql-restoreing- a-single-table-from-a-huge-mysqldump-file.html
An alternative (depending on your design) would be to use triggers on the original table inserts so that the duplicated table gets the data as well.
另一种选择（取决于您的设计）是在原始表插入上使用触发器，以便复制的表也获取数据。
And a better alternative would be to create another MySQL instance and either run it in a master-slave configuration or in a daily dump master/load slave fashion.
更好的选择是创建另一个 MySQL 实例，并以主从配置或每日转储主/加载从方式运行它。
