То, как MySQL работает с несколькими операторами вставки (или обновления), зависит от механизма работы с таблицами и режима SQL сервера.

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

Настольные двигатели

В случае нетранзакционного табличного движка, такого как MyISAM, вы легко можете выполнить частичное обновление, потому что каждая вставка или обновление выполняются последовательно и не могут быть отброшены, когда встречается неверная строка и оператор прерывается.

Однако, если вы используете механизм транзакционных таблиц, такой как InnoDB, любое нарушение ограничений во время оператора вставки или обновления вызовет откат любых изменений, сделанных до этого момента, в дополнение к прерыванию оператора.

Режимы SQL

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

• вставка строки без указания значений для каждого столбца NOT NULL
• вставка '123' в столбец, определенный с числовым типом (а не 123)
• обновление столбца CHAR(3) для хранения значения 'four'

В этих случаях MySQL выдаст ошибку, если действует строгий режим. Однако, если строгий режим не действует, вместо этого он часто «исправляет» вашу ошибку, что может привести к всевозможным потенциально опасным действиям (см. MySQL «Усечено неправильное значение INTEGER» и преобразование строки mysql возвращает 0 только для двух примеров).

Опасность, Уилл Робинсон!

Есть некоторые потенциальные «подводные камни» с нетранзакционными таблицами и строгим режимом. Вы не сказали нам, с каким движком таблиц вы работаете, но этот ответ в том виде, в каком он написан в настоящее время, явно использует нетранзакционный таблице, и важно знать, как это влияет на результат.

Например, рассмотрим следующий набор утверждений:

SET sql_mode = '';  # This will make sure strict mode is not in effect

CREATE TABLE tbl (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val INT
) ENGINE=MyISAM;  # A nontransactional table engine (this used to be the default)

INSERT INTO tbl (val) VALUES (1), ('two'), (3);

INSERT INTO tbl (val) VALUES ('four'), (5), (6);

INSERT INTO tbl (val) VALUES ('7'), (8), (9);

Поскольку строгий режим не действует, неудивительно, что вставляются все девять значений, а недопустимые строки преобразуются в целые числа. Сервер достаточно умен, чтобы распознать '7' как число, но не распознает 'two' или 'four', поэтому они преобразуются в значение по умолчанию для числовых типов в MySQL:

mysql> SELECT val FROM tbl;
+------+
| val  |
+------+
|    1 |
|    0 |
|    3 |
|    0 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

Теперь попробуйте сделать это снова с sql_mode = 'STRICT_ALL_TABLES'. Короче говоря, первая инструкция INSERT приведет к частичной вставке, вторая полностью завершится ошибкой, а третья молча принудит '7' к 7 (что не кажется очень "строгим", если вы спросите меня, но это < href="http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html" rel="nofollow noreferrer">задокументированное поведение и не то чтобы необоснованное).

Но подождите, есть еще! Попробуйте с sql_mode = 'STRICT_TRANS_TABLES'. Теперь вы обнаружите, что первый оператор выдает предупреждение вместо ошибки, но второй оператор по-прежнему терпит неудачу! Это может быть особенно неприятно, если вы используете LOAD DATA с кучей файлов, и некоторые из них дают сбой, а другие нет (см. это закрытый отчет об ошибке).

Что делать

В случае ключевых нарушений имеет значение только то, является ли движок таблицы транзакционным (пример: InnoDB) или нет (пример: MyISAM). Если вы работаете с транзакционной таблицей, код Python в вашем вопросе заставит сервер MySQL выполнять действия в следующем порядке:

1. Разберите оператор INSERT и запустите транзакцию.*
2. Вставьте первый кортеж.
3. Вставьте второй кортеж (ключевое ограничение нарушено).
4. Откат транзакции.
5. Отправить сообщение об ошибке на pymysql.

^{*Было бы разумно проанализировать оператор перед началом транзакции, но я не знаю точной реализации, поэтому я соберу их вместе в один шаг.}

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

Однако если вы работаете с нетранзакционной таблицей, сервер пропустит шаг 4 (и соответствующую часть шага 1), потому что обработчик таблиц не поддерживает отчеты о транзакциях. В этом случае, когда ваш скрипт входит в блок except, первый кортеж был вставлен, второй взорван, и вы не сможете легко определить, сколько строк было успешно вставлено, потому что функция, которая обычно это делает, возвращает -1, если последняя вставка или обновление заявление выдало ошибку.

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

Чтобы избежать [частичного обновления], используйте операторы с одной строкой, которые можно прервать без изменения таблицы.

