Mysql: Скопировать структуру таблицы, включая внешние ключи

Ответ 1

Возможно, вы могли бы написать процедуру, которая после create table like готовит инструкции 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;

Ответ 2

Ответ Wrikken вдохновил меня. Позвольте мне расширить его.

Ну, все сложнее. Смотрите: http://dev.mysql.com/doc/refman/5.1/en/create-table.html. В нем говорится, что будут проблемы с таблицами TEMPORARY и другими вещами. Решение, упомянутое Wrikken, является хорошим подходом, однако для получения информации о правилах UPDATE и DELETE вам потребуется, по крайней мере, еще один поиск:

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

Итак, может быть хорошей идеей получить инструмент, который упростит задачу. Я лично использую Adminer (https://sourceforge.net/projects/adminer/). Он поставляется с возможностью экспорта всей БД (со всеми таблицами, триггерами, внешними ключами...). После его экспорта (в синтаксисе SQL) вы можете легко изменить имя базы данных и импортировать ее обратно. Я написал об этом в разделе ошибок в проекте (см. Билет 380).

Возможно, у вас уже есть ваш любимый менеджер БД и вы не хотите другого. Вы можете выполнить следующие шаги:

• Дамп БД с помощью mysqldump → вы получаете file.sql
• Создать новую БД
• Выполнить file.sql в новой БД

Оба Adminer и mysqldump имеют возможность выбирать только определенные таблицы, поэтому вам не нужно экспортировать всю БД. Если вам нужна только структура, а не данные, используйте опцию -d для mysqldump или щелкните ее в Adminer.

Ответ 3

Если немного скриптов в стороне, вы можете использовать SHOW CREATE TABLE в нескольких строках вроде этого (PHP, но концепция работает на любом языке):

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

Не очень элегантный, но он работает до сих пор. Я делаю это в тестовой среде, поэтому я помещаю это в свой метод setUp().

Ответ 4

Если у вас есть phpMyAdmin, вы можете экспортировать только структуру своей таблицы, а затем изменить старые имена таблиц на новые в вашем .sql файле и импортировать обратно.

Это довольно быстрый способ

Ответ 5

Я создал скрипт bash для копирования таблицы в другую базу данных, модифицируя ключи и ограничения, добавляя к ним '_1':

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

Это работает и для новых экземпляров базы данных, но команда sed не понадобится. Это необходимо, только если исходная и целевая базы данных находятся в одном и том же экземпляре.

Ответ 6

Если вы просто хотите очистить таблицу, вы можете скопировать все соответствующие данные в таблицу копирования. Затем обрезайте и скопируйте обратно. Результат состоит в том, что ваш 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;