MySQL 的 INSERT IGNORE INTO 和外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6849393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL's INSERT IGNORE INTO & foreign keys
提问by Mehran
Why in MySQL, INSERT IGNORE INTOdoes not change the foreign key constrainterrors into warnings?
为什么在 MySQL 中，INSERT IGNORE INTO不会将外键约束错误更改为警告？
I'm trying to insert a number of records into a table and I expect MySQL to leave out the ones that result in error, any error, and insert the rest. Does anyone have any suggestions?
我正在尝试将一些记录插入到一个表中，我希望 MySQL 忽略那些导致错误的记录，任何错误，并插入其余的。有没有人有什么建议？
And the SET FOREIGN_KEY_CHECKS = 0;is not my answer. Because I expect the rows which defy the constraints not to be inserted at all.
这SET FOREIGN_KEY_CHECKS = 0;不是我的答案。因为我希望根本不会插入违反约束的行。
Thanks
谢谢
采纳答案by Mehran
[NEW ANSWER]
[新答案]
Thanks to @NeverEndingQueue for bringing this up. It seems MySQL has finally fixed this issue. I'm not sure which version this problem was first fixed in, but right now I tested with the following version and the problem is not there anymore:
感谢@NeverEndingQueue 提出这个问题。看来 MySQL 终于解决了这个问题。我不确定这个问题最初是在哪个版本中修复的，但现在我使用以下版本进行了测试，问题不再存在：
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.22 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.22 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
To be clear:
要清楚：
mysql> INSERT IGNORE INTO child
-> VALUES
-> (NULL, 1)
-> , (NULL, 2)
-> , (NULL, 3)
-> , (NULL, 4)
-> , (NULL, 5)
-> , (NULL, 6);
Query OK, 4 rows affected, 2 warnings (0.03 sec)
Records: 6 Duplicates: 2 Warnings: 2
To better understand the meaning of this last query and why it shows the problem is fixed, please continue with the old answer below.
为了更好地理解最后一个查询的含义以及为什么它显示问题已修复，请继续下面的旧答案。
[OLD ANSWER]
[旧答案]
My solution is a work around to the problem and the actual solution will always be fixing the problem within the MySQL itself.
我的解决方案是解决该问题，而实际的解决方案始终是在 MySQL 内部解决问题。
The following steps solved my problem:
以下步骤解决了我的问题：
a.Consider having the following tables and data:
一种。考虑拥有以下表格和数据：
mysql>
CREATE TABLE parent (id INT AUTO_INCREMENT NOT NULL
, PRIMARY KEY (id)
) ENGINE=INNODB;
mysql>
CREATE TABLE child (id INT AUTO_INCREMENT
, parent_id INT
, INDEX par_ind (parent_id)
, PRIMARY KEY (id)
, FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=INNODB;
mysql>
INSERT INTO parent
VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
mysql>
SELECT * FROM parent;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
b.Now we need to delete some of the rows to demonstrate the problem:
湾 现在我们需要删除一些行来演示问题：
mysql>
DELETE FROM parent WHERE id IN (3, 5);
c. PROBLEM:The problem arises when you try to insert the following child rows:
C。问题：当您尝试插入以下子行时会出现问题：
mysql>
INSERT IGNORE INTO child
VALUES
(NULL, 1)
, (NULL, 2)
, (NULL, 3)
, (NULL, 4)
, (NULL, 5)
, (NULL, 6);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERE
NCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql>
SELECT * FROM child;
Empty set (0.00 sec)
Even though the IGNOREkeyword is used, but MySQL cancels the the requested operation because the generated error is not turned into warning (as it supposed to). Now that the problem is obvious, let's see how can we execute the last insert into statement without facing any error.
即使IGNORE使用了关键字，但 MySQL 取消了请求的操作，因为生成的错误没有变成警告（正如它应该的那样）。现在问题已经很明显了，让我们看看如何在不出现任何错误的情况下执行最后的 insert into 语句。
d. SOLUTION:I'm going to wrap the insert into statement by some other constant statements which are neither dependent on the records inserted, nor on their number.
d. 解决方案：我将通过其他一些既不依赖于插入的记录也不依赖于它们的数量的常量语句来包装 insert into 语句。
mysql>
SET FOREIGN_KEY_CHECKS = 0;
mysql>
INSERT INTO child
VALUES
(NULL, 1)
, (NULL, 2)
, (NULL, 3)
, (NULL, 4)
, (NULL, 5)
, (NULL, 6);
mysql>
DELETE FROM child WHERE parent_id NOT IN (SELECT id FROM parent);
mysql>
SET FOREIGN_KEY_CHECKS = 1;
I know that this is not optimum but as long as MySQL has not fixed the problem, this is the best I know. Especially since all the statements can be executed in one request if you use mysqli library in PHP.
我知道这不是最佳的，但只要 MySQL 没有解决问题，这是我所知道的最好的。特别是因为如果您在 PHP 中使用 mysqli 库，所有语句都可以在一个请求中执行。
回答by Doug Kress
I think the simplest, mysql solution to the problem is joining into the foreign key table to verify constraints:
我认为解决问题的最简单的 mysql 解决方案是加入外键表以验证约束：
INSERT INTO child (parent_id, value2, value3)
SELECT p.id, @new_value2, @new_value3
FROM parent p WHERE p.id = @parent_id
But it seems strange that you want to throw away records based on missing foreign keys. I, for instance, prefer having INSERT IGNORE error on foreign key checks.
但是您想根据丢失的外键丢弃记录似乎很奇怪。例如，我更喜欢在外键检查中出现 INSERT IGNORE 错误。
回答by Ike Walker
I believe INSERT IGNOREis intended to ignore errors from the server layer, not the storage engine layer. So it will help for duplicate key errors (it's primary use case) and certain data conversions, but not foreign key errors, which come from the storage engine layer.
我相信INSERT IGNORE是为了忽略来自服务器层的错误，而不是存储引擎层。因此，它将有助于重复键错误（它是主要用例）和某些数据转换，但不会帮助来自存储引擎层的外键错误。
As for your specific requirement:
至于您的具体要求：
I'm trying to insert a number of records into a table and I expect MySQL to leave out the ones that produce error, any error, and insert the rest. Does anyone have any suggestions?
我正在尝试将一些记录插入到一个表中，我希望 MySQL 忽略那些产生错误的记录，任何错误，并插入其余的记录。有没有人有什么建议？
For that I recommend using mysql -fto force it to keep running despite any errors. So for example, if you have a file like this:
为此，我建议使用mysql -f强制它在出现任何错误的情况下继续运行。例如，如果您有这样的文件：
insert into child (parent_id, ...) values (bad_parent_id, ...);
insert into child (parent_id, ...) values (good_parent_id, ...);
Then you can load that file like so, which will insert the good rows and ignore the error from the bad rows:
然后您可以像这样加载该文件，这将插入好的行并忽略坏行中的错误：
mysql -f < inserts.sql
回答by ks1322
This issue seems to be fixed in MySQL 5.7, see https://bugs.mysql.com/bug.php?id=78853.
此问题似乎已在 MySQL 5.7 中修复，请参阅https://bugs.mysql.com/bug.php?id=78853。
Now foreign key constraint error is turned into warning instead:
现在外键约束错误变成了警告：
This issue exists in 5.1,5.5,5.6 builds but I see some improvements done in 5.7 where error has been converted to warning instead of error after WL#6614.
这个问题存在于 5.1、5.5、5.6 版本中，但我看到在 5.7 中做了一些改进，在 WL#6614 之后错误已转换为警告而不是错误。
回答by Robert Martin
If you are inserting a row into the database, you can run this check explicitly using an additional query. Say you have these tables:
如果您要向数据库中插入一行，则可以使用附加查询显式运行此检查。假设你有这些表：
User Pet
userId | userName | petId petId | petName
-------+----------+------ ------+----------
1 | Harold | 8 1 | Fido
2 | Fred | 3 3 | Spot
8 | Mittens
and you want to insert a new user (George, who has pet #1). You could do something like
并且您想插入一个新用户（George，他有宠物 #1）。你可以做类似的事情
$newUserName = "George"
$petId = "1"
mysql_query("begin")
$result = mysql_query("SELECT petId FROM Pet WHERE petId = $petId LIMIT 1")
if ($result && 1 == mysql_num_rows($result)) {
mysql_query("INSERT INTO User (userName, petId) VALUES ('$newUserName', $petId)")
}
mysql_query("commit")
Please excuse the overly simplified code and bad practices in my example -- it's just meant as illustrative of a possible work-around.
请原谅我的示例中过于简化的代码和不良做法——这只是为了说明可能的解决方法。
回答by Jose Canciani
Have in mind that INSERT IGNORE is not part of the ANSI standard, and there is a reason for it. You should try to avoid inserting duplicates records in the first place. Primary/Unique is not the same as a Foreign key. And have in mind that IGNORE will also ignore other error and warnings (division by zero, data truncations), which usually is not a good thing.
请记住， INSERT IGNORE 不是 ANSI 标准的一部分，这是有原因的。您应该首先尝试避免插入重复记录。主键/唯一键与外键不同。并记住 IGNORE 也会忽略其他错误和警告（除以零，数据截断），这通常不是一件好事。
In this case, and almost every time, it makes more sense to use REPLACE instead of INSERT IGNORE. Another option is ON DUPLICATE KEY UPDATE. This is Mysql's answer to the MERGE Ansi SQL.
在这种情况下，几乎每次，使用 REPLACE 而不是 INSERT IGNORE 更有意义。另一个选项是 ON DUPLICATE KEY UPDATE。这是 Mysql 对 MERGE Ansi SQL 的回答。
Another alternative is to use temporary tables.
另一种选择是使用临时表。
create temporary table tmpTable (col1, col2, ...);
insert into tmpTable ...; -- same insert you already had
alter table tmpTable add key (foreignColumnName); -- only if # of rows is big
insert into table select null /* AI id */, col1, col2 ...
from tmpTable join parentTable on foreignColumnName = parent.id;
Regards, Jose.
问候，何塞。
