SQL MODE is a system variable in MySQL, which can be composed of multiple modes. Each MODE controls a behavior, such as whether to allow the divisor to be 0, and whether to allow the value of '0000-00-00' in the date.
Why should we pay attention to SQL mode?
First, look at three simple demos (MySQL 5.6).
1.
mysql> create table t1(c1 datetime); Query OK, 0 rows affected (0.16 sec) mysql> insert into t1 values('2019-02-29'); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t1; +---------------------+ | c1 | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row in set (0.00 sec)
The actual stored value does not match the inserted value.
2.
mysql> create table t2(c1 varchar(10)); Query OK, 0 rows affected (0.06 sec) mysql> insert into t2 values('a'),('b'),('c'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t2; +------+ | c1 | +------+ | a | | b | | c | +------+ 3 rows in set (0.00 sec) mysql> alter table t2 modify column c1 int; Query OK, 3 rows affected, 3 warnings (0.05 sec) Records: 3 Duplicates: 0 Warnings: 3 mysql> show warnings; +---------+------+-------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------+ | Warning | 1366 | Incorrect integer value: 'a' for column 'c1' at row 1 | | Warning | 1366 | Incorrect integer value: 'b' for column 'c1' at row 2 | | Warning | 1366 | Incorrect integer value: 'c' for column 'c1' at row 3 | +---------+------+-------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select * from t2; +------+ | c1 | +------+ | 0 | | 0 | | 0 | +------+ 3 rows in set (0.00 sec)
DDL causes the original column to be lost.
3.
mysql> create table t3(id int not null,c1 varchar(10)); Query OK, 0 rows affected (0.05 sec) mysql> insert into t3 values(null,'a'); ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into t3(c1) values('a'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1364 | Field 'id' doesn't have a default value | +---------+------+-----------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t3; +----+------+ | id | c1 | +----+------+ | 0 | a | +----+------+ 1 row in set (0.00 sec)
The processing logic of explicitly specifying columns and not explicitly specifying columns is different.
Why is that? This is related to SQL mode.
In MySQL 5.6, the default value of SQL mode is no engine subscription, which is not strict mode.
In this mode, when performing data change operations, if there are invalid values in the involved columns (such as date does not exist, data type is wrong, data overflow), only "Warning" will be prompted, and no error will be reported.
If you want to avoid the above problems, you need to turn on the strict mode of SQL mode.
Strict mode of SQL mode
The so-called strict mode is that strict all tables or strict trans tables are enabled in SQL mode.
In the Demo above, look at the MySQL processing logic in strict mode.
mysql> set session sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values('2019-02-29'); ERROR 1292 (22007): Incorrect datetime value: '2019-02-29' for column 'c1' at row 1 mysql> alter table t2 modify column c1 int; ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'c1' at row 1 mysql> insert into t3(c1) values('a'); ERROR 1364 (HY000): Field 'id' doesn't have a default value
The same SQL, in strict mode, prompts "ERROR" instead of "Warning".
The same as strict mode, let's see the difference between strict table or strict tran s table.
Difference between strict all tables and strict trans tables
Strict trans tables enables strict mode only for transaction tables. Strict all tables enables strict mode for all tables, not only transaction tables, but also non transaction tables.
Look at the following test.
Insert 3 pieces of data into the myisam table, where the third piece of data is an empty string and does not match the defined int type.
mysql> create table t (c1 int) engine=myisam; Query OK, 0 rows affected (0.00 sec) mysql> set session sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values (1),(2),(''); Query OK, 3 rows affected, 1 warning (0.00 sec) Records: 3 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------+ | Warning | 1366 | Incorrect integer value: '' for column 'c1' at row 3 | +---------+------+------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t; +------+ | c1 | +------+ | 1 | | 2 | | 0 | +------+ 3 rows in set (0.00 sec) mysql> set session sql_mode='STRICT_ALL_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values (1),(2),(''); ERROR 1366 (HY000): Incorrect integer value: '' for column 'c1' at row 3
As you can see, when the table is myisam storage engine, only when strict all tables is turned on will an error be reported.
Default SQL mode of different versions
MySQL 5.5: empty
MySQL 5.6: NO_ENGINE_SUBSTITUTION
MySQL 5.7: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
MySQL 8.0: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
How to modify SQL mode
SQL MODE can be modified both at the global level and at the session level. Multiple modes can be specified, separated by commas.
Global level
set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';
Session level
set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';
Full list of SQL mode
ALLOW_INVALID_DATES
In the strict MODE, the date detection is more strict, which must be effective. If this MODE is turned on, the detection of month and day will be relatively loose. Among them, month only needs to be between 1 and 12, and day only needs to be between 1 and 31, regardless of whether it is valid or not, such as' 2004-02-31 'below.
mysql> create table t (c1 datetime); Query OK, 0 rows affected (0.21 sec) mysql> set session sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values('2004-02-31'); ERROR 1292 (22007): Incorrect datetime value: '2004-02-31' for column 'c1' at row 1 mysql> set session sql_mode='STRICT_TRANS_TABLES,ALLOW_INVALID_DATES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values('2004-02-31'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +---------------------+ | c1 | +---------------------+ | 2004-02-31 00:00:00 | +---------------------+ 1 row in set (0.00 sec)
Note that this MODE only applies to DATE and DATETIME, not to TIMESTAMP.
ANSI_QUOTES
In MySQL, keywords and reserved words are not allowed to be used as table and field names. If you have to use it, you must escape it using backquotes (').
mysql> create table order (id int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (id int)' at line 1 mysql> create table `order` (id int); Query OK, 0 rows affected (0.12 sec)
If the MODE is turned on, the double quotation marks, like the back quotation marks, can escape the keyword and reserved word.
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> create table "order" (c1 int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"order" (c1 int)' at line 1 mysql> set session sql_mode='ANSI_QUOTES'; Query OK, 0 rows affected (0.00 sec) mysql> create table "order" (c1 int); Query OK, 0 rows affected (0.17 sec)
It should be noted that when this MODE is turned on, double quotation marks cannot be used to enclose strings.
ERROR_FOR_DIVISION_BY_ZERO
This MODE determines the processing logic with a divisor of 0. The actual effect also depends on whether strict MODE is enabled.
1. Turn on the strict MODE, turn on the MODE, insert 1 / 0, and an error will be reported directly.
mysql> create table t (c1 double); Query OK, 0 rows affected (0.04 sec) mysql> set session sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values(1/0); ERROR 1365 (22012): Division by 0
2. Only strict MODE is enabled, and the MODE is not enabled. 1 / 0 insertion is allowed, and warning is not prompted. 1 / 0 will be converted to NULL finally.
mysql> set session sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values(1/0); Query OK, 1 row affected (0.07 sec) mysql> select * from t; +------+ | c1 | +------+ | NULL | +------+ 1 row in set (0.00 sec)
3. Do not turn on the strict MODE, only turn on the MODE, allow 1 / 0 insertion, but prompt warning.
4. Do not turn on the strict MODE or the MODE, allow 1 / 0 insertion, and do not prompt warning, the same as 2.
HIGH_NOT_PRECEDENCE
By default, NOT takes precedence over the comparison operator. In some lower versions, however, NOT takes precedence over the comparison operator.
Look at the difference.
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select not 1 < -1; +------------+ | not 1 < -1 | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql> set session sql_mode='HIGH_NOT_PRECEDENCE'; Query OK, 0 rows affected (0.00 sec) mysql> select not 1 < -1; +------------+ | not 1 < -1 | +------------+ | 0 | +------------+ 1 row in set (0.00 sec)
When SQL mode is empty, not 1 < - 1 is equivalent to not (1 < - 1), and if 'high not precision' is set, it is equivalent to (not 1) < 1.
IGNORE_SPACE
By default, spaces are not allowed between function names and open parentheses ("("). If the MODE is turned on, it is allowed.
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from t; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) mysql> select count (*) from t; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from t' at line 1 mysql> set session sql_mode='IGNORE_SPACE'; Query OK, 0 rows affected (0.01 sec) mysql> select count (*) from t; +-----------+ | count (*) | +-----------+ | 2 | +-----------+ 1 row in set (0.01 sec)
NO_AUTO_VALUE_ON_ZERO
By default, when NULL or 0 is inserted into the auto increment primary key, the next value is automatically generated. If this MODE is turned on, when 0 is inserted, the next value will not be generated automatically.
If there is a value of 0 in the auto increment primary key column in the table, data inconsistency may occur during the logical backup restore. Therefore, mysqldump will automatically turn on this MODE before generating backup data to avoid data inconsistency.
mysql> create table t (id int auto_increment primary key); Query OK, 0 rows affected (0.11 sec) mysql> set session sql_mode=''; Query OK, 0 rows affected (0.01 sec) mysql> insert into t values (0); Query OK, 1 row affected (0.04 sec) mysql> select * from t; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> set session sql_mode='NO_AUTO_VALUE_ON_ZERO'; Query OK, 0 rows affected (0.02 sec) mysql> insert into t values (0); Query OK, 1 row affected (0.09 sec) mysql> select * from t; +----+ | id | +----+ | 0 | | 1 | +----+ 2 rows in set (0.00 sec)
NO_BACKSLASH_ESCAPES
By default, the backslash "\" will be used as an escape character. If the MODE is turned on, the backslash "\" will be used as a normal character instead of an escape character.
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.01 sec) mysql> select '\\t'; +----+ | \t | +----+ | \t | +----+ 1 row in set (0.00 sec) mysql> set session sql_mode='NO_BACKSLASH_ESCAPES'; Query OK, 0 rows affected (0.00 sec) mysql> select '\\t'; +-----+ | \\t | +-----+ | \\t | +-----+ 1 row in set (0.00 sec)
NO_DIR_IN_CREATE
By default, when creating a table, you can specify DATA DIRECTORY and INDEX DIRECTORY. If you enable this MODE, these two options will be ignored. In the master-slave copy scenario, you can turn on the MODE on the slave database.
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.01 sec) mysql> create table t (id int) data directory '/tmp/'; Query OK, 0 rows affected (0.15 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='/tmp/' 1 row in set (0.00 sec) mysql> set session sql_mode='NO_DIR_IN_CREATE'; Query OK, 0 rows affected (0.00 sec) mysql> drop table t; Query OK, 0 rows affected (0.11 sec) mysql> create table t (id int) data directory '/tmp/'; Query OK, 0 rows affected, 1 warning (0.05 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
NO_ENGINE_SUBSTITUTION
When this MODE is on, when creating a table, if the specified storage engine does not exist or does not support it, the system will directly prompt "ERROR".
If not, it will only prompt "Warning" and use the default storage engine.
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> create table t (id int) engine=federated; Query OK, 0 rows affected, 2 warnings (0.11 sec) mysql> show warnings; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1286 | Unknown storage engine 'federated' | | Warning | 1266 | Using storage engine InnoDB for table 't' | +---------+------+-------------------------------------------+ 2 rows in set (0.00 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) mysql> drop table t; Query OK, 0 rows affected (0.11 sec) mysql> set session sql_mode='NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) mysql> create table t (id int) engine=federated; ERROR 1286 (42000): Unknown storage engine 'federated'
NO_UNSIGNED_SUBTRACTION
If one of the two integers is an unsigned bit, a value of the unsigned bit will be generated by default. If the value is negative, an ERROR will be prompted. For example,
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select cast(0 as unsigned)-1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
If this MODE is turned on, a negative result is allowed.
mysql> set session sql_mode='NO_UNSIGNED_SUBTRACTION'; Query OK, 0 rows affected (0.00 sec) mysql> select cast(0 as unsigned)-1; +-----------------------+ | cast(0 as unsigned)-1 | +-----------------------+ | -1 | +-----------------------+ 1 row in set (0.00 sec)
NO_ZERO_DATE
This MODE affects the insertion of '0000-00-00'. The actual effect also depends on whether strict MODE is turned on.
1. It is not allowed to insert '0000-00-00' when the strict MODE is turned on and the MODE is turned on at the same time.
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 3135 Message: 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.1 row in set (0.00 sec) mysql> insert into t values ('0000-00-00'); ERROR 1292 (22007): Incorrect datetime value: '0000-00-00' for column 'c1' at row 1
2. Only the strict MODE is turned on, and this MODE is not turned on. Insertion of '0000-00-00' value is allowed, and warning is not prompted.
mysql> set session sql_mode='STRICT_TRANS_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values ('0000-00-00'); Query OK, 1 row affected (0.04 sec)
3. Do not turn on the strict MODE, only turn on the MODE, allow the insertion of '0000-00-00' value, but prompt warning.
mysql> set session sql_mode='NO_ZERO_DATE'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into t values ('0000-00-00'); Query OK, 1 row affected, 1 warning (0.05 sec) mysql> show warnings; +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1264 | Out of range value for column 'c1' at row 1 | +---------+------+---------------------------------------------+ 1 row in set (0.01 sec)
4. Do not turn on the strict MODE or the MODE, allow the insertion of '0000-00-00' value, and do not prompt warning.
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values ('0000-00-00'); Query OK, 1 row affected (0.03 sec)
NO_ZERO_IN_DATE
It is similar to no zero date, except that no zero date is for '0000-00-00', while no zero in date is for the date with year not 0, but month or day 0, such as' 2010-00-01 'or' 2010-01-00 '.
The actual effect also depends on whether the strict mode is turned on, the same as no zero date.
ONLY_FULL_GROUP_BY
When this MODE is turned on, only grouping columns and aggregation functions can appear in the SELECT list.
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select dept_no,emp_no,min(from_date) from dept_emp group by dept_no; +---------+--------+----------------+ | dept_no | emp_no | min(from_date) | +---------+--------+----------------+ | d001 | 10017 | 1985-01-01 | | d002 | 10042 | 1985-01-01 | | d003 | 10005 | 1985-01-01 | | d004 | 10003 | 1985-01-01 | | d005 | 10001 | 1985-01-01 | | d006 | 10009 | 1985-01-01 | | d007 | 10002 | 1985-01-01 | | d008 | 10007 | 1985-01-01 | | d009 | 10011 | 1985-01-01 | +---------+--------+----------------+ 9 rows in set (0.64 sec) mysql> set session sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> select dept_no,emp_no,min(from_date) from dept_emp group by dept_no; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.dept_emp.emp_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
If this MODE is not enabled, any columns in the SELECT list are allowed, but the values of these columns are not determined, which is also mentioned in the official documents.
If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses. Disabling ONLY_FULL_GROUP_BY is useful primarily when you know that, due to some property of the data, all values in each nonaggregated column not named in the GROUP BY are the same for each group. https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
PAD_CHAR_TO_FULL_LENGTH
When the CHAR field is stored, it will occupy a fixed length of bytes in Compact format.
For example, the c1 column below is defined as char(10). Although 'ab' only occupies two bytes, it will occupy 10 bytes in Compact format, and the insufficient part will be filled with spaces.
When querying, by default, spaces at the end are removed. If this MODE is turned on, it will not be eliminated, and fixed length characters will be returned each time.
mysql> create table t (c1 char(10)); Query OK, 0 rows affected (0.17 sec) mysql> insert into t values('ab'); Query OK, 1 row affected (0.11 sec) mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select c1, hex(c1), char_length(c1) from t; +------+---------+-----------------+ | c1 | hex(c1) | char_length(c1) | +------+---------+-----------------+ | ab | 6162 | 2 | +------+---------+-----------------+ 1 row in set (0.00 sec) mysql> set session sql_mode='PAD_CHAR_TO_FULL_LENGTH'; Query OK, 0 rows affected (0.00 sec) mysql> select c1, hex(c1), char_length(c1) from t; +------------+----------------------+-----------------+ | c1 | hex(c1) | char_length(c1) | +------------+----------------------+-----------------+ | ab | 61622020202020202020 | 10 | +------------+----------------------+-----------------+ 1 row in set (0.00 sec)
PIPES_AS_CONCAT
In Oracle, concat and pipe character ("|") can be used to connect strings, but concat can only connect two strings (concat in MySQL can connect multiple characters). The limitation is too large. If you want to connect multiple strings, pipe character is generally used.
Turn on this MODE to use the pipe character as the connector.
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select 'a'||'b'; +----------+ | 'a'||'b' | +----------+ | 0 | +----------+ 1 row in set, 2 warnings (0.00 sec) mysql> select concat('a','b'); +-----------------+ | concat('a','b') | +-----------------+ | ab | +-----------------+ 1 row in set (0.00 sec) mysql> set session sql_mode='PIPES_AS_CONCAT'; Query OK, 0 rows affected (0.00 sec) mysql> select 'a'||'b'; +----------+ | 'a'||'b' | +----------+ | ab | +----------+ 1 row in set (0.00 sec)
REAL_AS_FLOAT
When creating a table, the data type can be specified as real. By default, it will be converted to double. If the MODE is turned on, it will be converted to float.
mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> create table t ( c1 real); Query OK, 0 rows affected (0.12 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `c1` double DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> drop table t; Query OK, 0 rows affected (0.04 sec) mysql> set session sql_mode='REAL_AS_FLOAT'; Query OK, 0 rows affected (0.00 sec) mysql> create table t ( c1 real); Query OK, 0 rows affected (0.11 sec) mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `c1` float DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
STRICT_ALL_TABLES
Turn on strict mode for transaction tables.
STRICT_TRANS_TABLES
Turn on strict mode for all tables.
TIME_TRUNCATE_FRACTIONAL
When the time type defines decimal seconds, if the number of digits inserted is greater than the specified number of digits, it will be rounded by default. If the MODE is enabled, it will be truncate d directly.
mysql> create table t (c1 int,c2 datetime(2)); Query OK, 0 rows affected (0.04 sec) mysql> set session sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values(1,'2018-08-08 11:12:13.125'); Query OK, 1 row affected (0.06 sec) mysql> select * from t; +------+------------------------+ | c1 | c2 | +------+------------------------+ | 1 | 2018-08-08 11:12:13.13 | +------+------------------------+ 1 row in set (0.00 sec) mysql> set session sql_mode='TIME_TRUNCATE_FRACTIONAL'; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values(2,'2018-08-08 11:12:13.125'); Query OK, 1 row affected (0.06 sec) mysql> select * from t; +------+------------------------+ | c1 | c2 | +------+------------------------+ | 1 | 2018-08-08 11:12:13.13 | | 2 | 2018-08-08 11:12:13.12 | +------+------------------------+ 2 rows in set (0.00 sec)
NO_AUTO_CREATE_USER
Prior to MySQL 8.0, direct authorization implicitly created users.
mysql> select host,user from mysql.user where user='u1'; Empty set (0.00 sec) mysql> grant all on *.* to 'u1'@'%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.12 sec) mysql> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select host,user from mysql.user where user='u1'; +------+------+ | host | user | +------+------+ | % | u1 | +------+------+ 1 row in set (0.00 sec)
In the same grant statement, errors are reported in MySQL 8.0.
mysql> grant all on *.* to 'u1'@'%' identified by '123'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123'' at line 1
In MySQL 8.0, grant statement is not allowed to implicitly create users, so the MODE does not exist in 8.0.
Literally, this MODE prohibits the implicit creation of users when authorizing. But in the actual test process, it can not be prohibited.
mysql> set session sql_mode='NO_AUTO_CREATE_USER'; Query OK, 0 rows affected (0.03 sec) mysql> grant all on *.* to 'u1'@'%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.00 sec)
In fact, the MODE only prohibits grant statements without the "identified by" clause. For grant statements with the "identified by" clause, it does not prohibit.
mysql> drop user u1; Query OK, 0 rows affected (0.00 sec) mysql> set session sql_mode='NO_AUTO_CREATE_USER'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> grant all on *.* to 'u1'@'%'; ERROR 1133 (42000): Can't find any matching row in the user table mysql> set session sql_mode=''; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> grant all on *.* to 'u1'@'%'; Query OK, 0 rows affected, 1 warning (0.00 sec)
Common combinations of SQL mode
In MySQL 5.7, you can also set SQL? Mode to ANSI, DB2, maxdb, MSSQL, mysql323, mysql40, Oracle, PostgreSQL, transactional.
In fact, these modes are only a combination of the above modes for compatibility with other databases.
In MySQL 8.0, only two combinations of ANSI and transactional are supported.
ANSI
It is equivalent to real as float, pipes as concat, ANSI quotes, ignore space, only full group by.
mysql> set session sql_mode='ANSI'; Query OK, 0 rows affected (0.00 sec) mysql> show session variables like 'sql_mode'; +---------------+--------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------------------------------------------+ | sql_mode | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI | +---------------+--------------------------------------------------------------------------------+ 1 row in set (0.03 sec)
TRADITIONAL
It is equivalent to strict trans tables, strict all tables, no zero in date, no zero date, error for division by zero, no engine subsystem.
mysql> set session sql_mode='TRADITIONAL'; Query OK, 0 rows affected (0.00 sec) mysql> show session variables like 'sql_mode'; +---------------+----------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+----------------------------------------------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION | +---------------+----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
summary
1. In the non strict mode, there will be many unexpected results. It is recommended to turn on strict mode online. But for the old online environment, if it runs in a non strict mode at the beginning, it is forbidden to adjust it directly. After all, the difference between the two is quite huge.
2. The official default SQL mode is changing all the time. MySQL 5.5, 5.6 and 5.7 are different, but they are generally stricter. When upgrading the database, it must consider whether the default SQL mode needs to be adjusted.
3. During database migration, SQL mode can be adjusted to be compatible with the syntax of other databases.