Shulou(Shulou.com)06/01 Report--

This article will explain in detail about the use of SQL_MODE in MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

SQL_MODE is a system variable (variable) in MySQL that can consist of multiple MODE, each of which controls a behavior, such as whether a divisor of 0 is allowed and whether a value of '0000-00-00' is allowed in the date.

First, look at three simple Demo (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 | +-+ | 0-00-0000: 00:00 | +-+ row in set (0000 sec)

The actual stored value does not match the inserted value.

two。

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.01sec) Records: 3 Duplicates: 0 Warnings: 0mysql > select * from t2t2flirty words + | C1 | +-+ | a | b | | c | +-+ rows in set (0.00 sec) mysql > alter table T2 modify column c1 int Query OK, 3 rows affected, 3 warnings (0.05sec) Records: 3 Duplicates: 0 Warnings: 3mysql > 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 | + -+-+-- + rows in set (0.00 sec) mysql > select * from T2 +-+ | C1 | +-+ | 0 | 0 | 0 | 0 | +-+ rows in set (0.00 sec)

DDL caused the original column to be lost.

3.

Mysql > create table T3 (id int not null,c1 varchar (10)); Query OK, 0 rows affected (0.05sec) mysql > insert into T3 values (null,'a'); ERROR 1048 (23000): Column 'id' cannot be nullmysql > 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 | +-+ row in set (0.00 sec) mysql > select * from T3 +-+-+ | id | C1 | +-+-+ | 0 | a | +-+-+ row in set (0.00 sec)

The processing logic for explicitly specifying a column is not the same as that for not explicitly specifying it.

What causes it? This is related to SQL_MODE.

In MySQL 5.6, the default value for SQL_MODE is "NO_ENGINE_SUBSTITUTION", which is not strict mode.

In this mode, when making a data change operation, if there are invalid values in the column involved (such as date does not exist, data type is wrong, data overflow), it will only prompt "Warning" and will not report an error.

If you want to avoid the above problems, you need to turn on the strict mode of SQL_MODE.

The strict mode of SQL_MODE

The so-called strict mode is that STRICT_ALL_TABLES or STRICT_TRANS_TAB LES is turned on in SQL_MODE.

Or the Demo above, look at the processing logic of MySQL 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 1mysql > alter table T2 modify column C1 int;ERROR 1366 (HY000): Incorrect integer value:'a 'for column' C1 at row 1mysql > insert into T3 (C1) values ('a'); ERROR 1364 (HY000): Field 'id' doesn't have a default value

The same SQL, in strict mode, directly prompts "ERROR" instead of "Warning".

Also in strict mode, let's take a look at the difference between STRICT_ALL_TABLES or STRICT_TRAN S_TABLES.

The difference between STRICT_ALL_TABLES and STRICT_TRANS_TABLES

STRICT_TRANS_TABLES only enables strict mode for transaction tables, while STRICT_ALL_TABLES enables strict mode for all tables, not only transactional tables, but also non-transactional tables.

Look at the following test.

Insert 3 pieces of data into the myisam table, of which 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: 1mysql > show warnings +-+ | Level | Code | Message | +- -+ | Warning | 1366 | Incorrect integer value: 'for column' C1'at row 3 | +- -+ row in set (0.00 sec) mysql > select * from t +-+ | C1 | +-+ | 1 | | 2 | | 0 | +-+ 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, in the case where the table is myisam storage engine, only when STRICT_ALL_TABLES is enabled will an error be reported.

Different versions of default SQL_MODE

MySQL 5.5Null

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_pISION_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_pISION_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. You can specify that multiple MODE,MODE be separated by commas.

Global level

Set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES'

Session level

Complete list of set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';SQL_MODE

ALLOW_INVALID_DATES

In strict mode, the detection of the date is more strict, it must be effective. If the MODE is turned on, the detection of month and day will be relatively relaxed. 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 1mysql > 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 | +-+ | 0-02-31 00:00:00 | +-+ row in set (0.00 sec)

