MySQL의 INSERT IGNORE INTO & Foreign 키

MySQL의 INSERT IGNORE INTO & Foreign 키

에 있는 이유, MySQL 왜서,INSERT IGNORE INTO외부 키 제약 조건 오류를 경고로 변경하지 않습니까?

테이블에 여러 레코드를 삽입하려고 하는데 MySQL에서 오류가 발생하는 레코드를 제외하고 나머지 레코드를 삽입하기를 기대합니다.제안할 사람이 있습니까?

리고그.SET FOREIGN_KEY_CHECKS = 0;제 대답이 아닙니다.왜냐하면 저는 제약 조건을 무시하는 행들이 전혀 삽입되지 않기를 기대하기 때문입니다.

감사해요.

[새로운 답변]

@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                        |
+-------------------------+------------------------------+

분명히 해야 할 일:

mysql> 하위 항목에 무시 삽입-> 가치-> (NULL, 1)-> , (NULL, 2)-> , (NULL, 3)-> , (NULL, 4)-> , (NULL, 5)-> , (NULL, 6);쿼리 정상, 영향을 받는 4개 행, 경고 2개(0.03초)
기록: 6개 중복: 2개 경고: 2개

이 마지막 쿼리의 의미와 문제가 해결된 것으로 표시되는 이유를 더 잘 이해하려면 아래의 이전 답변으로 계속하십시오.

[이전 답변]

나의 해결책은 문제를 해결하는 것이며, 실제 해결책은 항상 MySQL 자체 내에서 문제를 해결하는 것입니다.

다음 단계를 통해 문제가 해결되었습니다.

다음 표와 데이터를 사용하는 것이 좋습니다.

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 |
+----+

이제 일부 행을 삭제하여 문제를 입증해야 합니다.

mysql>
DELETE FROM parent WHERE id IN (3, 5);

문제: 다음 하위 행을 삽입할 때 문제가 발생합니다.

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)

비록 그것이IGNORE키워드가 사용되지만 생성된 오류가 경고로 변환되지 않기 때문에 MySQL은 요청된 작업을 취소합니다.이제 문제가 명백해졌으므로 오류가 발생하지 않고 문에 마지막으로 삽입한 내용을 어떻게 실행할 수 있는지 살펴보겠습니다.

해결책: 삽입된 레코드나 번호에 의존하지 않는 다른 상수 문으로 삽입을 설명합니다.

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 라이브러리를 사용하면 한 번의 요청으로 모든 문을 실행할 수 있습니다.

문제에 대한 가장 간단한 mysql 솔루션은 외부 키 테이블에 참여하여 제약 조건을 확인하는 것이라고 생각합니다.

INSERT INTO child (parent_id, value2, value3)
SELECT p.id, @new_value2, @new_value3
FROM parent p WHERE p.id = @parent_id

하지만 분실된 외국 키를 기반으로 한 기록을 버리려는 것은 이상해 보입니다.예를 들어, 나는 외래 키 검사에서 INSERT IGNORE 오류가 발생하는 것을 선호합니다.

는 어요믿을 믿습니다.INSERT IGNORE는 스토리지 엔진 계층이 아닌 서버 계층의 오류를 무시하기 위한 것입니다.따라서 중복 키 오류(기본 사용 사례)와 특정 데이터 변환에 도움이 되지만 스토리지 엔진 계층에서 발생하는 외부 키 오류에는 도움이 되지 않습니다.

구체적인 요구 사항에 대해서는:

테이블에 여러 개의 레코드를 삽입하려고 하는데 MySQL에서 오류나 오류를 생성하는 레코드는 제외하고 나머지는 삽입하기를 기대합니다.제안할 사람이 있습니까?

이를 위해 다음을 사용할 것을 권장합니다.mysql -f오류에도 불구하고 계속 실행되도록 강제합니다.예를 들어, 다음과 같은 파일이 있는 경우:

insert into child (parent_id, ...) values (bad_parent_id, ...);
insert into child (parent_id, ...) values (good_parent_id, ...);

그런 다음 해당 파일을 로드하면 올바른 행을 삽입하고 잘못된 행의 오류를 무시할 수 있습니다.

mysql -f < inserts.sql

이 문제는 MySQL 5.7에서 해결된 것 같습니다. https://bugs.mysql.com/bug.php?id=78853 을 참조하십시오.

이제 외부 키 제약 조건 오류가 경고로 바뀝니다.

이 문제는 5.1, 5.5, 5.6 빌드에 존재하지만 WL#6614 이후 오류가 오류가 아닌 경고로 변환된 5.7에서 일부 개선 사항을 확인할 수 있습니다.

데이터베이스에 행을 삽입할 경우 추가 쿼리를 사용하여 이 검사를 명시적으로 실행할 수 있습니다.다음 테이블이 있다고 가정합니다.

사용자 애완동물userId | userName | petId | petName-------+----------+------          ------+----------1 | 해럴드 | 81 | 피도2 | 프레드 | 33 | 스팟8 | 벙어리 장갑

새 사용자(애완동물 #1을 키우는 George)를 삽입하려고 합니다.당신은 다음과 같은 것을 할 수 있습니다.

$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")

제 예에서 지나치게 단순화된 코드와 잘못된 관행을 용서해 주십시오. 이는 가능한 해결책을 설명하기 위한 것입니다.

INSERT IGNORE는 ANSI 표준의 일부가 아니며 그에 대한 이유가 있습니다.우선 중복 레코드를 삽입하지 않도록 해야 합니다.기본/고유 키가 외부 키와 다릅니다.또한 IGNORE는 다른 오류 및 경고(0으로 나눗셈, 데이터 잘라내기)도 무시합니다. 이는 일반적으로 좋은 일이 아닙니다.

이 경우에는 거의 매번 INSERT INGORE 대신 REPLACE를 사용하는 것이 좋습니다.다른 옵션은 중복 키 업데이트입니다.MERGE Ansi SQL에 대한 Mysql의 답변입니다.

또 다른 대안은 임시 테이블을 사용하는 것입니다.

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;

잘 부탁드립니다, 호세.

언급URL : https://stackoverflow.com/questions/6849393/mysqls-insert-ignore-into-foreign-keys