MySQL更改外键的类型

我正在使用MySQL，并且我有一个表，其索引被用作许多其他表中的外键。 我想改变索引的数据类型（从signed到unsigned integer），最好的办法是什么？

我试图改变索引字段上的数据类型，但是失败了，因为它被用作其他表的外键。 我尝试更改其中一个外键的数据类型，但由于它与索引的数据类型不匹配而失败。

我想我可以手动删除所有外键约束，更改数据类型并添加约束，但是这将会是很多工作，因为我有很多使用此索引作为外键的表。 有没有办法在进行更改时暂时关闭外键约束？ 另外，有没有办法将所有引用索引的字段列为外键？

更新：在关闭外键检查后，我尝试修改一个外键，但似乎没有关闭检查：

SET foreign_key_checks = 0;

ALTER TABLE `escolaterrafir`.`t23_aluno` MODIFY COLUMN `a21_saida_id` INTEGER DEFAULT NULL;

这是错误：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
090506 11:57:34 Error in foreign key constraint of table escolaterrafir/t23_aluno:
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 to the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT FK_t23_aluno_8 FOREIGN KEY (a21_saida_id) REFERENCES t21_turma (A21_ID)

索引表的定义：

DROP TABLE IF EXISTS `escolaterrafir`.`t21_turma`;
CREATE TABLE  `escolaterrafir`.`t21_turma` (
  `A21_ID` int(10) unsigned NOT NULL auto_increment,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;

以及具有指向它的外键的表：

DROP TABLE IF EXISTS `escolaterrafir`.`t23_aluno`;
CREATE TABLE  `escolaterrafir`.`t23_aluno` (
  ...
  `a21_saida_id` int(10) unsigned default NULL,
  ...
  KEY `Index_7` (`a23_id_pedagogica`),
  ...
  CONSTRAINT `FK_t23_aluno_8` FOREIGN KEY (`a21_saida_id`) REFERENCES `t21_turma` (`A21_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=387 DEFAULT CHARSET=latin1;

---

要回答我自己的问题，我无法找到一个更简单的方法来做到这一点。 我最终放弃了所有的外键约束，改变了字段类型，然后添加了所有的外键约束。

正如R.Bemrose指出的那样，使用SET foreign_key_checks = 0; 仅在添加或更改数据时有所帮助，但不允许会破坏外键约束的ALTER TABLE命令。

---

这是我对这个主题的小贡献。 感谢Daniel Schneller的灵感，并给我解决方案的一大部分！

set group_concat_max_len = 2048;
set @table_name = "YourTableName";
set @change = "bigint unsigned";
select distinct table_name,
       column_name,
       constraint_name,
       referenced_table_name,
       referenced_column_name,
       CONCAT(
           GROUP_CONCAT('ALTER TABLE ',table_name,' DROP FOREIGN KEY ',constraint_name SEPARATOR ';'),
           ';',
           GROUP_CONCAT('ALTER TABLE `',table_name,'` CHANGE `',column_name,'` `',column_name,'` ',@change SEPARATOR ';'),
           ';',
           CONCAT('ALTER TABLE `',@table_name,'` CHANGE `id` `id` ',@change),
           ';',
           GROUP_CONCAT('ALTER TABLE `',table_name,'` ADD CONSTRAINT `',constraint_name,'` FOREIGN KEY(',column_name,') REFERENCES ',referenced_table_name,'(',referenced_column_name,')' SEPARATOR ';')
       ) as query
from   key_column_usage
where  referenced_table_name is not null
   and referenced_column_name is not null
   and referenced_table_name = @table_name
group by referenced_table_name

通过设置@table_name和@change，您可以生成一个查询。 @table_name应该是具有主键的表的表名（它将查找将该列用作外键的表），并将其类型更改为@change。

我不得不改变一些这样的桌子，所以这就像一个魅力。 我只需更改@table_name，然后执行查询。

---

要了解如何使用外键约束，请在INFORMATION_SCHEMA数据库上发出以下查询：

select distinct table_name, 
       column_name, 
       constraint_name,  
       referenced_table_name, 
       referenced_column_name 
from   key_column_usage 
where  constraint_schema = 'XXX' 
   and referenced_table_name is not null 
   and referenced_column_name is not null;

将XXX替换为模式名称。 这会给你一个表和列的列表，将其他列引用为外键。

不幸的是，模式更改是非事务性的，所以我担心你将不得不临时禁用这个操作的foreign_key_checks。 我建议 - 如果可能的话 - 在此阶段防止来自任何客户的连接，以尽量减少意外违反约束的风险。

至于密钥本身：当您更改表格数据类型时，它们将需要删除并重新创建。

链接地址: http://www.djcxy.com/p/65877.html

上一篇: MySQL change type of foreign key

下一篇: How can I include <sodium.h> correctly in Visual Studio?