SQL Modes

This page is part of MariaDB's Documentation.

The parent of this page is: Reference for MariaDB Enterprise Server 10.2

Topics on this page:

Overview

Overview

The supported SQL modes in MariaDB Enterprise Server:

SQL Mode	Description
ALLOW_INVALID_DATES	This mode allows the DATE data type to have any value in the range 1-31 in the day component, even if it isn't a valid date
ANSI_QUOTES	This mode makes the " character become the identifier quote character, instead of the ` character. This is more compatible with the ANSI standard, but it may break applications developed for MariaDB if they use the " character to quote string literals.
ERROR_FOR_DIVISION_BY_ZERO	This mode causes an error to be thrown when a number is divided by zero. Otherwise, `NULL` is returned.
HIGH_NOT_PRECEDENCE	This mode increases the precedence of the NOT operator, so that `NOT a BETWEEN b and c` is interpreted as `(NOT a) BETWEEN b and C`. This mode is more compatible with MySQL 5.0.1 and before.
IGNORE_BAD_TABLE_OPTIONS	This mode causes bad table options provided to the CREATE TABLE and ALTER TABLE statements to generate warnings instead of errors
IGNORE_SPACE	This mode allows spaces to appear between function names and parenthesis. However, a side effect is that the names of built-in functions become reserved words.
NO_AUTO_CREATE_USER	This mode prohibits the GRANT statement from automatically creating a user, unless the statement also included authentication-related information, such as a password or an authentication plugin
NO_AUTO_VALUE_ON_ZERO	This mode disables the default behavior that occurs when the value `0` is inserted into an `AUTO_INCREMENT` column. By default, the value `0` is handled similar to `NULL`, so a new auto-increment value is generated and inserted into the column, and the value `0` is discarded. When this mode is enabled, no auto-increment value is generated, and the value `0` is inserted into the column.
NO_BACKSLASH_ESCAPES	This mode does not allow the backslash character () to be used to escape characters within strings. Instead, it is just a regular character.
NO_DIR_IN_CREATE	This mode causes the `DATA DIRECTORY` and `INDEX DIRECTORY` table options to be ignored in the CREATE TABLE and ALTER TABLE statements
NO_ENGINE_SUBSTITUTION	This mode does not allow a storage engine to be substituted with the default. Usually, if a storage engine is specified in a CREATE TABLE or ALTER TABLE statement and that storage engine doesn't exist, then a warning with the ER_UNKNOWN_STORAGE_ENGINE error code will be raised, and the storage engine will be substituted with the default. This mode causes an error to be thrown instead.
NO_FIELD_OPTIONS	This mode prevents the SHOW CREATE TABLE statement from printing MariaDB-specific column options
NO_KEY_OPTIONS	This mode prevents the SHOW CREATE TABLE statement from printing MariaDB-specific index options
NO_TABLE_OPTIONS	This mode prevents the SHOW CREATE TABLE statement from printing MariaDB-specific table options
NO_UNSIGNED_SUBTRACTION	This mode forces subtraction results to use signed data types, even if the operands use unsigned data types
NO_ZERO_DATE	This mode prohibits '0000-00-00' as a valid value for the DATE data type
NO_ZERO_IN_DATE	This mode prohibits the DATE data type from having a non-zero value for the year component when the date does have zero values for the month or day components. For example, '0000-00-000' would be allowed, but '2020-01-00', '2020-00-01', and '2020-00-00' would be prohibited.
ONLY_FULL_GROUP_BY	This mode disallows columns to be selected in `SELECT ... GROUP BY` statements, unless the column is in the `GROUP BY` clause or the column is provided to an aggregate function, such as the COUNT() function.
PAD_CHAR_TO_FULL_LENGTH	This mode causes columns using the CHAR data type to be padded to the column's full length when the column is selected
PIPES_AS_CONCAT	This mode makes the \|\| operator become functionally equivalent to the CONCAT() function, rather than functionally equivalent to the OR operator. This means that `a \|\| b` would be functionally equivalent to `CONCAT(a, b)`.
REAL_AS_FLOAT	This mode makes the REAL data type become an alias for the FLOAT data type, rather than an alias for the DOUBLE data type
STRICT_ALL_TABLES	This mode enables strict mode for tables that use transactional and non-transactional storage engines. In strict mode, any statements that involve invalid or missing data will generate errors instead of warnings. With non-transactional storage engines, statements cannot be rolled back, so if the error occurs after the first row, then the statement may only be partially completed.
STRICT_TRANS_TABLES	This mode enables strict mode for tables that use transactional storage engines. In strict mode, any statements that involve invalid or missing data will generate errors instead of warnings, and the statement will be aborted and rolled back.

A SQL mode alias sets one or more alternate mode flags instead of putting its own name into the SQL mode string.

The aliased SQL modes in MariaDB Enterprise Server 10.2:

SQL Mode	Description
ANSI	This mode is an alias for REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE
DB2	This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS
MAXDB	This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER
MSSQL	This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS
MYSQL40	This mode is an alias for HIGH_NOT_PRECEDENCE
MYSQL323	This mode is an alias for HIGH_NOT_PRECEDENCE
ORACLE	This mode enables a subset of Oracle's PL/SQL language for stored procedures. It also enables the following other modes: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER, SIMULTANEOUS_ASSIGNMENT.
POSTGRESQL	This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS
TRADITIONAL	This mode is an alias for 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