1.6.3.3 Constraints on Invalid Data — MySQL 5.7

1.6.3.3 Ограничения на некорректные данные

MySQL 5.7.5 и более поздние версии по умолчанию используют строгий режим SQL, который обрабатывает некорректные значения таким образом, что сервер отклоняет их и прерывает оператор, в котором они встречаются (см. Раздел 5.1.10, «Режимы SQL сервера»). Ранее MySQL гораздо больше допускал некорректные значения, используемые при вводе данных; теперь это требует отключения строгого режима, чего не рекомендуется делать. Остальная часть этого раздела обсуждает старое поведение, применяемое MySQL при отключенном строгом режиме.

Если вы не используете строгий режим, то всякий раз, когда вы вставляете “некорректное” значение в столбец, такой как NULL в столбец NOT NULL или слишком большое числовое значение в числовой столбец, MySQL устанавливает столбец в “наилучшее возможное значение”, а не генерирует ошибку: следующие правила более подробно описывают, как это работает:

Если вы пытаетесь сохранить значение, выходящее за пределы диапазона, в числовой столбец, MySQL Server вместо этого сохраняет ноль, наименьшее возможное значение или наибольшее возможное значение, которое ближе всего к некорректному значению.
Для строк MySQL сохраняет либо пустую строку, либо столько символов строки, сколько можно сохранить в столбце.
Если вы пытаетесь сохранить строку, которая не начинается с числа, в числовой столбец, MySQL Server сохраняет 0.
Некорректные значения для столбцов ENUM и SET обрабатываются как описано в Разделе 1.6.3.4, «Ограничения ENUM и SET».
MySQL позволяет сохранить некоторые некорректные значения даты в столбцы DATE и DATETIME (например, '2000-02-31' или '2000-02-00'). В этом случае, если приложение не включило строгий режим SQL, приложение должно проверить даты перед их сохранением. Если MySQL может сохранить значение даты и получить ровно такое же значение, MySQL сохраняет его как задано. Если дата полностью некорректна (вне возможностей сервера для ее хранения), вместо этого в столбец сохраняется специальное значение даты «ноль» '0000-00-00'.
Если вы пытаетесь сохранить NULL в столбец, который не принимает NULL значения, возникает ошибка для операторов INSERT с одной строкой. Для операторов INSERT с несколькими строками или для операторов INSERT INTO ... SELECT MySQL Server сохраняет неявное значение по умолчанию для типа данных столбца. В общем случае, это 0 для числовых типов, пустая строка ('') для строковых типов и значение «ноль» для типов даты и времени. Неявные значения по умолчанию обсуждаются в Разделе 11.6, «Значения по умолчанию типов данных».
Если оператор INSERT не указывает значение для столбца, MySQL вставляет его значение по умолчанию, если определение столбца включает явное условие DEFAULT. Если определение не содержит такого условия DEFAULT, MySQL вставляет неявное значение по умолчанию для типа данных столбца.

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

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

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';

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

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

Для ещё более строгой проверки включите STRICT_ALL_TABLES. Это то же, что и STRICT_TRANS_TABLES, за исключением того, что для не транзакционных механизмов хранения ошибки прерывают оператор даже при некорректных данных в строках, следующих за первой строкой. Это означает, что если ошибка возникает на половине операции вставки или обновления нескольких строк для не транзакционной таблицы, возникает частичное обновление. Предыдущие строки вставляются или обновляются, но строки с точки ошибки и далее – нет. Чтобы избежать этого для не транзакционных таблиц, используйте операторы с одной строкой или же используйте STRICT_TRANS_TABLES, если приемлемы предупреждения о преобразовании, а не ошибки. Чтобы избежать проблем с самого начала, не используйте MySQL для проверки содержимого столбцов. Безопаснее (и часто быстрее) позволить приложению убедиться, что оно передает в базу данных только корректные значения.

При использовании любого из вариантов строгого режима вы можете заставить ошибки обрабатываться как предупреждения, используя INSERT IGNORE или UPDATE IGNORE вместо INSERT или UPDATE без IGNORE.