Mysql 转储还原失败 : Cannot add foreign key constraint

Question

我正在尝试恢复使用 mysqldump 创建的转储。在恢复相同时，我得到了这个

ERROR 1215 (HY000) at line 63: Cannot add foreign key constraint

DROP TABLE IF EXISTS `channel_tags`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `channel_tags` (
  `channel_tag_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `channel_id` bigint(20) NOT NULL,
  `tag_name` varchar(60) NOT NULL,
  PRIMARY KEY (`channel_tag_id`),
  KEY `channel_id_idx` (`channel_id`),
  KEY `tag_name_idx` (`tag_name`),
  CONSTRAINT `ct_channel_fk` FOREIGN KEY (`channel_id`) REFERENCES `channel_shard` (`channel_id`),
  CONSTRAINT `ct_tag_fk` FOREIGN KEY (`tag_name`) REFERENCES `tags` (`tag_name`)
) ENGINE=InnoDB AUTO_INCREMENT=833 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `tags`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tags` (
  `tag_name` varchar(60) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`tag_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

建表语句的顺序是一样的。

SHOW ENGINE INNODB STATUS\G 给我这个:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2015-12-07 17:20:16 1ac30b000 Error in foreign key constraint of table sde/channel_tags:
 FOREIGN KEY (`tag_name`) REFERENCES `tags` (`tag_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

谁能告诉我这里发生了什么？

Answer 1

我也遇到了这个错误。

我猜你和我做了同样的事情:将整个数据库设置为 UTF8，然后将一些列/表更改为 UTF8MB4。

我不知道如何解决它。但是，有一个解决方法:将转储 SQL 文件中的所有 UTF8MB4 更改回 UTF8，将其恢复到 DB，并通过以下命令手动将特定列更改为 UTF8MB4:

ALTER DATABASE [dbname] CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE [tablename] CHANGE [colname] [colname] VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE [tablename] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;