Переименование таблицы InnoDB без обновления ссылок на внешние ключи?

Я пытаюсь заменить таблицу InnoDB на новую таблицу, и я хочу, чтобы все ссылки внешнего ключа указывали на старую таблицу, чтобы указать на новую таблицу.

Итак, я пробовал это:

SET foreign_key_checks = 0;
ALTER TABLE foo RENAME foo_old;
ALTER TABLE foo_new RENAME foo;

К сожалению, даже с отключением foreign_key_checks все ссылки, указывающие на foo, изменяются так, чтобы они указывали на foo_old. Теперь я ищу либо

способ изменения ссылок на внешние ключи без восстановления всей таблицы, OR
способ переименования таблицы без обновления ссылок на внешние ключи.

Я попытался сбросить внешние ключи и воссоздать их, но поскольку таблицы огромны, требуется несколько часов. Весь смысл замены таблицы состоял в том, чтобы изменить схему с ограниченным временем простоя.

Ответ 1

Старые вопросы, но следующий - это возможный путь. В основном перемещение данных, а не переименование таблиц. Вы должны, конечно же, убедиться, что новые данные соответствуют правилам внешнего ключа.

SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS foo_old LIKE foo;
INSERT INTO foo_old SELECT * FROM foo;
TRUNCATE foo;
INSERT INTO foo SELECT * FROM foo_new;

Убедитесь, что вы запускаете его как один запрос, чтобы foreign_key_checks применялся ко всему этому. Надеюсь, это поможет.

Ответ 2

В MySQL 5.6 с innodb_file_per_table=ON вы можете менять табличные пространства "на лету". Это невозможно сделать полностью с использованием SQL, поскольку операции с файлами нужно выполнять отдельно. Сначала подготовьте таблицу foo_new, которую нужно скопировать, и оставьте данные foo:

SET foreign_key_checks = 0;
ALTER TABLE foo DISCARD TABLESPACE;
FLUSH TABLES foo_new FOR EXPORT;

В этот момент вам нужно скопировать соответствующие файлы InnoDB, чтобы исправить имя. Файлы хранятся в вашем каталоге данных. Например, в Debian они по умолчанию находятся в /var/lib/mysql/yourdatabase, а файлы - foo_new.ibd, foo_new.cfg и foo_new.frm. Скопируйте их в foo.ibd, foo.cfg и foo.frm соответственно. Например:

$ cp foo_new.ibd foo.ibd
$ cp foo_new.frm foo.frm
$ cp foo_new.cfg foo.cfg

Обратите внимание, что MySQL имеет доступ к новым файлам (например, у них есть правильный владелец, права доступа). После этого вы можете снова импортировать таблицу и включить внешние ключи:

UNLOCK TABLES;
ALTER TABLE foo IMPORT TABLESPACE;
SET foreign_key_checks = 1;

Это копирует только foo_new в foo. Повторите шаги, если вам нужно скопировать foo в foo_old.

Ответ 3

К сожалению, я не думаю, что есть способ обойти вашу проблему, не отбрасывая внешние ключи, и они их повторно создают.

Это незначительно, но я кое-что заметил с вашими командами RENAME. Вы можете связать их вместе, и если все шаги не будут успешными, он откатит все остальные переименования. Вот синтаксис:

RENAME TABLE foo TO foo_old, foo_new TO foo;

Ответ 4

InnoDB использует внутренний указатель таблицы во внешнем ключе, поэтому независимо от имени, указанного в этой таблице (используя RENAME), ограничения сохраняются, в том числе при использовании SET foreign_key_checks = 0.

способ изменения ссылок на внешние ключи без восстановления всей таблицы

Использование innodb_file_per_table=ON было бы самым близким к нам (см. ответ @vhu).

способ переименования таблицы без обновления ссылок на внешние ключи.

Решение, которое будет подразумевать наименьшее время простоя и усилий и не требует доступа к серверу на сервере, может просто состоять в том, чтобы работать с двумя базами данных и своевременно включать их, если данные не очень сильно изменяется

Еще быстрее синхронизировать каждую другую таблицу, чем большую, или временную дублирующую команду mysql (удалить, обновить, вставить) в приложение до переключения, если у вас есть некоторые изменения.

Ответ 5

Я нашел способ обойти это... вы просто отбрасываете исходную таблицу, а не переименовываете ее.

В этом примере мы будем называть таблицу mytbl.

создать копию исходной таблицы, например. 'Mytbl_new'
копировать данные в новую таблицу
удалить исходную таблицу 'mytbl'
переименовать 'mytbl_new' в 'mytbl'

Единственным недостатком является то, что вы не можете сохранить резервную копию своей исходной таблицы, но вы можете mysqldump ее перед рукой. Кроме того, вы можете создать дополнительную копию таблицы, если вы хотите получить стенографическую копию исходной таблицы.