MySQL 转储恢复不会恢复仅包含 1 行的表的数据

Question

我有一个从 MySQL 5.5.62 数据库中获取的转储。对于超过 1 行的表，导入到我的 5.7 数据库中效果很好。但是，任何仅包含 1 行的表都无法正确插入数据。下面是转储文件中的一个此类表的示例:

--
-- Table structure for table `Tab2`
--
DROP TABLE IF EXISTS `Tab2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Tab2` (
  `Col2` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `ColT1` varchar(235) NOT NULL,
  `ColT2` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`Col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `Tab2`
--

LOCK TABLES `Tab2` WRITE;
/*!40000 ALTER TABLE `Tab2` DISABLE KEYS */;
INSERT INTO `Tab2` VALUES ('094e5439-7afd-4fba-b934-476873a55c8b','/',NULL);
/*!40000 ALTER TABLE `Tab2` ENABLE KEYS */;
UNLOCK TABLES;

DROP TABLE IF EXISTS `Tab1`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `Tab1` (
      `Col1` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `Col2` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `Col3` varchar(50) NOT NULL,
      `Col4` tinyint(1) NOT NULL,
      `Col5` datetime NOT NULL,
      PRIMARY KEY (`Col1`),
      KEY `Col2` (`Col2`),
      CONSTRAINT `Constraint1` FOREIGN KEY (`Col2`) REFERENCES `Tab2` (`Col2`) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    /*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `Tab1`
--

LOCK TABLES `Tab1` WRITE;
/*!40000 ALTER TABLE `Tab1` DISABLE KEYS */;
INSERT INTO `Tab1` VALUES ('xxx','xxx','xxx',0,'2019-07-24 15:29:06');
/*!40000 ALTER TABLE `Tab1` ENABLE KEYS */;
UNLOCK TABLES;

我不确定为什么会发生这种情况，GCP 导入工具和 MySQL Workbench 都没有给出任何错误。

Answer 1

您的表中有外键约束

CONSTRAINT `Constraint1` FOREIGN KEY (`Col2`) REFERENCES `Tab2` (`Col2`) ON DELETE NO ACTION ON UPDATE NO ACTION

您是否尝试在 Tab2 表中创建/插入数据之前执行此脚本？为了使此插入工作正常，您需要在 Tab2 中的 Tab2.Col2 = 'xxx' 行中插入与 Tab1.Col2 ('xxx') 具有相同值的行

对于此类问题，您可能会收到与外键相关的其他错误，调用:

显示引擎innodb状态；