MySQL的INSERT IGNORE INTO&外键 [英] MySQL's INSERT IGNORE INTO & foreign keys

问题描述

为什么在MySQL中， INSERT IGNORE INTO 不会将外键约束错误更改为警告？

我试图插入一些记录到一个表中，我希望MySQL省略那些导致错误，任何错误，并插入其余的。有没有人有任何建议？

和 SET FOREIGN_KEY_CHECKS = 0; 不是我的答案。因为我期望违反约束的行根本不被插入。

谢谢

解决方案我的解决方案是解决这个问题，实际的解决方案将永远解决MySQL本身的问题。

以下步骤解决我的问题：

a。考虑使用下列表格和数据：

 的MySQL> 
 CREATE TABLE parent（id INT AUTO_INCREMENT NOT NULL 
，PRIMARY KEY（id）
）ENGINE = INNODB; 
 
 mysql> 
 CREATE TABLE child（id INT 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> （NULL），（NULL），（NULL），（NULL），（NULL），（NULL）; 
 INSERT INTO parent 
 VALUES 
 
 mysql> 
 SELECT * FROM parent; 
 + ---- + 
 | id | 
 + ---- + 
 | 1 | 
 | 2 | 
 | 3 | 
 | 4 | 
 | 5 | 
 | 6 | 
 + ---- + 
  

b。需要删除一些行来演示这个问题：

  mysql> 
 DELETE FROM parent WHERE id IN（3，5）; 
  

c。问题：当你尝试插入以下子行时，会出现问题：

INSERT IGNORE INTO child
VALUES
（NULL，1）
，（NULL，2）
，（NULL，3）
，（NULL， 4）
，（NULL，5）
，（NULL，6）;

错误1452（23000）：无法添加或更新子行：外键约束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;
空集（0.00秒）

即使 IGNORE 关键字被使用，但是MySQL取消了请求的操作，因为生成的错误没有变成警告（如它应该的那样）。现在问题是显而易见的，让我们看看如何执行最后一个insert into语句而不会遇到任何错误。

d。解决方案：我将通过一些其他常量语句将插入语句包装到语句中，这些语句既不依赖于插入的记录，也不依赖于它们的编号。

 的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; 
  

我知道这不是最优的，但只要MySQL没有解决问题，这是最好的我知道。特别是因为如果在PHP中使用mysqli库，所有的语句都可以在一个请求中执行。

Why in MySQL, INSERT IGNORE INTO does not change the foreign key constraint errors into warnings?

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?

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.

Thanks

解决方案

My solution is a work around to the problem and the actual solution will always be fixing the problem within the MySQL itself.

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:

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 IGNORE keyword 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.

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.

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的INSERT IGNORE INTO&外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！