MySQL INSERT IGNORE INTO и внешние ключи

Ответ 1

Мое решение - это проблема с проблемой, и фактическое решение всегда будет устранять проблему внутри самого MySQL.

Следующие шаги решили мою проблему:

a. Рассмотрим следующие таблицы и данные:

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. Теперь нам нужно удалить некоторые из строк, чтобы продемонстрировать проблему:

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 не исправил проблему, это лучшее, что я знаю. Тем более, что все заявления могут быть выполнены в одном запросе, если вы используете библиотеку mysqli в PHP.

Ответ 2

Я думаю, что самое простое решение 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 при проверке внешнего ключа.

Ответ 3

Я полагаю, что 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

Ответ 4

INSERT IGNORE - всего лишь обходной путь для ленивых. Вы не должны вставлять записи дубликатов в первую очередь. Кроме того, Primary/Unique отличается от внешнего ключа. И имейте в виду, что IGNORE также игнорирует другие ошибки и предупреждения (деление на ноль, усечение данных), что обычно не очень хорошо.

В этом случае и почти каждый раз имеет смысл использовать REPLACE вместо INSERT IGNORE. Другой вариант - ОБНОВЛЕНИЕ ОБНОВЛЕНИЯ КЛЮЧА DUPLICATE.

В вашем случае, если вы не можете удалить отсутствующих родителей перед вставкой, вы можете управлять одним и тем же с помощью временной таблицы следующим образом:

create temporary table tmpTable (col1, col2, ...);
insert into tmpTable values (row1), (row2), ...; -- 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;

С уважением, Хосе.

Ответ 5

Эта проблема, как представляется, исправлена ​​в MySQL 5.7, см. https://bugs.mysql.com/bug.php?id=78853.

Теперь ошибка ограничения внешнего ключа вместо этого превращается в предупреждение:

Эта проблема существует в строках 5.1.5.5.5.6, но я вижу некоторые улучшения, сделанные в 5.7, где ошибка была преобразована в предупреждение вместо ошибки после WL # 6614.

Ответ 6

Если вы вставляете строку в базу данных, вы можете выполнить эту проверку явно, используя дополнительный запрос. Скажем, у вас есть эти таблицы:

User                               Pet
userId | userName | petId          petId | petName
-------+----------+------          ------+----------
 1     | Harold   | 8               1    | Fido
 2     | Fred     | 3               3    | Spot
                                    8    | Mittens

и вы хотите вставить нового пользователя (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")

Пожалуйста, извините чрезмерно упрощенный код и неправильные методы в моем примере - это просто означает иллюстрацию возможной работы.