SQL Mode определяет синтаксис комманд MySQL и то, какие проверки данных необходимо проводить. Эта возможность заметно облегчает использование баз данных MySQL в различных средах и совместимость MySQL c другими базами данных.

MySQL сервер может работать в различных режимах SQL. Каждому клиенту можно применить свой режим. Это делает возможным любому приложению приспособить режим работы MySQL сервера для решения своих задач.

Настроить SQL Mode для всей БД можно при запуске MySQL, запустив mysqld c опцией --sql-mode="моды через запятую". Также, эти настройки можно внести в главный конфигурационный файл MySQL my.ini (Windows) или my.cnf (Unix) при помощи строки sql-mode="моды через запятую". Значение по умолчанию SQL Mode - отсутствие любых настроек. Чтобы запустить MySQL без модов нужно указать пустой набор модов, например записав в my.ini sql-mode="".

SQL Mode можно настраивать и после запуска MySQL, отправив запрос серверу: SET [GLOBAL|SESSION] sql_mode='моды через запятую'. Для изменения глобальных sql-модов (GLOBAL), распространяющихся на всех пользователей с момента запроса, необходимо обладать привелегией SUPER. Любой другой пользователь может использовать запрос SET SESSION sql_mode='моды через запятую', для изменения модов, чтобы применить свои настройки в любое время.

Просмотреть настройки SQL mod можно такими запросами:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

STRICT_TRANS_TABLES и STRICT_ALL_TABLES

Режим STRICT_TRANS_TABLES контролирует вхождение всех недопустимых или отсутствующих значений, и в случае нахождения таких данных, MySQL вернет сообщение об ошибке. Например, вносимые данные могут иметь неправильный тип для столбца, в который они записываются, или их значения находятся за пределами диапазона, определенными для столбца. Также, если колонке не присвоено значение по умолчанию (нет DEFAULT в определении столбца), и в записываемой строке не определены данные для такого столбца - будет отображено сообщение об ошибке. Столбцам, для которых значения NULL допустимы, NULL вставляется, даже если значение отсутствует.

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

В отношении нетранзакционных таблиц, например MyISAM, MySQL будет вести себя так же, как для поддерживающих транзакции, если не верное значение найдено в первом ряду при Insert или Update. Запрос прекращается и таблица остается неизменной. Если запрос вставляет или обновляет несколько строк и ошибка в значениях обнаруживается во второй или более строке, результат зависит от того, какой из режимов включен - STRICT_TRANS_TABLES или STRICT_ALL_TABLES:

• Если включен STRICT_ALL_TABLES, MySQL вернет ошибку, игнорируя вставку или обновление последующих строк. Тем не менее, в данном случае, ранее вставленные или обновленные строки такими и останутся. Таким образом можно обновить только часть данных, что может быть не приемлимо. Чтобы этого избежать, лучше всего проводить обновления и вставки построчно, так как такие запросы могут быть прерваны без внесения изменений в таблицы.
• Используя режим STRICT_TRANS_TABLES, MySQL будет преобразовывать недопустимые значения до ближайших приемлимых для конкретного столбца и записывать их в таблицу. Если значение отсутствует, MySQL запрос будет использовать значения по умолчанию для столбца, например, для определенного как INT, это будет 0, даже если не указано DEFAULT 0. В любом случае, MySQL сгенерирует предупреждение, а не ошибку и продолжит обработку запроса.

STRICT_ALL_TABLES и STRICT_TRANS_TABLES запрещают внесение в БД недопустимых значений дат, таких как '2004-04-31'. Но даты с нулевым месяцем или днем, например '2011-04-00', эти режимы не отфильтровуют. Чтобы запретить внесение дат такого вида, следует использовать NO_ZERO_IN_DATE и NO_ZERO_DATE SQL mod в дополнение к STRICT_ режимам.

Если ни один из STRICT_ режимов MySQL не используется, происходит запись скорректированных или отсутствующих значений. В STRICT_TRANS_TABLES или STRICT_ALL_TABLES режиме можно добиться тех же результатов, с помощью INSERT IGNORE ... или UPDATE IGNORE ..., детальные сведения по этим запросам можно посмотреть в оригинальной документации (eng): "SHOW WARNINGS Syntax".

Строгий режим не влияет на ограничения проверки внешнего ключа. Foreign_key_checks может быть для этого использован. (См. раздел 5.1.3 оригинальной документации MySQL (eng): "Server System Variables".)

Режим STRICT_TRANS_TABLES является рекомендуемым для использования, так как не позволяет производить незаметную корректировку данных сервером MySQL в запросах к добавлению или изменению одной строки, и в случае отправки запроса затрагивающего более одного ряда данных не нарушает целостности информации в БД. Также, стоит прочесть статьи: "К чему может привести усечение данных в SQL" и "Строгий режим MySQL и почему он должен быть включен".

TRADITIONAL

Мод TRADITIONAL вынуждает MySQL вести себя как «традиционные» системы баз данных SQL. Вместо предупреждения, при установке некорректного значения в столбце выдается сообщение об ошибке.

Запросы Inseret и Update будут прерваны, как только обнаружится любая ошибка. При использовании таблиц, без поддержки транзакций, возможно нарушение целосности данных, так как "откат" изменений при возникновении ошибки в таких видах таблиц не предусмотрен.

Режим TRADITIONAL - это набор таких режимов, как: STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER и NO_ENGINE_SUBSTITUTION.

С полным списком SQL-режимов для MySQL 5.5 можно ознакомится в официальной документации по MySQL, на странице: "Официальное руководство MySQL 5.5 по SQL mod (eng)".