Note that this MODE applies only 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 must, you must escape 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 1mysql > create table `order` (id int); Query OK, 0 rows affected (0.12 sec)

If the MODE is turned on, double quotes, like backquotes, can escape keywords and reserved words.

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 1mysql > 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 is important to note that when the MODE is turned on, the string can no longer be quoted in double quotes.

ERROR_FOR_pISION_BY_ZERO

The MODE determines the processing logic with a divisor of 0, and the actual effect also depends on whether strict mode is turned on.

1. Turn on strict mode, turn on the MODE, insert 1Universe 0, and the 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 sec) mysql > insert into t values (1 sec 0); ERROR 1365 (22012): Division by 0

two。 Only strict mode is enabled, the MODE is not enabled, the insertion of 1 warning,1/0 0 is allowed, and there is no hint that the warning,1/0 will eventually be converted to NULL.

Mysql > set session sql_mode='STRICT_TRANS_TABLES';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > insert into t values (1 sec 0); Query OK, 1 row affected (0.07 sec) mysql > select * from t + row in set + | C1 | +-+ | NULL | +-+ row in set (0.00 sec)

3. Strict mode is not turned on, only the MODE is enabled, allowing the insertion of 1max 0, but prompting warning.

4. The strict mode is not enabled, and the MODE is not enabled, allowing the insertion of 1 warning 0, and does not prompt for the same as 2.

HIGH_NOT_PRECEDENCE

By default, NOT takes precedence over the comparison operator. However, in some lower versions, NOT takes precedence over the comparison operator.

Look at the difference between the two.

Mysql > set session sql_mode='';Query OK, 0 rows affected (0.00 sec) mysql > select not 1

< -1;+------------+| not 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 |+------------+row in set (0.00 sec) 在sql_mode为空的情况下， not 1 < -1相当于not (1 < -1)，如果设置了'HIGH_ NOT_PRECEDENCE'，则相当于(not 1) < -1。 IGNORE_SPACE 默认情况下，函数名和左括号（"("）之间不允许存在空格。若开启该MODE，则允许。 mysql>

Set session sql_mode='';Query OK, 0 rows affected (0.00 sec) mysql > select count (*) from twittering select count + | count (*) | +-+ | 2 | +-+ row in set (0.00 sec) mysql > select count (*) from tmistor 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 1mysql > set session sql_mode='IGNORE_SPACE';Query OK, 0 rows affected (0.01sec) mysql > select count (*) from twittering sec + | count (*) | +-+ | 2 | +-+ row in set (0.01 sec)

NO_AUTO_VALUE_ON_ZERO

By default, when you insert NULL or 0 on a self-incrementing primary key, the next value is automatically generated. If the MODE is enabled, the next value is not automatically generated when 0 is inserted.

