SQL_MODE

MariaDB поддерживает несколько различных режимов, позволяющих настроить его под ваши нужды.

Наиболее важными способами для этого являются использование SQL_MODE (управляемого системной переменной sql_mode) и OLD_MODE (системная переменная old_mode). SQL_MODE используется для того, чтобы MariaDB эмулировал поведение других SQL-серверов, в то время как OLD_MODE используется для эмуляции поведения более старых версий MariaDB или MySQL.

SQL_MODE — это строка с различными опциями, разделенными запятыми (',') без пробелов. Опции нечувствительны к регистру.

Вы можете проверить локальное и глобальное значение с помощью:

SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

Настройка SQL_MODE

Значения по умолчанию

Вы можете установить SQL_MODE как из командной строки (командная строка) (опция --sql-mode), так и задав системную переменную sql_mode.

SET sql_mode = 'modes';
SET GLOBAL sql_mode = 'modes';

Значение сеанса влияет только на текущего клиента и может быть изменено клиентом по мере необходимости. Для установки глобального значения требуется привилегия SUPER, и изменение повлияет на всех клиентов, подключившихся с этого момента.

Значения SQL_MODE

Различные SQL_MODE значения:

ALLOW_INVALID_DATES

Разрешает любой день от 1 до 31 в части дня. Это удобно, когда вы хотите считать все (включая неправильные данные) в базу данных и затем обработать их там.

ANSI

Изменяет синтаксис SQL, приближая его к ANSI SQL.

Устанавливает: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.

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

Если задано, в выводе SHOW CREATE TABLE не будут отображаться специфичные для MariaDB атрибуты таблицы.

ANSI_QUOTES

Изменяет " на то, чтобы он обрабатывался как `, символ кавычек для идентификаторов. Это может сломать старые приложения MariaDB, которые предполагают, что " используется как символ кавычек для строк.

DB2

То же, что: PIPES_AS_CONCAT, ANSI_QUOTES , IGNORE_SPACE, DB2, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS

Если задано, в выводе SHOW CREATE TABLE не будут отображаться специфичные для MariaDB атрибуты таблицы.

EMPTY_STRING_IS_NULL

Опция совместимости Oracle, которая преобразует Item_string, созданный в анализаторе, в Item_null и преобразует привязку пустой строки как параметры подготовленного оператора в привязку NULL. Например, SELECT '' IS NULL возвращает TRUE, INSERT INTO t1 VALUES ('') вставляет NULL. С версии MariaDB 10.3.3

ERROR_FOR_DIVISION_BY_ZERO

Если не задано, деление на ноль возвращает NULL. Если задано, возвращает ошибку, если кто-то пытается обновить столбец с 1/0, и также возвращает предупреждение. Также см. MDEV-8319. По умолчанию с версии MariaDB 10.2.4.

HIGH_NOT_PRECEDENCE

Опция совместимости для MySQL 5.0.1 и ранее; Это изменяет NOT a BETWEEN b AND c на обработку как (NOT a) BETWEEN b AND c

IGNORE_BAD_TABLE_OPTIONS

Если это задано, генерируется предупреждение (а не ошибка) для неправильной опции таблицы в CREATE TABLE. Кроме того, начиная с 10.0.13, эти неправильные опции таблиц не комментируются в SHOW CREATE TABLE.

IGNORE_SPACE

Разрешает наличие пробелов (включая символы табуляции и новой строки) между именем функции и '('. Недостаток заключается в том, что это приводит к тому, что встроенные функции становятся зарезервированными словами.

MAXDB

То же, что: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, MAXDB, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

Также приводит к тому, что поля типа TIMESTAMP молча преобразуются в поля типа DATETIME при создании или изменении.

Если задано, в выводе SHOW CREATE TABLE не будут отображаться специфичные для MariaDB атрибуты таблицы.

MSSQL

Кроме того, подразумевает следующее: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

Кроме того, начиная с MariaDB 10.4.5, реализует ограниченный подмножество языка Microsoft SQL Server. Более подробная информация см. в SQL_MODE=MSSQL.

Если задано, в выводе SHOW CREATE TABLE не будут отображаться специфичные для MariaDB атрибуты таблицы.

MYSQL323

То же, что: NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.

