English 中文(简体)
我sql: 表格结构,包括外国钥匙
原标题:mysql: Copy table structure including foreign keys
  • 时间:2010-09-20 21:20:07
  •  标签:
  • mysql

我知道如何用<条码>复制新的表格,如旧的“表<>/代码”,但该表也没有复制外国关键制约因素。 我也可以根据<代码>的计算结果进行操纵,从而创建表格旧:table(使用定期表述取代表格名称和外国关键限制名称),但似乎容易出现错误。 是否有更好的办法复制表格的结构,包括外国钥匙?

问题回答

您可以根据以下信息撰写一份程序:<代码>create table as prepareds ALTER 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;

如果没有附加说明,你就可以利用<代码>。 缩略语

// 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://stackoverflow.com/questions/12623651/
$create = preg_replace("/FK_/", "FK_TEMP_", $create);    

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

并非非常任人,但迄今工作。 我在测试环境中这样做,因此,我在<条码>Up()方法中这样做。

Wrikken的回答激励我。 请允许我谈谈这个问题。

事情也变得更加复杂。 见:rel=“nofollow”http://dev.mysql.com/doc/refman/5.1/en/create-table.html。 它说,在<条码>临时表格和其他材料方面也会出现问题。 Wrikken提到的解决办法是一种好的做法,但你至少需要再一次研究,以了解有关<编码>>UPD和DELETE<<>/code>的规则:

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

因此,获得简化任务的工具可能是一个好的想法。 I personal use Adminer ( https://sourceforge.net/projects/adminer/)。 它有出口整个非行的选择(所有表格、触发器、外国钥匙......)。 一旦出口,你可以很容易地改变行文名称,然后进口。 我在项目标的栏目中写道(见机票380)。

或许你们已经拥有优惠的行凶者,不想要别人。 之后,你可以采取这些步骤:

  1. Dump the DB using mysqldump -> you get file.sql
  2. Create a new DB
  3. Execute file.sql in the new DB

* E/CN.6/2009/1。 选择只选择具体的表格,这样你就不必出口整个非行。 如果你只需要结构,而不是数据,则使用<代码>-d>的备选案文。 页: 1

如果你有phpMyAdmin,你只能出口桌子的结构,然后将旧的桌子改换成新名称,然后进口。

它相当迅速

如果你只是想清理桌子,你就可以把所有相关数据复制到您的复印桌上。 Than。 结果是,你保护了你的科索沃。

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来修改关键和制约因素:

mysqldump -h ${host_ip} -u root -p${some_password} ${some_database} ${some_table}  > some_filename
sed -i -r -e  s@KEY `([a-zA-Z0-9_]*)`@KEY `1_1`@g  -e  s@CONSTRAINT `([a-zA-Z0-9_]*)`@CONSTRAINT `1_1`@g  some_filename
mysql -h ${host_ip} -u root -p${some_password} ${some_new_database} < some_filename

这还有助于建立新的数据库,但必须赢得 command。 只有在来源和目的地数据库相同的情况下才有必要。





相关问题
SQL SubQuery getting particular column

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...

please can anyone check this while loop and if condition

<?php $con=mysql_connect("localhost","mts","mts"); if(!con) { die( unable to connect . mysql_error()); } mysql_select_db("mts",$con); /* date_default_timezone_set ("Asia/Calcutta"); $date = ...

php return a specific row from query

Is it possible in php to return a specific row of data from a mysql query? None of the fetch statements that I ve found return a 2 dimensional array to access specific rows. I want to be able to ...

Character Encodings in PHP and MySQL

Our website was developed with a meta tag set to... <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> This works fine for M-dashes and special quotes, etc. However, I ...

Pagination Strategies for Complex (slow) Datasets

What are some of the strategies being used for pagination of data sets that involve complex queries? count(*) takes ~1.5 sec so we don t want to hit the DB for every page view. Currently there are ~...

Averaging a total in mySQL

My table looks like person_id | car_id | miles ------------------------------ 1 | 1 | 100 1 | 2 | 200 2 | 3 | 1000 2 | 4 | 500 I need to ...

热门标签