sql_mode
This page is part of MariaDB's Enterprise Documentation.
The parent of this page is: System Variables for MariaDB Enterprise Server
Topics on this page:
Overview
Overrides the default behavior of the server in several contexts.
See also: System Variables for MariaDB Enterprise Server 10.6, in 10.6 CS, in 10.5 ES, in 10.5 CS, in 10.4 ES, in 10.4 CS, in 10.3 ES, in 10.3 CS, in 10.2 ES, and in 10.2 CS
USAGE
The sql_mode
system variable can be set in a configuration file:
[mariadb]
sql_mode=ORACLE
The global value of the sql_mode
system variable can also be set dynamically at runtime by executing SET GLOBAL:
SET GLOBAL sql_mode='ORACLE';
The session value of the sql_mode
system variable can also be set dynamically at runtime by executing SET SESSION:
SET SESSION sql_mode='ORACLE';
When the sql_mode
system variable is set dynamically at runtime, its value will be reset the next time the server restarts. To make the value persist on restart, set it in a configuration file too.
DETAILS
The sql_mode
system variable can be used to override the default behavior of the server in several contexts, such as:
How SQL statements are parsed.
How SQL statements are executed.
How SQL statements handle errors.
How SQL statements interpret data types.
The options for the sql_mode
system variable are divided into two categories:
Base
sql_mode
options.Alias
sql_mode
options.
The list of base options for the sql_mode
system variable are listed in the following table:
SQL Mode | Description |
---|---|
| 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. |
| 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. |
| This mode causes the empty string to be treated the same as |
| This mode causes an error to be thrown when a number is divided by zero. Otherwise, |
| This mode allows aliases to work similar to aliases in Sybase SQL Anywhere. Aliases are resolved in the SELECT list and WHERE clause. Aliases can be used in the SELECT list before the alias is defined. If the same label is used for an alias and a column, the alias is used. |
| This mode increases the precedence of the NOT operator, so that |
| This mode causes bad table options provided to the CREATE TABLE and ALTER TABLE statements to generate warnings instead of errors. |
| 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. |
| 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. |
| This mode does not interpret the value 0 the same way as |
| This mode does not allow the backslash character () to be used to escape characters within strings. Instead, it is just a regular character. |
| This mode causes the |
| 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. |
| This mode prevents the SHOW CREATE TABLE statement from printing MariaDB-specific column options. |
| This mode prevents the SHOW CREATE TABLE statement from printing MariaDB-specific index options. |
| This mode prevents the SHOW CREATE TABLE statement from printing MariaDB-specific table options. |
| This mode forces subtraction results to use signed data types, even if the operands use unsigned data types. |
| This mode prohibits '0000-00-00' as a valid value for the DATE data type. |
| 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. |
| This mode disallows columns to be selected in |
| This mode causes columns using the CHAR data type to be padded to the column's full length when the column is selected. |
| This mode makes the || operator become functionally equivalent to the CONCAT() function, rather than functionally equivalent to the OR operator. This means that |
| This mode makes the REAL data type become an alias for the FLOAT data type, rather than an alias for the DOUBLE data type. |
| This mode causes the server to evaluate all assignments in the |
| 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. |
| 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. |
| This mode causes the server to round fractional seconds when the value is converted to a data type without the fractional seconds. |
The list of alias options for the sql_mode
system variable are listed in the following table:
SQL Mode | Description |
---|---|
| This mode is an alias for REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE. |
| This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS. |
| 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. |
| This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS. |
| This mode is an alias for HIGH_NOT_PRECEDENCE. |
| This mode is an alias for HIGH_NOT_PRECEDENCE. |
| 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. |
| This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS. |
| 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. |
PARAMETERS
Command-line | --sql_mode={REAL_AS_FLOAT| |
Configuration file | Supported |
Dynamic | Yes |
Scope | Global, Session |
Data Type | SET (REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, IGNORE_BAD_TABLE_OPTIONS, ONLY_FULL_GROUP_BY, NO_UNSIGNED_SUBTRACTION, NO_DIR_IN_CREATE, POSTGRESQL, ORACLE, MSSQL, DB2, MAXDB, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, MYSQL323, MYSQL40, ANSI, NO_AUTO_VALUE_ON_ZERO, NO_BACKSLASH_ESCAPES, STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ALLOW_INVALID_DATES, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER, HIGH_NOT_PRECEDENCE, NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH, EMPTY_STRING_IS_NULL, SIMULTANEOUS_ASSIGNMENT, TIME_ROUND_FRACTIONAL, EXTENDED_ALIASES) |
Default Value | STRICT_TRANS_TABLES, |
PRIVILEGES
The sql_mode
system variable requires the SUPER
privilege to dynamically set its global value at runtime.
The sql_mode
system variable does not require any special privilege to dynamically set its session value at runtime.
EXAMPLES
Specifying Multiple SQL Modes
Under most circumstances, you would normally specify multiple values for the sql_mode
system variable, while separating each one with a comma:
SET SESSION sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
Adding Values to Current SQL Mode
You can easily add values to the current value of the sql_mode
system variable using the CONCAT() function:
SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
SET SESSION sql_mode=CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES');
SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------+
Temporarily Changing SQL Mode
You can temporarily change the value of the sql_mode
system variable using a user variable:
SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
SET @old_sql_mode=@@session.sql_mode;
SET SESSION sql_mode='ORACLE';
SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
SET SESSION sql_mode=@old_sql_mode;
SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
Dynamically Resetting Global Value to Default
The global value of the sql_mode
system variable can be dynamically reset to the default value by setting it to DEFAULT
:
SET GLOBAL sql_mode='ORACLE';
SHOW GLOBAL VARIABLES LIKE 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
SET GLOBAL sql_mode=DEFAULT;
SHOW GLOBAL VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
Resetting Session Value to Global Value
The session value of the sql_mode
system variable can be reset to the global value by setting it to DEFAULT
:
SET SESSION sql_mode='ORACLE';
SHOW GLOBAL VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
SET SESSION sql_mode=DEFAULT;
SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
Invalid Value for Variable
If a user tries to set the sql_mode
system variable to an invalid value, then the operation will fail with the ER_WRONG_VALUE_FOR_VAR error code:
SET GLOBAL sql_mode=-1;
ERROR 1231 (42000): Variable 'default_password_lifetime' can't be set to the value of '-1'
SET GLOBAL sql_mode='STRICT_TRANS_TABLE';
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'STRICT_TRANS_TABLE'
Invalid Type for Variable
If a user tries to set the sql_mode
system variable to an invalid argument, then the operation will fail with the ER_WRONG_TYPE_FOR_VAR error code:
SET GLOBAL sql_mode=1.0;
ERROR 1232 (42000): Incorrect argument type to variable 'sql_mode'
CHANGE HISTORY
Release Series | History |
---|---|
10.6 Enterprise |
|
10.6 Community |
|
10.5 Enterprise |
|
10.5 Community |
|
10.4 Enterprise |
|
10.4 Community |
|
10.3 Enterprise |
|
10.3 Community |
|
10.2 Enterprise |
|
10.2 Community |
|
EXTERNAL REFERENCES
Information specific to MariaDB SkySQL can be found on the sql_mode page in the SkySQL Documentation.