SQL mode affects the SQL syntax supported by MySQL and performs data validation checks.
This article is organized according to the Official Handbook Https://dev.mysql.com/doc/refman/5.7/en/sql-mode.htmlvgli
Completed section: Setting up and querying the full list of SQL patterns in SQL mode, MySQL5.7
Unfinished section: Detailed description of strict mode, relationship between ignore keyword and strict mode, changes in SQL mode in MySQL5.7
setting and querying SQL mode
Change the SQL schema by modifying the value of the Sql_mode variable.
SQL mode can be set at the global level or at the session level. The value of Sql_mode can be modified when the database is started and when the database is running.
To set the SQL mode when the database starts
Use the--sql_mode= ' modes ' option on the command line, or use sql_mode= "modes" in the configuration file.
Modes is a comma-delimited list of patterns.
To clear the SQL schema, set it to an empty string, such as sql_mode= ""
Setting SQL mode while the database is running
Use the SET statement to change the value of the Sql_mode, for example:
SET GLOBAL sql_mode = ' modes ';
SET SESSION sql_mode = ' modes ';
Setting the value of a global variable requires super permission, which is set after all client-connected operations are applied to it.
Setting the session variable applies only to the current client, and each client can change its sessionsql mode at any time.
Querying SQL Mode
To determine which SQL mode is currently in use, use the following statement to query
SELECT @ @GLOBAL. Sql_mode;
SELECT @ @SESSION. Sql_mode;
Primary SQL Mode
The main Sql_mode values are the following:
- Ansi
This is a combination pattern, it's like syntax and behavior more consistent with the standard SQL
- Strict_trans_tables
Strict mode for the transaction table. In this mode, if a value cannot be inserted into the transaction table, the statement is terminated. For a non-transactional table, the statement is terminated if the value that cannot be inserted occurs in the first line of a single-line statement or multiline statement.
- Traditional
Traditional mode, which is also a combination mode. In this mode, when an incorrect value is inserted, an error is given instead of a warning. (In a non-transactional storage engine, this is probably not what we want because the statement breaks when an error occurs, but the data modifications made before the error occur cannot be rolled back, causing a partial update.) ）
Full list of SQL schemas
The SQL schema can be broadly divided into the following categories
Strict mode (including Strict_all_tables and Strict_trans_tables)
- Strict_all_tables
For all storage engines, strict mode is enabled, and invalid values are rejected.
- Strict_trans_tables
Enable strict mode for the transactional storage engine, and enable strict mode for the fly transaction store engine where possible.
In MySQL5.7.4 to MySQL5.7.7, strict patterns include the effects of error_for_division_by_zero,no_zero_date and no_zero_in_date.
Used to limit the value of 0, used in conjunction with strict mode.
The
- no_zero_date
Affects whether the database allows ' 0000-00-00 ' as a valid date. The effect also depends on whether strict mode is enabled
If the mode is enabled, the ' 0000-00-00 ' value is allowed and the insert does not produce a warning
If the mode is disabled, the ' 0000-00-00 ' value is allowed but the insert generates a warning
if the mode and strict mode are enabled simultaneously, ' 0000-00-00 ' is not allowed to insert and produce an error
- no_zero_in_date
affects whether the database is allowed in a year other than 0 o'clock, month or date 0, unless ignore is also given. The effect also depends on whether strict mode is enabled.
If this mode is enabled, allow 0 date values to be included and insert does not produce a warning.
If this mode is disabled, allow 0 date values to be included but insert generates a warning.
If the pattern is used in conjunction with strict mode, inserting a date value that contains 0 is not allowed and inserts produce an error unless ignore is given at the same time. For insert IGNORE and update IGNORE, a date value that contains 0 is inserted as ' 0000-00-00 ' and generates a warning
- Error_for_division_by_zero
Affects whether the database allows 0 as a divisor, including mod (n,0). The effect also depends on whether strict mode is enabled
If this mode is enabled, a divisor of 0 is allowed and the insert does not produce a warning
If the mode is disabled, 0 is allowed to divide but the insertion generates a warning
if the mode and strict mode are used simultaneously, unless ignore is given simultaneously, Otherwise, a divisor of 0 is not allowed and the insertion generates an error. For insert IGNORE and update IGNORE, a divisor of 0 inserts null and generates a warning.
In previous versions of MySQL5.7.4, the above three schemas were deprecated
In MySQL5.7.4 to MySQL5.7.7, the above three modes do not work and their effects are included in strict mode.
In MySQL5.7.8 and later versions, the above three models have their own individual role, not part of the strict pattern. However, they should be used with strict mode, and they are all turned on by default. If you use strict mode and do not use the above pattern, a warning is generated if you use any of the above patterns but do not enable strict mode.
Since the above three schemas are deprecated, they will be deleted as a separate schema name in subsequent MySQL versions, and their effects will be included in strict mode.
Used to describe the function of a symbol.
- Ansi_quotes
Use "as an identifier ( 相同）而不是作为字符串的引用符号。在启用此模式的情况下，仍然可以使用 as a reference identifier, but you cannot refer to a text string by using double quotation marks.)
- Pipes_as_concat
Will | | As a string join operator (same as concat ()), not as a synonym for or
- Real_as_float
Use real as a synonym for float. By default, MySQL treats real as a synonym for double.
- No_backslash_escapes
Disables the backslash character () as an escape character in a string. When this mode is enabled, the backslash becomes a normal character. Affecting the way or result of a statement.
No_unsigned_subtraction
For subtraction between integers, if the type of a value is unsigned, the result of an unsigned integer is generated by default, but an error occurs if the result is a negative number
If No_unsigned_subtraction is enabled, the result is negative without an error
Ignore_space
In the function name and (middle allow spaces.) This causes the built-in function names to be treated as reserved words. Therefore, identifiers that are the same as the function names must be referenced.
For example, because the count () function exists, using count directly as the table name can produce an error
Mysql> CREATE TABLE count (i INT);
Error 1064 (42000): You have a error in your SQL syntax
The table name should be referenced:
Mysql> CREATE TABLE count (i INT);
Query OK, 0 rows Affected (0.00 sec)
- High_not_precedence
In MySQL5.7, not a between B and C are evaluated in the order of, not (a between B and C)
When High_not_precedence is enabled, the order is changed to (not a) between B and C
That is used to limit the output of the show CREATE table statement.
- No_field_options
Specific MySQL column options are not displayed in the output of show CREATE table
- No_key_options
Do not display specific and MySQL index options in the output of show CREATE table
- No_table_options
Specific table options with MySQL are not displayed in the output of show Cretae table
That affect the behavior of the database.
- No_auto_create_user
If you do not specify authentication information, the grant statement does not automatically create the user. The GRANT statement must use the identified by statement to specify a non-empty password or use the identified with statement to specify the authentication plug-in.
We recommend that you use the Create USER statement for creating users
- No_auto_value_on_zero
No_auto_value_on_zero affects the processing of the auto-grow column. In general, the next sequence number is generated by inserting null or by N. No_auto_value_on_zero allows you to insert a value of 0 in the autogrow column so that only null is inserted to generate the next sequence number.
- No_engine_substitution
When a statement, such as CREATE TABLE or ALTER TABLE, specifies a disabled or not compiled storage engine, it is automatically replaced with the default storage engine. When No_engine_substitution is not enabled. If the specified storage engine is not available, for CREATE TABLE, the default storage engine is used and a warning is generated, and for ALTER TABLE, a warning is generated and the table is not modified.
When No_engine_substitution is enabled, if the specified storage engine is not available, either creating the table or modifying the table will result in an error.
- Pad_char_to_full_length
By default, spaces at the end of the char column are automatically deleted at query time. When Pad_char_to_full_length is enabled, no spaces are removed, and the queried CHAR value is fully completed to the full column length.
- No_dir_in_create
When you create a table, all index directory and data directory directives are ignored. This option is useful for copying from a library.
- Only_full_group_by
Columns that are not named in the GROUP BY clause or are not uniquely determined by the GROUP BY clause cannot be included in the Select having or order by list
Please refer to http://www.ywnds.com/?p=8184
- Allow_invalid_dates
The invalid date is allowed, only the month between 1-12 and the date is 1-31, and the date is not fully checked. This pattern applies only to date and datetime columns. In the case where strict mode is disabled, invalid dates such as "2018-02-31" are converted to ' 0000-00-00 ' and produce a warning that an invalid date would produce an error if strict mode is enabled.
Combination of SQL schemas
The following pattern is an abbreviation for a partial combination of the above SQL schema complete list
| name |
full list |
| Ansi |
Real_as_float, Pipes_as_concat, Ansi_quotes, Ignore_space, and (in MySQL 5.7.5) only_full_group_by |
| DB2 |
Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, no_field_options |
| MSSQL |
Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, no_field_options |
| POSTGRESQL |
Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, no_field_options |
| ORACLE |
Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options, NO_AUTO_CREATE_USER |
| MAXDB |
Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options, NO_AUTO_CREATE_USER |
| Traditional |
Strict_trans_tables, Strict_all_tables, No_zero_in_date, No_zero_date, Error_for_division_by_zero, NO_AUTO_CREATE_ USER, No_engine_substitution |
MYSQL SQL Mode (not completed)
