MySQL 在mysql中复制表的最快方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2943400/
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
Fastest way to copy a table in mysql?
提问by Anon
I want to copy a table in MySQL. What is the fastest way? Like this?
我想在 MySQL 中复制一个表。最快的方法是什么？像这样？
CREATE TABLE copy LIKE original;
INSERT INTO copy SELECT * FROM original;
or
或者
CREATE TABLE copy SELECT * FROM original;
ALTER TABLE copy ADD PRIMARY KEY (id);
or is there another way?
还是有另一种方法？
EDIT: I'm worried about the indexes being re-created, how does mysql proceed executing these statements?
编辑：我担心索引被重新创建，mysql 如何继续执行这些语句？
PS. can't use command-line tools like mysqldump, must be on-the-fly.
附注。不能使用像 mysqldump 这样的命令行工具，必须是即时的。
回答by ceteras
This copies the structure of the table immediately, but not the data:
这会立即复制表的结构，但不会复制数据：
CREATE TABLE copy LIKE original;
This creates all the indexes the originaltable had.
这将创建该original表的所有索引。
It works this way in mysql 5.1.39.
它在 mysql 中以这种方式工作5.1.39。
回答by ctbrown
The fastest way using MyISAMtables while preserving indexes) and maybe other storage engines is:
使用MyISAM表同时保留索引）和其他存储引擎的最快方法是：
CREATE TABLE copy LIKE original;
ALTER TABLE copy DISABLE KEYS;
INSERT INTO copy SELECT * FROM original;
ALTER TABLE copy ENABLE KEYS;
You want to disable your keys for your database load and then recreate the keys at the end.
您想为数据库加载禁用密钥，然后在最后重新创建密钥。
Similarly, for InnoDB:
同样，对于InnoDB：
SET unique_checks=0; SET foreign_key_checks=0;
..insert sql code here..
SET unique_checks=1; SET foreign_key_checks=1;
(As pointed out in the comments.)
（正如评论中指出的那样。）
回答by dnagirl
From the manual:
从手册：
"CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement: "
“CREATE TABLE ... SELECT 不会自动为您创建任何索引。这样做是有意使语句尽可能灵活。如果您想在创建的表中创建索引，您应该在 SELECT 语句之前指定这些：”
CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
You can specify indices and data types (to avoid datatype conversion) in with both CREATE TABLE LIKEand CREATE TABLE SELECT. Which one is faster will depend on your setup.
您可以与两个指定索引和数据类型（以避免数据类型转换）CREATE TABLE LIKE和CREATE TABLE SELECT。哪个更快取决于您的设置。
回答by Zigri2612
To copy with indexes and triggers do these 2 queries:
要使用索引和触发器进行复制，请执行以下 2 个查询：
CREATE TABLE newtable LIKE oldtable;
INSERT newtable SELECT * FROM oldtable;
To copy just structure and data use this one:
要仅复制结构和数据，请使用以下命令：
CREATE TABLE tbl_new AS SELECT * FROM tbl_old;
回答by Draco Ater
Does create table mynewtable (select * from myoldtable)work in mysql? If so you can try it too.
是否create table mynewtable (select * from myoldtable)在MySQL的工作？如果是这样你也可以试试。
回答by Anil Chahal
Best way to copy structure and all entries from one table to another table (by creating new table) is this query...
将结构和所有条目从一个表复制到另一个表（通过创建新表）的最佳方法是此查询...
CREATE TABLE new_table LIKE old_table; INSERT new_table SELECT * FROM old_table;
CREATE TABLE new_table LIKE old_table; INSERT new_table SELECT * FROM old_table;
回答by amphetamachine
Try SELECT INTO, and use a variableas a go-between.
尝试SELECT INTO，并使用一个变量作为中间人。
You'll have to create the receiving table, first, to have the same structure as the source table.
首先，您必须创建接收表，使其具有与源表相同的结构。
Best thing is, it's internal so it's fast. You'll lose your indexes, though.
最好的是，它是内部的，所以速度很快。但是，您会丢失索引。
回答by Aravind
if you are using MyISAM you can also copying and renaming the induvidual files . .MYD, .MYI, .FRM files in the backend
如果您使用的是 MyISAM，您还可以复制和重命名单个文件。.MYD、.MYI、.FRM 文件在后端
回答by Julian
Maybe you could take a look at SHOW CREATE TABLE.
也许你可以看看SHOW CREATE TABLE。
Steps to take:
采取的步骤：
Go to phpmyadmin
Go to SQL
转到 phpmyadmin
转到 SQL
Execute this query
执行此查询
SHOW CREATE TABLE `the_old_table`;
- Click options Check "Full texts" and press Go.
- 单击选项检查“全文”，然后按转到。
The result is a full CREATE TABLE statement. Edit the query until you are happy.
结果是一个完整的 CREATE TABLE 语句。编辑查询，直到您满意为止。
Resource: http://dev.mysql.com/doc/refman/5.7/en/show-create-table.html
资源：http: //dev.mysql.com/doc/refman/5.7/en/show-create-table.html
回答by Thor
CREATE TABLE copy SELECT * FROM original;
Is a fast way but maybe not the quickest cause of indexes.
是一种快速的方法，但可能不是索引的最快原因。
