将MySQL中的int列改为bigint列并保留外键。

Question

我想将数据库中一些主键列的数据类型从INT更改为BIGINT。以下定义是一个玩具示例，用于说明问题：

CREATE TABLE IF NOT EXISTS `owner` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thing_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `thing_id` (`thing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `thing`;
CREATE TABLE IF NOT EXISTS `thing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `owner`
  ADD CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`thing_id`) REFERENCES `thing` (`id`);

现在当我尝试执行以下命令之一时：

ALTER TABLE `thing` CHANGE `id` `id` BIGINT NOT NULL AUTO_INCREMENT;
ALTER TABLE `owner` CHANGE `thing_id` `thing_id` BIGINT NOT NULL;

i'm running into an error

->

我遇到了一个错误

#1025 - Error on rename of './debug/#[temp-name]' to './debug/[tablename]' (errno: 150)

SHOW INODB STATUS 输出：

LATEST FOREIGN KEY ERROR
------------------------
120126 13:34:03 Error in foreign key constraint of table debug/owner:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
 CONSTRAINT "owner_ibfk_1" FOREIGN KEY ("thing_id") REFERENCES "thing" ("id")

我猜测外键定义会阻止在任何一侧更改列类型。解决这个问题的朴素方法是删除外键定义，更改列，然后重新定义外键。是否有更好的解决方案？

- deif

---

你提到的方法并不是幼稚的，你确实可以使用它。 - Devart

谢谢Devart！我一直在寻找一个不那么痛苦的解决方案，因为我必须对许多表格进行操作，但显然丢弃并重新创建约束似乎是正确的做法 :-/. - deif

这个问题是在2012年提出的，从MySQL InnoDB的角度来看，在2019年有什么更新或发现。这样可以简化我们进行更新的方式。特别是当涉及到1000多个表时。 - Mendy

Answer 1

即使使用SET foreign_key_checks = 0，你仍然无法改变约束列的类型。根据MySQL文档：http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html。 然而，即使设置 foreign_key_checks = 0，在InnoDB中也不允许创建一个外键约束，其中一个列引用了不匹配的列类型。 因此，我同意Devart的评论。只需将其删除并重新创建即可。

- lqez

---

尝试禁用外键检查，但仍然无效。感谢指出原因。 - deif

Answer 2

我建议您在GUI工具 - dbForge Studio for MySQL （免费试用版）中重命名这些字段：

只需在“数据库资源管理器”中选择要重命名的字段，单击“重构”->“重命名”命令，在弹出窗口中输入新名称，然后按“确定”，它将自动重命名该字段并重新创建所有外键。

- Devart

Answer 3

尽管您更改了id和thing_id的列大小，但行中的原始数据仍然是4字节整数，而不是8字节整数。 但是您可以转换数据。

请尝试使用此解决方案。 最近我在一个大型数据集上做类似的事情，当数据集威胁要变得太大时，将INT列转换为BIGINT。

ALTER TABLE `owner`
DROP FOREIGN KEY `owner_ibfk_1`;

ALTER TABLE `thing` CHANGE `id` `id` BIGINT NOT NULL AUTO_INCREMENT;
ALTER TABLE `owner` CHANGE `thing_id` `thing_id` BIGINT NOT NULL;

UPDATE `thing` SET `id` = CAST(`id` AS UNSIGNED INTEGER);
UPDATE `owner` SET `thing_id` = CAST(`thing_id` AS UNSIGNED INTEGER);

-- Now the data are BIGINTs; re-create the foreign key constraint

ALTER TABLE `owner`
  ADD CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`thing_id`) REFERENCES `thing` (`id`);

- Ben Birney

Answer 4

我曾经遇到过类似的问题，解决方法是更改语句：

ALTER TABLE table_name CHANGE id id BIGINT(20) NOT NULL AUTO_INCREMENT;

那对我有用。

- Oliver