MYSQL40

То же, что: NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE.

NO_AUTO_CREATE_USER

Не создавайте автоматически пользователей с GRANT , если не указана информация об аутентификации. Если она не указана, появится ошибка 1133: «Не найдено соответствующей строки в таблице пользователей». По умолчанию с версии MariaDB 10.1.7.

NO_AUTO_VALUE_ON_ZERO

Если задано, не генерировать AUTO_INCREMENT при INSERT нуля в столбец типа AUTO_INCREMENT, или при добавлении атрибута AUTO_INCREMENT с помощью инструкции ALTER TABLE. Обычно и zero и NULL генерируют новые значения AUTO_INCREMENT.

NO_BACKSLASH_ESCAPES

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

NO_DIR_IN_CREATE

Игнорирует все директивы INDEX DIRECTORY и DATA DIRECTORY при создании таблицы. Может быть полезно на серверах репликации slave replication.

NO_ENGINE_SUBSTITUTION

Если не установлено, если доступный движок хранения, указанный в CREATE TABLE, недоступен, выдается предупреждение, и вместо него используется движок хранения по умолчанию. Если установлено, генерируется ошибка 1286 при создании таблицы, если указанный движок хранения недоступен. См. также enforce_storage_engine. По умолчанию с версии MariaDB 10.1.7.

NO_FIELD_OPTIONS

Удаляет специфичные для MariaDB опции столбцов из вывода SHOW CREATE TABLE. Также используется режимом переноса mariadb-dump.

NO_KEY_OPTIONS

Удаляет специфичные для MariaDB опции индексов из вывода SHOW CREATE TABLE. Также используется режимом переноса mariadb-dump.

NO_TABLE_OPTIONS

Удаляет специфичные для MariaDB опции таблиц из вывода SHOW CREATE TABLE. Также используется режимом переноса mariadb-dump.

NO_UNSIGNED_SUBTRACTION

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

NO_ZERO_DATE

Не разрешает '0000-00-00' как допустимую дату в строгом режиме (возвращает ошибку 1525). Нулевые даты можно вставлять с IGNORE. Если режим не строгий, генерируется предупреждение.

NO_ZERO_IN_DATE

Не разрешает даты, где год не равен нулю, но месяц или день даты равны нулю (возвращает ошибку 1525). Например, при заданном значении '0000-00-00' разрешается, но '1970-00-10' или '1929-01-00' — нет. Если используется опция ignore, MariaDB вставит '0000-00-00' для таких дат. Если режим не строгий, генерируется предупреждение вместо ошибки.

ONLY_FULL_GROUP_BY

Для запросов SELECT ... GROUP BY, запрещает SELECT столбцов, которые не указаны в условии GROUP BY, за исключением случаев, когда они передаются в агрегатную функцию, такую как COUNT() или MAX(). Возвращает ошибку 1055.

ORACLE

Во всех версиях MariaDB до MariaDB 10.2 это устанавливает sql_mode, что эквивалентно: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER

С MariaDB 10.3 этот режим также устанавливает SIMULTANEOUS_ASSIGNMENT и настраивает сервер на понимание большого подмножества языка Oracle PL/SQL вместо традиционной синтаксической конструкции MariaDB для хранимых процедур. См. SQL_MODE=ORACLE From MariaDB 10.3.

Если установлено, вывод SHOW CREATE TABLE не будет отображать атрибуты таблицы, специфичные для MariaDB.

PAD_CHAR_TO_FULL_LENGTH

Пробелы в конце CHAR-колонок по умолчанию обрезаются при извлечении. При включенном PAD_CHAR_TO_FULL_LENGTH обрезка не происходит. Не относится к VARCHAR.

PIPES_AS_CONCAT

Разрешает использовать символ «|» (ASCII 124) как оператор конкатенации строк. Это означает, что "A" || "B" может быть использован вместо CONCAT("A", "B").

POSTGRESQL

То же самое, что: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, POSTGRESQL, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

Если установлено, вывод SHOW CREATE TABLE не будет отображать атрибуты таблицы, специфичные для MariaDB.

REAL_AS_FLOAT

REAL является синонимом для FLOAT, а не DOUBLE.

SIMULTANEOUS_ASSIGNMENT

