sql_mode

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

    Last
    Next

    OceanBaseOceanBase

    OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

    Company

    About OceanBaseContact UsPartnerTrust Center

    Product

    OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

    Resources

    DocsBlogWhite Paper

    Follow us

    LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

    © OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

    Contact Us