sql_mode specifies the SQL mode. Database actions vary greatly with the SQL modes.
| Attribute |
Description |
| Type |
Varchar |
| Default value |
STRICT_ALL_TABLES,NO_ZERO_IN_DATE |
| Value range |
Any combination of the following values is supported:
- ANSI_QUOTES: The string quotation mark (") is used as the identifier quotation mark (`). Therefore, strings cannot be quoted with double quotation marks.
- ERROR_FOR_DIVISION_BY_ZERO: affects the handling of division by zero when
INSERT or UPDATE statements are executed.
- If
ERROR_FOR_DIVISION_BY_ZERO is disabled, division by zero inserts NULL.
- If
ERROR_FOR_DIVISION_BY_ZERO is enabled but strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) is disabled, division by zero inserts NULL.
- If both strict SQL mode (
STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and ERROR_FOR_DIVISION_BY_ZERO are enabled, division by zero produces an error.
- If both strict SQL mode (
STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and ERROR_FOR_DIVISION_BY_ZERO are enabled and ignore is specified, division by zero inserts NULL.
- NO_AUTO_CREATE_USER: prevents the
GRANT statement from automatically creating new user accounts with empty passwords.
- NO_ENGINE_SUBSTITUTION: returns an error when a statement specifies a storage engine that is disabled or not compiled in. If this mode is disabled, the default storage engine is used, and an exception occurs when the default engine is unavailable. At present, this mode is for compatibility purposes only.
- NO_ZERO_DATE: specifies whether the server allows dates in the '0000-00-00' format.
- If the
NO_ZERO_DATE mode is disabled, dates in the '0000-00-00' format are allowed.
- When 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 allowed.
- If both 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 both the strict SQL mode (
STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the NO_ZERO_DATE mode are enabled and "ignore" is specified, dates in the '0000-00-00' format are allowed.
- NO_ZERO_IN_DATE: specifies whether the server allows dates in which the year part is nonzero but the month or day part is zero. For example, this mode affects dates such as '2010-00-01' and '2010-01-00'.
- If the
NO_ZERO_IN_DATE mode is disabled, the server allows a date in which the year part is nonzero but the month or day part is zero.
- If the
NO_ZERO_IN_DATE mode is enabled but the strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES) is disabled, a date in which the year part is nonzero but the month or day part is zero will be inserted as '0000-00-00'.
- If both the strict SQL mode (
STRICT_ALL_TABLES or STRICT_TRANS_TABLES) and the NO_ZERO_IN_DATE mode are enabled, a date in which the year part is nonzero but the month or day part 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 "ignore" is specified, a date in which the year part is nonzero but the month or day part is zero will be inserted as '0000-00-00'.
- ONLY_FULL_GROUP_BY: allows queries for which the select list, HAVING condition, or
ORDER BY list refers to non-aggregated columns that are neither named in the GROUP BY clause nor functionally dependent on or uniquely determined by GROUP BY columns.
- PAD_CHAR_TO_FULL_LENGTH: retains the trailing spaces in CHAR column values upon retrieval. The retrieved CHAR values are padded to their full length.
- 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 the transaction storage engine and if possible, for non-transaction storage engines.
|
| Applicable scope |
GlobalSession |
The following example shows how to set the SQL mode of the current session to STRICT_ALL_TABLES,STRICT_TRANS_TABLES.
obclient>SET SESSION sql_mode = 'STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
