sql_mode|V4.3.5| docs|Distributed Database

sql_mode

Last Updated：2025-11-14 07:33:33 Updated

Note

This variable was introduced in V1.4.

Description

The sql_mode variable specifies the SQL mode. Different SQL modes significantly affect database behavior.

Note

This variable applies only to OceanBase Database in MySQL mode.

Privilege requirements

Query variables
- Global level
  
  The sys tenant and all user tenants can execute the SHOW VARIABLES statement or query the information_schema.GLOBAL_VARIABLES view (MySQL mode) to view the value of a global system variable.
- Session level
  
  The sys tenant and all user tenants can execute the SHOW VARIABLES statement or query the information_schema.SESSION_VARIABLES view (MySQL mode) to view the value of a session system variable.
Modify variables
- Set the variable at the global level
  - The sys tenant can directly modify the value of a global system variable.
  - MySQL user tenants must have the SUPER or ALTER SYSTEM privilege to modify the value of a global system variable.
- Set the variable at the session level
  
  The sys tenant and all user tenants can directly modify the value of a session system variable in the current tenant.

Attributes

Attribute	Description
Type	Uint
Default value	STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER Note For V4.3.x, the default value is changed from `STRICT_ALL_TABLES,NO_ZERO_IN_DATE` to `STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER` as of V4.3.0. For V4.2.x, the default value is changed from `STRICT_ALL_TABLES,NO_ZERO_IN_DATE` to `STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER` as of V4.2.1 BP1.
Valid values	The variable can be set to one or more of the following values: ANSI_QUOTES: Treats double quotation marks (") as identifier quotation marks (`) and does not allow the use of double quotation marks to reference strings. ERROR_FOR_DIVISION_BY_ZERO: Returns an error when data is divided by zero in an INSERT or UPDATE statement. If the ERROR_FOR_DIVISION_BY_ZERO mode is disabled, data divided by zero returns NULL. If the ERROR_FOR_DIVISION_BY_ZERO mode is enabled but the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) is disabled, data divided by zero returns NULL. If the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the ERROR_FOR_DIVISION_BY_ZERO mode are enabled, data divided by zero is not allowed and an error is returned. If the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the ERROR_FOR_DIVISION_BY_ZERO mode are enabled and the ignore option is specified, data divided by zero returns NULL. HIGH_NOT_PRECEDENCE: The NOT operator has a lower precedence than the BETWEEN operator, so the `NOT a BETWEEN b AND c` expression is parsed as NOT (a BETWEEN b AND c). IGNORE_SPACE: Allows spaces between function names and characters. This field is currently used only for compatibility. It can be set but takes no effect. NO_AUTO_CREATE_USER: Does not allow users with empty passwords to be created by using the GRANT statement. NO_DIR_IN_CREATE: Ignores all INDEX DIRECTORY and DATA DIRECTORY instructions when a table is created. This field is currently used only for compatibility. It can be set but takes no effect, and behaves differently from MySQL in OceanBase Database. NO_ENGINE_SUBSTITUTION: Returns an error when the specified storage engine is disabled or not compiled. If this variable is not set, the default storage engine is used and an exception is displayed. This field is currently used only for compatibility. It can be set but takes no effect, and behaves differently from MySQL in OceanBase Database. NO_FIELD_OPTIONS: Does not allow MySQL-specific column options to be printed in the output of the `SHOW CREATE TABLE` statement. NO_KEY_OPTIONS: Does not allow MySQL-specific index options to be printed in the output of the `SHOW CREATE TABLE` statement. NO_TABLE_OPTIONS: Does not allow MySQL-specific table options (such as ENGINE) to be printed in the output of the `SHOW CREATE TABLE` statement. NO_UNSIGNED_SUBTRACTION: By default, the result of subtracting one integer value from another (where one of the values is of the UNSIGNED type) is unsigned. NO_ZERO_DATE: Specifies whether to allow dates in the '0000-00-00' format. If the NO_ZERO_DATE mode is disabled, dates in the '0000-00-00' format are allowed. If 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 converted to '0000-00-00'. If 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 the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the NO_ZERO_DATE mode are enabled and the ignore option is specified, dates in the '0000-00-00' format are converted to '0000-00-00'. NO_ZERO_IN_DATE: Allows dates in the format of a non-zero year, zero month, or zero day (such as '2010-00-01' or '2010-01-00'). If the NO_ZERO_IN_DATE mode is disabled, dates in the format of a non-zero year, zero month, or zero day are allowed. If the NO_ZERO_IN_DATE mode is enabled but the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) is disabled, dates in the format of a non-zero year, zero month, or zero day are converted to '0000-00-00'. If the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the NO_ZERO_IN_DATE mode are enabled, dates in the format of a non-zero year, zero month, or zero day are not allowed and an error is returned. If the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the NO_ZERO_IN_DATE mode are enabled and the ignore option is specified, dates in the format of a non-zero year, zero month, or zero day are converted to '0000-00-00'. ONLY_FULL_GROUP_BY: Only allows SELECT lists, HAVING conditions, or ORDER BY columns to include columns uniquely determined by the GROUP BY columns. PIPES_AS_CONCAT: Treats (	) as the string concatenation operator (the same as CONCAT()) instead of the OR operator. PAD_CHAR_TO_FULL_LENGTH: Retains trailing spaces in CHAR values retrieved from a column and pads the retrieved CHAR values to their full length. REAL_AS_FLOAT: By default, REAL is treated as a synonym for DOUBLE. After this variable is set, 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 transaction storage engines and enables it for nontransaction storage engines when possible. TIME_TRUNCATE_FRACTIONAL: Specifies whether to truncate a TIME, DATE, or TIMESTAMP value with a fractional second part when it is inserted into a column of the same type but with fewer fractional digits. The default value is Rounding. If this mode is enabled, the fractional part is truncated. ANSI: Equivalent to the combination of REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and ONLY_FULL_GROUP_BY. DB2: Equivalent to the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS. MAXDB: Equivalent to the combination 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 the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS. MYSQL323: Equivalent to the combination of HIGH_NOT_PRECEDENCE and some features of MySQL 3.23. MYSQL40: Equivalent to the combination of HIGH_NOT_PRECEDENCE and some features of MySQL 4.0. ORACLE: Equivalent to the combination 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 the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS.
Scope	Global Session

Examples

Set the SQL mode to STRICT_ALL_TABLES, NO_ZERO_IN_DATE, and NO_AUTO_CREATE_USER.

Take effect at the session level

obclient> SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER';

Take effect at the global level

obclient> SET GLOBAL sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER';

References

INFORMATION_SCHEMA TRIGGERS