在mysql中复制表的最快方法?

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;

或

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.

PS.不能使用像mysqldump这样的命令行工具，必须是动态的。

10 个解决方案

#1

This copies the structure of the table immediately, but not the data:

这将立即复制表的结构，但不复制数据:

CREATE TABLE copy LIKE original;

This creates all the indexes the original table had.

这将创建原始表拥有的所有索引。

It works this way in mysql 5.1.39.

它在mysql 5.1.39中是这样工作的。

#2

The fastest way using MyISAM tables while preserve 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.)

(正如评论中指出的那样。)

#3

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: "

“创建表……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 LIKE and CREATE TABLE SELECT. Which one is faster will depend on your setup.

您可以在CREATE TABLE LIKE和CREATE TABLE SELECT中指定索引和数据类型(以避免数据类型转换)。哪个更快取决于你的设置。

#4

Does create table mynewtable (select * from myoldtable) work in mysql? If so you can try it too.

在mysql中创建表mynewtable(从myoldtable中选择*)吗?如果是的话，你也可以试试。

#5

To copy with indexes and triggers do these 2 queries:

要复制索引和触发器，请执行以下两个查询:

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;

#6

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;

创建表new_table，如old_table;从old_table中插入new_table SELECT *;

#7

Try SELECT INTO, and use a variable as a go-between.

尝试选择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.

最好的是，它是内部的，所以速度很快。但是你会失去你的索引。

#8

if you are using MyISAM you can also copying and renaming the induvidual files . .MYD, .MYI, .FRM files in the backend

如果您正在使用MyISAM，您还可以在后端复制和重命名单个文件

#9

Maybe you could take a look at SHOW CREATE TABLE.

也许你可以看看SHOW CREATE TABLE。

Steps to take:

步骤:

Go to phpmyadmin

去phpmyadmin
Go to SQL

去SQL

Execute this query

执行这个查询

SHOW CREATE TABLE `the_old_table`;

Click options Check "Full texts" and press Go.
点击选项检查“全文”并按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

#10

CREATE TABLE copy SELECT * FROM original;

Is a fast way but maybe not the quickest cause of indexes.

是一种快速的方法，但可能不是索引的最快原因。

#1