问题描述
我正在尝试用新表替换InnoDB表，并且我希望所有指向旧表的外键引用都指向新表.
I am trying to replace an InnoDB table with a new table, and I want all foreign key references that point to the old table to point to the new table.
所以我尝试了这个:
SET foreign_key_checks = 0;
ALTER TABLE foo RENAME foo_old;
ALTER TABLE foo_new RENAME foo;
不幸的是，即使禁用了foreign_key_checks，所有指向foo的引用都被更改为指向foo_old.现在我正在寻找
Unfortunately, even with foreign_key_checks disabled, all references pointing to foo are changed to point to foo_old. Now I am looking for either
- 在不重建整个表的情况下改回外键引用的方法，或者
- 在不更新外键引用的情况下重命名表的方法.
我尝试删除外键并重新创建它们，但是由于表很大，因此要花费数小时.替换表的全部目的是在有限的停机时间内进行模式更改.
I tried dropping the foreign keys and recreating them, but since the tables are huge, it takes hours. The whole point of replacing the table was to make a schema change with limited downtime.
推荐答案
旧问题，但是可以采用以下解决方法.基本上是移动数据，而不是重命名表.当然，您需要确保新数据符合外键规则.
Old questions, but following is a possible way around.Basically move the data rather than renaming the tables. You need to of course make sure the new data adhere to the foreign key rules.
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适用于整个过程.希望这会有所帮助.
Make sure you run it as one query so the foreign_key_checks applies to the whole thing. Hope this helps.
这篇关于重命名InnoDB表而不更新对它的外键引用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！