Установление этого значения заставляет часть SET оператора UPDATE оценивать все присваивания одновременно, а не слева направо. С версии MariaDB 10.3.5.

STRICT_ALL_TABLES

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

STRICT_TRANS_TABLES

Режим строгости. Операторы с некорректными или отсутствующими данными прерываются и отменяются, за исключением случаев, когда для нетранзакционных хранилищ и операторов, влияющих на несколько строк, где некорректные или отсутствующие данные не находятся в первой строке, MariaDB преобразует некорректное значение в ближайшее допустимое значение или, если значение отсутствует, вставит значение по умолчанию столбца. По умолчанию с MariaDB 10.2.4.

TIME_ROUND_FRACTIONAL

При выключенном режиме MariaDB обрезает дробные секунды при изменении точности на меньшую. При включенном режиме MariaDB будет округлять при преобразовании в TIME, DATETIME и TIMESTAMP и обрезать при преобразовании в DATE. С MariaDB 10.4.1

TRADITIONAL

Заставляет MariaDB работать как традиционный SQL-сервер. То же самое, что: STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER.

Режим строгости

Режим, в котором хотя бы один из STRICT_TRANS_TABLES или STRICT_ALL_TABLES включен, называется режимом строгости.

При установленном режиме строгости (по умолчанию с MariaDB 10.2.4), операторы, изменяющие таблицы (либо транзакционные для STRICT_TRANS_TABLES или все для STRICT_ALL_TABLES ), будут отклоняться, и вместо этого будет возвращено сообщение об ошибке. Ключевое слово IGNORE можно использовать при установленном режиме строгости для преобразования ошибки в предупреждение.

При не установленном режиме строгости (по умолчанию в версиях <= MariaDB 10.2.3), MariaDB автоматически корректирует недопустимые значения, например, обрезает слишком длинные строки или корректирует числовые значения, выходящие за пределы диапазона, и выводит предупреждение.

Операторы, не изменяющие данные, будут возвращать предупреждение при корректировке независимо от режима.

SQL_MODE и хранимые программы

Хранимые программы и представления всегда используют SQL_MODE, активный при их создании. Это означает, что пользователи могут безопасно изменять сеансовый или глобальный SQL_MODE; хранимые программы, которые они используют, по-прежнему будут работать как обычно.

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

Некоторые таблицы Information Schema (например, ROUTINES) и операторы SHOW CREATE, такие как SHOW CREATE PROCEDURE, отображают SQL_MODE, используемый хранимыми программами.

Примеры

В этом примере показано, как получить читаемый список включенных флагов SQL_MODE:

SELECT REPLACE(@@SQL_MODE, ',', '\n');
+-------------------------------------------------------------------------+
| REPLACE(@@SQL_MODE, ',', '\n')                                          |
+-------------------------------------------------------------------------+
| STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------+

Добавление нового флага:

SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_ENGINE_SUBSTITUTION');

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

Как отключить флаг:

SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ENGINE_SUBSTITUTION', '');

Как проверить, установлен ли флаг:

SELECT @@SQL_MODE LIKE '%NO_ZERO_DATE%';
+----------------------------------+
| @@SQL_MODE LIKE '%NO_ZERO_DATE%' |
+----------------------------------+
|                                1 |
+----------------------------------+

Без режима строгости и с ним:

CREATE TABLE strict (s CHAR(5), n TINYINT);

INSERT INTO strict VALUES ('MariaDB', '128');
Query OK, 1 row affected, 2 warnings (0.14 sec)

SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 's' at row 1     |
| Warning | 1264 | Out of range value for column 'n' at row 1 |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)

SELECT * FROM strict;
+-------+------+
| s     | n    |
+-------+------+
| Maria |  127 |
+-------+------+

SET sql_mode='STRICT_TRANS_TABLES';

INSERT INTO strict VALUES ('MariaDB', '128');
ERROR 1406 (22001): Data too long for column 's' at row 1

Переопределение режима строгости с помощью ключевого слова IGNORE:

INSERT IGNORE INTO strict VALUES ('MariaDB', '128');
Query OK, 1 row affected, 2 warnings (0.15 sec)

См. также

• SQL_MODE=MSSQL
• SQL_MODE=ORACLE