sql_mode|V4.4.2| docs|Distributed Database

sql_mode

Last Updated：2026-03-06 07:02:42 Updated

Note

This variable is available starting with V1.4.

Description

sql_mode specifies the SQL mode. Different SQL modes have significant impacts on database behavior.

Note

This variable is available only for OceanBase Database in MySQL-compatible mode.

Privilege requirements

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

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 is changed from `STRICT_ALL_TABLES,NO_ZERO_IN_DATE` to `STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER` starting with 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` starting with V4.2.1 BP1.
Value range	The value can be any combination of the following values: ANSI_QUOTES: The double quotation mark (") is treated as an identifier quote character (`), so you cannot use the double quotation mark to enclose strings. ERROR_FOR_DIVISION_BY_ZERO: When you execute an INSERT or UPDATE statement, an error is returned if the value of a column is divided by zero. If the ERROR_FOR_DIVISION_BY_ZERO mode is not enabled, NULL is returned when the value of a column is divided by zero. 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, NULL is returned when the value of a column is divided by zero. If both the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the ERROR_FOR_DIVISION_BY_ZERO mode are enabled, an error is returned when the value of a column is divided by zero. 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, NULL is returned when the value of a column is divided by zero. HIGH_NOT_PRECEDENCE: The NOT operator has a higher precedence than the AND operator. Therefore, the expression `NOT a BETWEEN b AND c` is parsed as NOT (a BETWEEN b AND c). IGNORE_SPACE: You can specify a space between the function name and the character. This field is only used for compatibility. You can set this field, but the setting has no effect. The behavior of this field in OceanBase Database is different from that in MySQL. NO_AUTO_CREATE_USER: You cannot use the GRANT statement to create a user whose password is empty. NO_DIR_IN_CREATE: All INDEX DIRECTORY and DATA DIRECTORY clauses are ignored when you create a table. This field is only used for compatibility. You can set this field, but the setting has no effect. The behavior of this field in OceanBase Database is different from that in MySQL. NO_ENGINE_SUBSTITUTION: An error is returned if the storage engine specified in the CREATE TABLE statement is disabled or not compiled. If this value is not specified, the default storage engine is used, and an exception is displayed. This field is only used for compatibility. You can set this field, but the setting has no effect. The behavior of this field in OceanBase Database is different from that in MySQL. NO_FIELD_OPTIONS: The output of the `SHOW CREATE TABLE` statement does not contain MySQL-specific column options. NO_KEY_OPTIONS: The output of the `SHOW CREATE TABLE` statement does not contain MySQL-specific index options. NO_TABLE_OPTIONS: The output of the `SHOW CREATE TABLE` statement does not contain MySQL-specific table options, such as the ENGINE option. NO_UNSIGNED_SUBTRACTION: By default, the result of a subtraction between two integer values, where one of the values is of the UNSIGNED type, is an unsigned value. NO_ZERO_DATE: Specifies whether to allow the date in the '0000-00-00' format. If the NO_ZERO_DATE mode is not enabled, the date in the '0000-00-00' format 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 in the '0000-00-00' format 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 in the '0000-00-00' format 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 in the '0000-00-00' format is allowed. NO_ZERO_IN_DATE: Allows the date in the 'YYYY-00-00' or 'YYYY-01-00' format, where the year is not 0, and the month or day is 0. If the NO_ZERO_IN_DATE mode is not enabled, the date in the 'YYYY-00-00' or 'YYYY-01-00' format, where the year is not 0, and the month or day is 0, 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 in the 'YYYY-00-00' or 'YYYY-01-00' format, where the year is not 0, and the month or day is 0, is converted to the date in 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 in the 'YYYY-00-00' or 'YYYY-01-00' format, where the year is not 0, and the month or day is 0, 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 in the 'YYYY-00-00' or 'YYYY-01-00' format, where the year is not 0, and the month or day is 0, is converted to the date in the '0000-00-00' format. ONLY_FULL_GROUP_BY: Only columns that are uniquely determined by the GROUP BY columns can be specified in the SELECT list, HAVING condition, or ORDER BY clause. PIPES_AS_CONCAT: The (	) operator is treated as a string concatenation operator (same as CONCAT()), not as an alias for the OR operator. PAD_CHAR_TO_FULL_LENGTH: Trailing spaces are retained when a CHAR value is retrieved. The retrieved CHAR value is padded to the full length. REAL_AS_FLOAT: By default, REAL is treated as an alias for DOUBLE. After this value is specified, REAL is treated as an alias for FLOAT. STRICT_ALL_TABLES: The strict SQL mode is enabled for all storage engines. Invalid data values are rejected. STRICT_TRANS_TABLES: The strict SQL mode is enabled for transactional storage engines. If possible, the strict SQL mode is also enabled for nontransactional storage engines. TIME_TRUNCATE_FRACTIONAL: Specifies whether to round TIME, DATE, or TIMESTAMP values that have a fractional second part when they are inserted into columns of the same type with fewer decimal places. By default, the values are rounded. If this mode is enabled, the fractional part is discarded. ANSI: Equivalent to the combination of the REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and ONLY_FULL_GROUP_BY options. DB2: Equivalent to the combination of the PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS options. MAXDB: Equivalent to the combination of the PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER options. MSSQL: Equivalent to the combination of the PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS options. MYSQL323: Equivalent to the combination of the HIGH_NOT_PRECEDENCE option and some features of the MySQL 3.23 version. MYSQL40: Equivalent to the combination of the HIGH_NOT_PRECEDENCE option and some features of the MySQL 4.0 version. ORACLE: Equivalent to the combination of the PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER options. POSTGRESQL: Equivalent to the combination of the PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS 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