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

我想在 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);

还有别的办法吗？

编辑: 我担心重新创建的索引，mysql 如何继续执行这些语句？

不能使用如 mysqldump 这样的命令行工具，必须在运行中。

97008 次浏览
小开

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

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.

小开

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

小开

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 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.

小开

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.

小开

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

小开

The fastest way using MyISAM tables while preserving indexes) and maybe other storage engines is:

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:

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.)

小开 
CREATE TABLE copy SELECT * FROM original;

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

小开

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;

小开

Maybe you could take a look at SHOW CREATE TABLE.

Steps to take:

  • Go to phpmyadmin

  • Go to 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.

Resource: http://dev.mysql.com/doc/refman/5.7/en/show-create-table.html

小开

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;

提交答案