sql_mode|V4.2.5| docs|Distributed Database

sql_mode

Last Updated：2026-02-12 02:58:32 Updated

Note

This variable is available starting with V1.4.

Description

The sql_mode variable specifies the SQL mode. Different SQL modes have significant effects on database behavior.

Note

This variable is available only in 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 obtain the values of global system variables.
- 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 obtain the values of session system variables.
Modify variables
- Global level
  - The sys tenant can directly modify the values of global system variables.
  - MySQL user tenants must have the SUPER or ALTER SYSTEM privilege to modify the values of global system variables.
- Session level
  
  The sys tenant and all user tenants can directly modify the values of session system variables.

Attributes

Attribute	Description
Parameter type	Uint
Default value	STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER Note For V4.3.x, the default value was changed from `STRICT_ALL_TABLES,NO_ZERO_IN_DATE` to `STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER` starting from V4.3.0. For V4.2.x, the default value was changed from `STRICT_ALL_TABLES,NO_ZERO_IN_DATE` to `STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER` starting from V4.2.1 BP1.
Value range	The value can be any combination of the following values: `ANSI_QUOTES`: Treats double quotation marks (") as identifier quotes (backticks (`)) and prevents the use of double quotation marks for string literals. `ERROR_FOR_DIVISION_BY_ZERO`: Returns an error when a division by zero occurs during the execution of an `INSERT` or `UPDATE` statement. If the `ERROR_FOR_DIVISION_BY_ZERO` mode is not enabled, a division 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 not enabled, a division by zero returns NULL. If both the strict SQL mode (`STRICT_ALL_TABLES` or `STRICT_TRANS_TABLES`) and the `ERROR_FOR_DIVISION_BY_ZERO` mode are enabled, a division by zero is not allowed and an error is returned. If both 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, a division by zero returns NULL. `HIGH_NOT_PRECEDENCE`: The `NOT` operator has a higher precedence than the `BETWEEN` operator. Therefore, an expression such as `NOT a BETWEEN b AND c` is parsed as `NOT (a BETWEEN b AND c)`. `IGNORE_SPACE`: Allows spaces between function names and characters. This field is only used for compatibility and can be set but does not take effect. `NO_AUTO_CREATE_USER`: Prevents the creation of users with empty passwords using the `GRANT` statement. `NO_DIR_IN_CREATE`: Ignores all `INDEX DIRECTORY` and `DATA DIRECTORY` instructions during table creation. This field is only used for compatibility and can be set but does not take effect. In OceanBase Database, the behavior is different from that in MySQL. `NO_ENGINE_SUBSTITUTION`: Returns an error if the specified storage engine is disabled or not compiled. If this value is not set, the default storage engine is used, and an exception is displayed. This field is only used for compatibility and can be set but does not take effect. In OceanBase Database, the behavior is different from that in MySQL. `NO_FIELD_OPTIONS`: Prevents the printing of MySQL-specific column options in the output of the `SHOW CREATE TABLE` statement. `NO_KEY_OPTIONS`: Prevents the printing of MySQL-specific index options in the output of the `SHOW CREATE TABLE` statement. `NO_TABLE_OPTIONS`: Prevents the printing of MySQL-specific table options (such as ENGINE) in the output of the `SHOW CREATE TABLE` statement. `NO_UNSIGNED_SUBTRACTION`: By default, the subtraction of an unsigned value from a signed value or vice versa results in an unsigned value. `NO_ZERO_DATE`: Specifies whether to allow the date format '0000-00-00'. If the `NO_ZERO_DATE` mode is not enabled, the date format '0000-00-00' is allowed. If the `NO_ZERO_DATE` mode is enabled but the strict SQL mode (`STRICT_ALL_TABLES` or `STRICT_TRANS_TABLES`) is not enabled, the date format '0000-00-00' is allowed. If both the strict SQL mode (`STRICT_ALL_TABLES` or `STRICT_TRANS_TABLES`) and the `NO_ZERO_DATE` mode are enabled, the date format '0000-00-00' 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_DATE` mode are enabled and the `ignore` option is specified, the date format '0000-00-00' is allowed. `NO_ZERO_IN_DATE`: Allows dates in the format where the year is not zero, but the month or day is zero (such as '2010-00-01' or '2010-01-00'). If the `NO_ZERO_IN_DATE` mode is not enabled, the date format where the year is not zero, but the month or day is zero is allowed. If the `NO_ZERO_IN_DATE` mode is enabled but the strict SQL mode (`STRICT_ALL_TABLES` or `STRICT_TRANS_TABLES`) is not enabled, the date format where the year is not zero, but the month or day is zero is converted to the '0000-00-00' format. If both the strict SQL mode (`STRICT_ALL_TABLES` or `STRICT_TRANS_TABLES`) and the `NO_ZERO_IN_DATE` mode are enabled, the date format where the year is not zero, but the month or day 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 the `ignore` option is specified, the date format where the year is not zero, but the month or day is zero is converted to the '0000-00-00' format. `ONLY_FULL_GROUP_BY`: Allows only columns that are uniquely determined by the `GROUP BY` columns in the `SELECT`, `HAVING`, or `ORDER BY` clauses. `PIPES_AS_CONCAT`: Treats the (	) operator as a string concatenation operator (same as CONCAT()) instead of an alias for the OR operator. `PAD_CHAR_TO_FULL_LENGTH`: Retains trailing spaces in CHAR columns during retrieval and fills the retrieved CHAR values to their full length. `REAL_AS_FLOAT`: By default, treats `REAL` as an alias for `DOUBLE`. After this value is set, `REAL` is treated as an alias 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 transactional storage engines and, if possible, for non-transactional storage engines. `TIME_TRUNCATE_FRACTIONAL`: Controls whether to round TIME, DATE, or TIMESTAMP values with fractional seconds when inserting them into columns of the same type with fewer decimal places. By default, rounding is performed. If this mode is enabled, the fractional part is discarded. `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 with some additional features specific to MySQL 3.23. `MYSQL40`: Equivalent to the combination of HIGH_NOT_PRECEDENCE with some additional features specific to 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
Modifiable	Yes. You can execute the `SET` statement to modify the value.

Examples

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

Session level

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

Global level

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

References

INFORMATION_SCHEMA.TRIGGERS