If there is a value of 0 in the self-increasing primary key column of the table, it may result in data inconsistency during a logical backup and restore. So mysqldump automatically opens the MODE before generating backup data to avoid data inconsistencies.

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. 04sec) mysql > select * from Twitt Musashi + | id | +-- + | 1 | +-- + row in set (0 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 tpolitics Musashi + | id | +-+ | 0 | 1 | +-+ rows in set (0.00 sec)

NO_BACKSLASH_ESCAPES

By default, the backslash "\" is used as an escape character, and if the MODE is turned on, the backslash "\" is used as a normal character, not an escape character.

Mysql > set session sql_mode='';Query OK, 0 rows affected (0.01mm sec) mysql > select'\\ t | +-+ |\ t | +-+ |\ t | +-+ row in set (0.00 sec) mysql > set session sql_mode='NO_BACKSLASH_ESCAPES';Query OK, 0 rows affected (0.00 sec) mysql > select'\\ tconversation + |\ t | +-+ |\\ t | +-+ row in set (0.00 sec)

NO_DIR_IN_CREATE

By default, when you create a table, you can specify the data directory (DATA DIRECTORY) and the index directory (INDEX DIRECTORY). If the MODE is turned on, these two options are ignored. In the master-slave replication scenario, you can open the MODE on the slave library.

Mysql > set session sql_mode='';Query OK, 0 rows affected (0.01sec) mysql > create table t (id int) data directory'/ tmp/' Query OK 0 rows affected (0.15 sec) mysql > show create table t\ gateway * 1. Row * * Table: tCreate Table: CREATE TABLE `t` (`id` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='/tmp/'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 tincture 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.05sec) mysql > show create table t\ Graph * 1. Row * * Table: tCreate Table: CREATE TABLE `t` (`id` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_cirow in set (0.00 sec)

NO_ENGINE_SUBSTITUTION

When the MODE is turned on, when creating a table, if the specified storage engine does not exist or does not support it, it will directly prompt "ERROR".

If not, only "Warning" will be prompted and the default storage engine will be used.

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' | +-+ rows in set ( 0.00 sec) mysql > show create table t\ gateway * 1. Row * * Table: tCreate Table: CREATE TABLE `t` (`id` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_cirow 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

Two integers are subtracted, and if one of the numbers is unsigned, a value without symbolic bits is produced by default, and if the value is negative, it prompts "ERROR". Such as

Mysql > set session sql_mode='';Query OK, 0 rows affected (0.00 sec) mysql > select cast (0 as unsigned)-1 position error 1690 (22003): BIGINT UNSIGNED value is out of range in'(cast (0 as unsigned)-1)'

If the MODE is enabled, the result is allowed to be negative.

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 | +-+ row in set (0.00 sec)

NO_ZERO_DATE

The MODE will affect the insertion of '0000-0000'. The actual effect also depends on whether strict mode is turned on.

1. When strict mode is turned on and the MODE is turned on at the same time, the '0000-00' insertion is not allowed.

Mysql > set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE' Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > show warnings\ row * 1. Row * * Level: Warning Code: 3135Message: '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 ina future release.1 row in set (0.00 sec) mysql > insert into t values ('0000-00'); ERROR 1292 (22007): Incorrect datetime value: '0000-00-00' for column' c1' at row 1

two。 Only strict mode is enabled, the MODE is not enabled, the value of '0000-0000' is allowed to be inserted, and the 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. Strict mode is not enabled, only the MODE is enabled, allowing the insertion of the value of '0000-0000', but prompting 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 | +-+ row in set (sec)

4. Strict mode is not enabled, and the MODE is not enabled, the value of '0000-0000' is allowed to be inserted, and warning is not prompted.

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

Similar to NO_ZERO_DATE, except that NO_ZERO_DATE is for '0000-00-00', while NO_ZERO_IN_DATE is for a date on which the year is not zero, but the month or day is 0, for example, '2010-00-01' or '2010-01-00'.

The actual effect also depends on whether strict mode is turned on, just like NO_ZERO_DATE.

ONLY_FULL_GROUP_BY

When the MODE is turned on, only grouping columns and aggregate 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 | | d005 | 10001 | 1985-01 | | d006 | 10009 | 1985-01-01 | | d007 | 10002 | 1985-01 | | d008 | 10007 | 1985-01-01 | | d009 | 10011 | 1985-01-01 | +- + 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 the MODE is not enabled, arbitrary columns are allowed in the SELECT list, but the values of these columns are uncertain, as mentioned in the official documentation.

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 storing CHAR fields, fixed-length bytes are consumed in Compact format.

The C1 column below is defined as char (10). Although 'ab' takes up only two bytes, it takes up 10 bytes in Compact format, with blanks to fill in.

When querying, the trailing space is removed by default. If the MODE is enabled, it will not be removed 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 | +-- -+ 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 | +-+ row in set (0.00 sec)

PIPES_AS_CONCAT

In Oracle, concat and pipe characters ("| |") are available for connection strings, but concat can only concatenate two strings (concat in MySQL can concatenate multiple characters), which is too limited. If you want to connect multiple strings, you usually use pipe characters.

By opening the MODE, you can use the pipe character as the connector.

Mysql > set session sql_mode='';Query OK, 0 rows affected (0.00 sec) mysql > select 'a' | |'b' |'b' | +-+ | 0 | +-+ row in set, 2 warnings (0.00 sec) mysql > select concat ('axiombinomy') +-+ | concat ('aplomb') | +-+ | ab | +-+ 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 | +-+ row in set (0.00 sec)

REAL_AS_FLOAT

When creating a table, the data type can be specified as real, which is converted to double by default, and to float if the MODE is turned on.

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\ Graph * 1. Row * * Table: tCreate Table: CREATE TABLE `t` (`c1` double DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_cirow 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\ gateway * 1. Row * * Table: tCreate Table: CREATE TABLE `t` (`c1` float DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_cirow in set (0.00 sec)

STRICT_ALL_TABLES

Turn on strict mode for the transaction table.

STRICT_TRANS_TABLES

Turn on strict mode for all tables.

TIME_TRUNCATE_FRACTIONAL

If 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, truncate will be dropped 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; Query OK, 1 row affected (0.06 sec) mysql > select * from t +-+-+ | C1 | c2 | +-+-+ | 1 | 2018-08-08 11 12 purse 13.13 | +-+- -+ 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; Query OK, 1 row affected (0.06 sec) mysql > select * from t +-+-+ | C1 | c2 | +-+-+ | 1 | 2018-08-08 11 11 12 purge 13.13 | | 2 | 2018-08-08 11 12 purse 13.12 | +-+- -+ 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 'U1transactions%' identified by '123 transactions political 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. | +- -- + row in set (0.00 sec) mysql > select host User from mysql.user where user='u1' +-+-+ | host | user | +-+-+ |% | U1 | +-+-+ row in set (0.00 sec)

The same grant statement will report an error in MySQL 8.0.

Mysql > grant all on *. * to 'u1customers% 'identified by' 123 employees% 'identified by' 123 investors' 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 employees'at line 1

In MySQL 8.0, the grant statement is no longer allowed to create users implicitly, so the MODE does not exist in 8.0 either.

Literally, the MODE forbids authorization when implicitly creating a user. However, in the actual testing process, it is found that 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 subscription% 'identified by' 123 transactions political query OK, 0 rows affected, 1 warning (0. 00 sec)

In fact, the MODE only forbids grant statements without a "identified by" clause, but it does not prohibit grant statements with "identified by" clauses.

Mysql > drop user u1bot 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 challenge percent error error 1133 (42000): Can't find any matching row in the user tablemysql > set session sql_mode='';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > grant all on *. * to'u1 percent error%' Query OK, 0 rows affected, 1 warning (0.00 sec)

Common combinations of SQL_MODE

In MySQL 5.7you can also set SQL_MODE to ANSI, DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, TRADITIONAL.

In fact, these MODE are just a combination of the above MODE, designed to be compatible with other databases.

In MySQL 8.0, only two combinations of ANSI and TRADITIONAL are supported.

ANSI

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 | +-+-+ row in set (0.03 sec)

TRADITIONAL

Equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_pISION_BY_ZERO, NO_ENGINE_SUBSTITUTION.

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 | +-+- -+ row in set (0.01sec)

Summary

1. SQL_MODE will produce a lot of unexpected results in non-strict mode. It is recommended to turn on strict mode online. But for the old online environment, if you run in a non-strict mode from the beginning, do not adjust directly, after all, the difference between the two is still quite large.

two。 The official default SQL_MODE is changing all the time. MySQL 5.5,5.6,5.7 is different, but it is generally strict. When upgrading the database, it must consider whether the default SQL_MODE needs to be adjusted.

3. When you migrate a database, you can adjust SQL_MODE to be compatible with the syntax of other databases.

So far, we have a basic understanding of SQL_MODE in MySQL, but we still need to consolidate and practice how to use it. If you want to know more about it, please pay attention to the industry information.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.