mySQL修复实现外键的新数据库完整性

Question

我的问题相当简单。我得到了大约95%的一个相当大的工作数据集。我编写了一个解析器，将数据设置到CSV中，编写批量加载，并将所有内容都设置为保存外键。

现在，丢失的5%中有一部分来自一个表(比如Items(，该表曾经包含另一个表，比如Users仍然拥有的信息。由于表之间的引用完整性被破坏，因此无法添加外键约束，从而引发error 1452，因为后一个表引用了前一个表中缺少的记录。

现在，我不介意失去这5%，我想拥有这95%。有没有一种简单的方法可以在添加外键时丢弃打破的记录？或者我可以通过一些SQL查询来做到这一点吗？还是我必须回去重新分析所有内容才能扔掉坏记录？我觉得第一个或第二个应该是一个选择。但我就是找不到它，因为我不是数据库管理员。。。我是不是错过了一些愚蠢而简单的东西？

Answer 1

您可以使用INSERT IGNORE ...，或者如果使用批量数据加载，则使用LOAD DATA INFILE 'filename' IGNORE ...，或者如果您使用mysqlimport程序，则有一个--ignore选项。

这样做的目的是，如果在尝试导入时出现错误，则跳过任何单独的行，并继续到下一行。

另一种选择是在使用INSERT或LOAD DATA INFILE之前使用SET foreign_key_checks=0;。这将允许加载行，即使它们引用了一个不存在的值。当然，这会导致另一个问题：您将有多行数据是"；孤儿；因为他们的父母不见了。这些孤立行往往不会被发现，因为它们自然会从联接中被省略。

您可以检测孤立行，如下所示：

SELECT Items.*
FROM Items
LEFT OUTER JOIN Users ON Items.user_id = Users.id
WHERE Users.id IS NULL;

这种查询只返回Items中在Users中没有匹配行的行，因此外部联接为Users.*的所有列返回NULL。然后，您可以查看这些行，并决定要对这些行执行什么操作，例如更新它们以将user_id更改为其他内容，或者只是删除这些行。