sql_mode|V4.1.0| docs|Distributed Database

sql_mode

Last Updated：2023-08-04 02:22:07 Updated

sql_mode specifies the SQL mode. Database actions vary greatly with the SQL modes.

Note

This variable is supported only in MySQL mode.

Attribute	Description
Type	uint
Default value	STRICT_ALL_TABLES,NO_ZERO_IN_DATE
Value range	Any combination of the following values is supported: ANSI_QUOTES: The string quotation mark (") is used as the identifier quotation mark (`). Therefore, strings cannot be quoted with double quotation marks. ERROR_FOR_DIVISION_BY_ZERO: affects the handling of division by zero when INSERT or UPDATE statements are executed. If ERROR_FOR_DIVISION_BY_ZERO is disabled, division by zero inserts NULL. If ERROR_FOR_DIVISION_BY_ZERO is enabled but strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) is disabled, division by zero inserts NULL. If both strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and ERROR_FOR_DIVISION_BY_ZERO are enabled, division by zero produces an error. If both strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and ERROR_FOR_DIVISION_BY_ZERO are enabled and ignore is specified, division by zero inserts NULL. HIGH_NOT_PRECEDENCE: The precedence of the NOT operator is such that expressions such as `NOT a BETWEEN b AND c` are parsed as NOT (a BETWEEN b AND c). IGNORE_SPACE: permits spaces between a function name and the character. The default mode of OceanBase Database is optional. NO_AUTO_CREATE_USER: prevents the GRANT statement from automatically creating new user accounts with empty passwords. NO_DIR_IN_CREATE: ignores all INDEX DIRECTORY and DATA DIRECTORY directives when creating a table. At present, this mode is for compatibility purpose only. NO_ENGINE_SUBSTITUTION: returns an error when a statement specifies a storage engine that is disabled or not compiled in. If this mode is disabled, the default storage engine is used, and an exception occurs when the default engine is unavailable. At present, this mode is for compatibility purposes only. NO_FIELD_OPTIONS: does not print MySQL-specific column options in the output of `SHOW CREATE TABLE`. NO_KEY_OPTIONS: does not print MySQL-specific index options in the output of `SHOW CREATE TABLE`. NO_TABLE_OPTIONS: does not print MySQL-specific table options (such as ENGINE) in the output of `SHOW CREATE TABLE`. NO_UNSIGNED_SUBTRACTION: Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. NO_ZERO_DATE: specifies whether the server allows dates in the '0000-00-00' format. If the NO_ZERO_DATE mode is disabled, dates in the '0000-00-00' format are allowed. When the NO_ZERO_DATE mode is enabled but the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) is disabled, dates in the '0000-00-00' format are allowed. If both the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the NO_ZERO_DATE mode are enabled, dates in the '0000-00-00' format are not allowed, and an error is returned. If both the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the NO_ZERO_DATE mode are enabled and 'ignore' is specified, dates in the '0000-00-00' format are allowed. NO_ZERO_IN_DATE: specifies whether the server allows dates in which the year part is nonzero but the month or day part is zero. For example, this mode affects dates such as '2010-00-01' and '2010-01-00'. If the NO_ZERO_IN_DATE mode is disabled, the server allows a date in which the year part is nonzero but the month or day part is zero. If the NO_ZERO_IN_DATE mode is enabled but the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) is disabled, a date in which the year part is nonzero but the month or day part is zero will be inserted as '0000-00-00'. If both the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the NO_ZERO_IN_DATE mode are enabled, a date in which the year part is nonzero but the month or day part is zero is not allowed, and an error is returned. If both the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the NO_ZERO_IN_DATE mode are enabled and 'ignore' is specified, a date in which the year part is nonzero but the month or day part is zero will be inserted as '0000-00-00'. ONLY_FULL_GROUP_BY: allows queries for which the select list, HAVING condition, or ORDER BY list refers to non-aggregated columns that are neither named in the GROUP BY clause nor functionally dependent on or uniquely determined by GROUP BY columns. PIPES_AS_CONCAT: treats \|\| as a string concatenation operator (same as CONCAT()) rather than a synonym for OR. PAD_CHAR_TO_FULL_LENGTH: retains the trailing spaces in CHAR column values upon retrieval. The retrieved CHAR values are padded to their full length. REAL_AS_FLOAT: By default, REAL is treated as a synonym for DOUBLE. If this mode is enabled, REAL is treated as a synonym for FLOAT. STRICT_ALL_TABLES: enables the strict SQL mode for all storage engines and rejects invalid data values. STRICT_TRANS_TABLES: enables the strict SQL mode for the transaction storage engine and if possible, for non-transaction storage engines. TIME_TRUNCATE_FRACTIONAL: controls whether rounding or truncation is performed when inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column having the same type but fewer fractional digits. Rounding is performed by default. If this mode is enabled, truncation is performed instead. ANSI: equivalent to combinations of REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and ONLY_FULL_GROUP_BY. DB2: equivalent to combinations of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS. MAXDB: equivalent to combinations of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER. MSSQL: equivalent to combinations of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS. MYSQL323: equivalent to HIGH_NOT_PRECEDENCE, except for some features specific to MYSQL323. MYSQL40: equivalent to HIGH_NOT_PRECEDENCE, except for some features specific to MYSQL40. ORACLE: equivalent to combinations of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER. POSTGRESQL: equivalent to combinations of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS.
Applicable scope	Global Session

The following example shows how to set the SQL mode of the current session to STRICT_ALL_TABLES,STRICT_TRANS_TABLES:

obclient>SET SESSION sql_mode = 'STRICT_ALL_TABLES,STRICT_TRANS_TABLES';