| Value range |
The value can be any combination of the following values:
- ANSI_QUOTES: Treats double quotation marks (") as identifier delimiters instead of string delimiters. Therefore, double quotation marks cannot be used to enclose strings.
- ERROR_FOR_DIVISION_BY_ZERO: When using the INSERT or UPDATE statement, an error is generated if a division by zero occurs.
- 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 generated.
- 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 precedence of the NOT operator causes expressions such as
NOT a BETWEEN b AND c to be parsed as NOT (a BETWEEN b AND c).
- IGNORE_SPACE: Allows spaces between function names and characters. This field is currently 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 when creating tables. This field is currently only used for compatibility and can be set but does not take effect. Its behavior in OceanBase Database is different from that in MySQL.
- NO_ENGINE_SUBSTITUTION: If the specified storage engine is disabled or not compiled, an error is generated. If this value is not set, the default storage engine is used, and an exception is displayed. This field is currently only used for compatibility and can be set but does not take effect. Its behavior in OceanBase Database 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, subtraction between integer values (where one is of the UNSIGNED type) produces an unsigned result.
- NO_ZERO_DATE: Specifies whether to allow the '0000-00-00' date format.
- If the NO_ZERO_DATE mode is not enabled, the '0000-00-00' date 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 '0000-00-00' date 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 '0000-00-00' date format is not allowed, and an error is generated.
- 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 '0000-00-00' date format is allowed.
- NO_ZERO_IN_DATE: Allows dates where the year is non-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, dates where the year is non-zero, but the month or day is zero are 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, dates where the year is non-zero, but the month or day is zero are converted to the '0000-00-00' date format.
- If both the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the NO_ZERO_IN_DATE mode are enabled, dates where the year is non-zero, but the month or day is zero are not allowed, and an error is generated.
- 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, dates where the year is non-zero, but the month or day is zero are converted to the '0000-00-00' date format.
- ONLY_FULL_GROUP_BY: Allows only columns in the SELECT list, HAVING conditions, or ORDER BY clauses that are uniquely determined by the GROUP BY columns.
- PIPES_AS_CONCAT: Treats (
|
) as a string concatenation operator (same as CONCAT()) instead of an alias for OR. 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 setting, treats REAL as an alias for FLOAT. STRICT_ALL_TABLES: Enables strict SQL mode for all storage engines and rejects invalid data values.STRICT_TRANS_TABLES: Enables strict SQL mode for transactional storage engines and, if possible, for non-transactional storage engines.TIME_TRUNCATE_FRACTIONAL: Controls whether rounding occurs when inserting TIME, DATE, or TIMESTAMP values with fractional seconds into columns of the same type with fewer decimal places. By default, rounding occurs. 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: Adds some features of MYSQL323 to the HIGH_NOT_PRECEDENCE mode.MYSQL40: Adds some features of MYSQL40 to the HIGH_NOT_PRECEDENCE mode.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. |
