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;

我遇到错误

#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")

我猜测外键定义会阻止更改列在任一侧键入。解决此问题的简单方法是删除外键定义、更改列并重新定义外键。有更好的解决方案吗？

Answer 1

即使使用 SETforeign_key_checks = 0，您也无法更改约束列的类型。
来自 MySQL 文档： http://dev.mysql .com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

但是，即使foreign_key_checks = 0，InnoDB也不允许创建外键约束，其中列引用不匹配的列类型。

因此，我同意 Devart 的评论。只需删除它并重新创建它即可。

Answer 2

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

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

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`);

Answer 4

我遇到了类似的问题，解决方案是更改条款：

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

这对我有用。