И, на мой взгляд, это именно то, что вы должны сделать. Вряд ли сложно написать цикл на Python, и вам не придется повторять код, если вы правильное вставление значений в качестве параметров вместо их жесткого кодирования - что вы уже делаете, верно? ПРАВИЛЬНО??? >:(

Альтернативные альтернативы

Если вы предполагаете иногда нарушать ограничение и хотите предпринять какие-либо другие действия, когда строка, которую вы пытаетесь вставить, уже существует, вам может быть интересен `ВСТАВИТЬ... ПРИ ОБНОВЛЕНИИ ДУБЛИКАЦИИ КЛЮЧА'. Это позволяет вам выполнять такие удивительные трюки вычислительной гимнастики, как счет вещей:

mysql> create table counting_is_fun (
    -> stuff int primary key,
    -> ct int unsigned not null default 1
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> insert into counting_is_fun (stuff)
    -> values (1), (2), (5), (3), (3)
    -> on duplicate key update count = count + 1;
Query OK, 6 rows affected (0.04 sec)
Records: 5  Duplicates: 1  Warnings: 0

mysql> select * from counting_is_fun;
+-------+-------+
| stuff | count |
+-------+-------+
|     1 |     1 |
|     2 |     1 |
|     3 |     2 |
|     5 |     1 |
+-------+-------+
4 rows in set (0.00 sec)

(Примечание: сравните количество вставленных кортежей с количеством «затронутых строк» ​​запросом и количеством строк в таблице после этого. Разве не весело считать?)

Или, если вы считаете, что данные, которые вы вставляете прямо сейчас, по крайней мере так же хороши, как данные, которые в настоящее время находятся в таблице, вы можете изучить REPLACE INTO - но это специфичное для MySQL расширение стандарта SQL и, как обычно, есть свои особенности, особенно в отношении AUTO_INCREMENT полей и ON DELETE действий, связанных со ссылками на внешние ключи.

Еще один подход, который люди любят предлагать, — это INSERT IGNORE. Это игнорирует ошибку и просто продолжает катиться. Отлично, правда? Кому вообще нужны ошибки? Причины, по которым мне это решение не нравится:

• INSERT IGNORE приведет к игнорированию любой ошибки, возникающей во время оператора, а не только той ошибки, которая, по вашему думанию, вас не волнует.
• В документации указано: "Игнорируемые ошибки вместо этого могут генерировать предупреждения, хотя повторяющийся ключ ошибок нет." Таким образом, вы даже не обязательно знаете, какие предупреждения ожидать при использовании этого ключевого слова!
• Для меня использование INSERT IGNORE говорит: «Я не знаю, как сделать это правильно, поэтому я просто сделаю это неправильно».

Я действительно иногда использую INSERT IGNORE, но когда документация прямо указывает вам «правильный способ» что-то сделать, не перехитрите себя. Сначала попробуйте так; если у вас все еще есть действительно веская причина сделать это неправильно и рискнуть нарушить целостность ваших данных и испортить все навсегда, по крайней мере, вы приняли обоснованное решение.

Проведя некоторые эксперименты с таблицей MyISAM, я вижу, что если вы попытаетесь вставить в таблицу два или более кортежа значений, и один (или более) из них нарушит ограничения таблицы (например, правила первичного ключа или уникальных индексов), кортежи после некорректного не будут вставлены:

create table test(
  id int unsigned not null primary key, 
  col varchar(100)
) Engine = MyISAM;

insert into test values
  (1, 'The first')
, (2, 'Should work')
, (2, 'Should fail') -- This one won't be inserted, and will be treated as an error
, (3, 'The last')    -- This one won't be inserted either, because of the
                     -- previous tuple "offense".
;
select * from test;
+----+-------------+
| id | col         |
+----+-------------+
|  1 | The first   |
|  2 | Should work |
+----+-------------+

В таблицах InnoDB поведение отличается (спасибо AirThomas за его комментарий) вставка не будет полностью:

drop table test;
create table test(
  id int unsigned not null primary key, 
  col varchar(100)
) Engine = InnoDB;

insert into test values
  (1, 'The first')
, (2, 'Should work')
, (2, 'Should fail') -- This will cause the whole insert to fail
, (3, 'The last')
;
select * from test;
    Empty set

Но есть альтернативы. Вы можете использовать ключевое слово ignore (похоже, это работает как с таблицами MyISAM, так и с таблицами InnoDB):

truncate test; -- Let's work with an empty table
insert IGNORE into test values
  (1, 'The first')
, (2, 'Should work')
, (2, 'Should fail') -- This one won't be inserted, but will not cause the insert 
                     -- to fail (because of the IGNORE keyword)
, (3, 'The last');   -- This one will be inserted, even given the previous 
                     -- tuple "offence"
;
-- In MySQL CLI this will pop out a message like this:
-- Query OK, 3 rows affected
-- Records: 4 Duplicates: 1 Warnings: 0
select * from test;
+----+-------------+
| id | col         |
+----+-------------+
|  1 | The first   |
|  2 | Should work |
|  3 | The last    |
+----+-------------+

Вы также можете использовать on duplicate key... Я оставляю это вам как "домашнее задание". Прочитайте документацию по insert ... on duplicate key update.