The MySQL server can run in different
SQL modes, and these modes can be applied in different ways for different clients, depending on the value of the sql_mode system variable. DBA can set the global SQL Mode according to the server requirements, and each application can set the session-level SQL Mode according to its own needs.
SQL Mode will affect the SQL syntax and data verification supported by MySQL. This makes it easier to use MySQL in different environments and use MySQL with other database servers.
Three ways to set SQL Mode:
1. Add parameters when the server starts: --sql-mode="modes"
2. Add in My.ini configuration file: sql-mode="modes"
3. SET [GLOBAL|SESSION] sql_mode ='modes';
View the current
SQL Mode:
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
Important reminder:
SQL mode and user-defined partitions. After creating data and inserting the data into a partitioned table, changing the server SQL mode may cause significant changes to the behavior of such tables and may cause data loss or damage. It is strongly recommended that you never change the SQL mode after creating a table that uses user-defined partitions.
When copying the partition table, different SQL modes on the master and slave servers can also cause problems. For best results, always use the same server SQL mode on the master and slave servers.
SQL Modes list:
1. ONLY_FULL_GROUP_BY
For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, then this SQL is illegal because the column is not in the GROUP BY clause.
2. NO_AUTO_VALUE_ON_ZERO
This value affects the insertion of self-increasing columns. In the default setting, insert 0 or NULL to generate the next self-increasing value. This option is useful if the user wants to insert a value of 0 and the column grows again.
3. STRICT_TRANS_TABLES
In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted, and there is no restriction on the non-transaction table
4. NO_ZERO_IN_DATE
In strict mode, the date and month are not allowed to be zero
5. NO_ZERO_DATE
Set this value, mysql database does not allow the insertion of zero date, inserting a zero date will throw an error instead of a warning.
6. ERROR_FOR_DIVISION_BY_ZERO
During INSERT or UPDATE, if the data is divided by zero, an error is generated instead of a warning. If the mode is not given, MySQL returns NULL when the data is divided by zero
7. NO_AUTO_CREATE_USER
Prevent GRANT from creating users with empty passwords
8. NO_ENGINE_SUBSTITUTION
If the required storage engine is disabled or not compiled, an error is thrown. When this value is not set, the default storage engine is used instead, and an exception is thrown
9. PIPES_AS_CONCAT
Treat "||" as a concatenation operator rather than an OR operator, which is the same as the Oracle database, and is similar to the concatenation function Concat
10. ANSI_QUOTES
When ANSI_QUOTES is enabled, you cannot quote strings with double quotes because it is interpreted as an identifier
11. ALLOW_INVALID_DATES
No comprehensive inspection of the date, only the range of months 1-12 and dates 1-31
12. IGNORE_SPACE
Allow spaces between function names and (characters.
13. NO_BACKSLASH_ESCAPES
The use of the backslash character (\) as an escape character in strings and identifiers is prohibited
14. PAD_CHAR_TO_FULL_LENGTH
By default, terminal spaces are removed from CHAR column values during retrieval. If this mode is enabled, it will not be deleted, and the retrieved CHAR value will be filled to its full length. This mode does not apply to VARCHAR columns, and the end space is still retained during retrieval.
15. REAL_AS_FLOAT
Treat REAL as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE.
16. STRICT_ALL_TABLES
Enable strict SQL mode for all storage engines. Invalid data value was rejected.
Combining SQLMode
1. ANSI
Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE and (as of MySQL 5.7.5) ONLY_FULL_GROUP_BY. combined mode.
This mode makes the syntax and behavior more compliant with standard SQL.
2. TRADITIONAL
Before MySQL 5.7.4, and in MySQL 5.7.8 and later:
Equivalent to 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.
From MySQL 5.7.4 to 5.7.7 version:
Equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION.
Strict mode will give an error instead of a warning for inserting incorrect values. Can be applied to transaction tables and non-transaction tables. When used in transaction tables, errors will be rolled back immediately
3. DB2
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.
4. MAXDB
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.
5. MSSQL
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE,
NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.
6. ORACLE
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.
7. POSTGRESQL
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.
