mysql:复制表结构，包括外键

I know how to copy a table using create new_table like old_table, but that does not copy over the foreign key constraints as well. I can also do string manipulation on the result from show create table old_table (using regular expressions to replace the table name and the foreign key constraint names), but that seems error prone. Is there a better way to copy the structure of a table, including the foreign keys?

我知道如何使用create new_table(比如old_table)复制一个表，但这并不会复制外键约束。我还可以对show create table old_table的结果进行字符串处理(使用正则表达式替换表名和外键约束名)，但这似乎很容易出错。是否有更好的方法来复制表的结构，包括外键?

5 个解决方案

#1

Possibly you could write a procedure that after the create table like prepares ALTER TABLE ... statements, based on information from:

可能您可以编写一个过程，在创建表之后，比如preparealter table……基于以下信息的陈述:

SELECT * 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME LIKE '<table_name>' 
AND TABLE_SCHEMA = '<db_name>'
AND REFERENCED_TABLE_NAME IS NOT NULL;

#2

If a little side-scripting is OK, you can take advantage of SHOW CREATE TABLE in a few lines like so (PHP but concept works in any language):

如果可以使用一些辅助脚本，您可以利用SHOW CREATE TABLE的优势，比如so (PHP但是concept works in any language):

// Get the create statement
$retrieve = "SHOW CREATE TABLE <yourtable>";

$create = <run the $retrieve statement>

// Isolate the "Create Table" index
$create = $create['Create Table'];

// Replace old table name with new table name everywhere
$create = preg_replace("/".$newname."/", $oldname, $create);

// You may need to rename foreign keys to prevent name re-use error.
// See http://*.com/questions/12623651/
$create = preg_replace("/FK_/", "FK_TEMP_", $create);    

// Create the new table
<run the $create statement>

Not very elegant, but it works so far. I'm doing this in a testing environment so I put this in my setUp() method.

虽然不是很优雅，但到目前为止，它仍然有效。我是在测试环境中做这个的，所以我把它放到了setUp()方法中。

#3

Wrikken's answer inspired me. Let me extend it.

Wrikken启发了我的答案。让我来扩展它。

Well, things are more complicated. See: http://dev.mysql.com/doc/refman/5.1/en/create-table.html. It says, that there are going to be problems also with TEMPORARY tables and other things. The solution mentioned by Wrikken is a good approach, however, you will need at least one more lookup to get information about UPDATE and DELETE rules:

事情更复杂。参见:http://dev.mysql.com/doc/refman/5.1/en/create-table.html。它说，临时桌子和其他东西也会有问题。Wrikken提到的解决方案是一个很好的方法，但是，您至少需要一个查找来获得关于更新和删除规则的信息:

SELECT * 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE TABLE_NAME LIKE '<table_name>' 
AND CONSTRAINT_SCHEMA = '<db_name>';

So, it might be a good idea to get a tool that simplifies the task. I personally use Adminer (https://sourceforge.net/projects/adminer/). It comes with an option to export whole DB (with all tables, triggers, foreign keys, ...). Once you export it (in SQL syntax) you can change DB name easily and import it back. I wrote about that at the bugs section of the project (see ticket 380).

所以，找到一个简化任务的工具可能是个好主意。我个人使用Adminer (https://sourceforge.net/projects/adminer/)。它带有一个导出整个DB(包含所有表、触发器、外键…)的选项。一旦导出(使用SQL语法)，就可以轻松地更改DB名称并将其导入。我在项目的bugs部分写了这一点(见第380号门票)。

Maybe you already have your favorite DB manager and do not want another one. You could follow these steps then:

也许您已经有了您最喜欢的DB管理器，并且不想再要一个。你可以遵循以下步骤:

Dump the DB using mysqldump -> you get file.sql
使用mysqldump ->转储数据库，您将获得file.sql
Create a new DB
创建一个新的数据库
Execute file.sql in the new DB
执行文件。新的数据库中的sql

Both Adminer and mysqldump have an option to select only specific tables so you do not have to export whole DB. Should you need only the structure, not the data, use an option -d for mysqldump or click it in Adminer.

Adminer和mysqldump都具有只选择特定表的选项，因此您不必导出整个DB。如果您只需要结构，而不需要数据，那么对mysqldump使用-d选项，或者在Adminer中单击它。

#4

If you have phpMyAdmin, you can export only structure of your table, then change old table names to new in your .sql file and import it back.

如果您有phpMyAdmin，那么只能导出表的结构，然后在.sql文件中将旧表名更改为新的，并将其导入回来。

It's rather quick way

而快速的方法

#5

-2

If you just want to clean your table, you could copy all relevant data to your copy table. Than truncate and copy back. Result is, that you have your FK preserved.

如果只想清理表，可以将所有相关数据复制到复制表中。比截断和复制回来。结果是，你保留了你的FK。

CREATE TABLE IF NOT EXISTS t_copy LIKE t_origin;

INSERT INTO t_copy 
SELECT  t_origin.*      
FROM t_origin;


SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE t_origin;

INSERT INTO t_origin
SELECT t_copy.* 
FROM t_copy;

DROP TABLE t_copy;

SET FOREIGN_KEY_CHECKS = 1;

#1