sql_mode
Default SQL mode
- NO_ENGINE_SUBSTITUTION
- From 5.7.5 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES
- From 5.7.7 NO_AUTO_CREATE_USER
- From 5.7.8 NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_IN_DATE
Set sql_mode
in my.cnf
- sql-mode="value1,value2"
dynamic
- SET GLOBAL sql_mode = 'modes';
- SET SESSION sql_mode = 'modes';
Full List of SQL Modes
(based on 5.7.23)
Strict SQL mode
- STRICT_ALL_TABLES - Enable strict SQL mode for all storage engines.
- STRICT_TRANS_TABLES - (Default) Enable strict SQL mode for transactional storage engines.
Data integrity
- ALLOW_INVALID_DATES
- ERROR_FOR_DIVISION_BY_ZERO (Default)
- NO_ZERO_DATE (Default)
- NO_ZERO_IN_DATE (Default)
- PAD_CHAR_TO_FULL_LENGTH
SQL
- ANSI_QUOTES
- HIGH_NOT_PRECEDENCE
- ONLY_FULL_GROUP_BY (Default)
- PIPES_AS_CONCAT
- REAL_AS_FLOAT
- NO_UNSIGNED_SUBTRACTION
- NO_AUTO_VALUE_ON_ZERO
Client
- NO_FILES_OPTIONS
- NO_KEY_OPTIONS
- NO_TABLE_OPTIONS
Etc
- NO_AUTO_CREATE_USER (Default)
- NO_ENGINE_SUBSTITUTION (Default)
- NO_DIR_IN_CREATE
- IGNORE_SPACE
- NO_BACKSLASH_ESCAPES
Combination SQL Modes
- ANSI = REAL_AS_FLOAT + PIPES_AS_CONCAT + ANSI_QUOTES + IGNORE_SPACE + ONLY_FULL_GROUP_BY
- ORACLE= PIPES_AS_CONCAT + ANSI_QUOTES + IGNORE_SPACE + NO_KEY_OPTIONS + NO_TABLE_OPTIONS + NO_FILED_OPTIONS + NO_AUTO_CREATE_USER
( 5.7.22 Will be deprecated or removed)
Strict SQL Mode
Enabled when using STRICT_ALL_TABLES or STRICT_TRANS_TABLES.
This mode controls how MySQL handles invalid or missing values in data-change statements.
Under strict SQL Mode
- Index creation failed when key exceed maximum_key_length
- For transaction tables, Error occurs for invalid or missing values in data-change statement
| Operation | Strict mode | |
| Enabled | Disabled | |
| Division by zero | Insert NULL with warning | Insert NULL without warning and error |
| Insert ZERO date - '0000-00-00' | Not permitted and inserts produce an error | Insert without warning |
| Insert ZERO in date - '2010-00-01', '2010-01-00' | Not permitted and inserts produce an error | Insert without warning |
The following options only valid when use with strict sql mode.
- NO_ZERO_DATE (Default) '0000-00-00'
- NO_ZERO_IN_DATE (Default) '2019-00-00'
