How to set SQL_MODE in MySQL 5.7

sql_mode is a variable that is easily overlooked. The default value in 5.5 is null. Under this setting, some illegal operations can be allowed, such as allowing the insertion of some illegal data.

This value setting has been strengthened in 5.6, and 5.7 has paid more attention to security specifications. This value defaults to strict mode

1. sql_mode is used to solve the following types of problems

By setting sql mode, data verification with different stringency levels can be completed to effectively ensure data readiness.

By setting the sql mode to relaxed mode, we ensure that most sql conforms to the standard sql syntax. In this way, when the application is migrated between different databases, there is no need to make major modifications to the business sql, and it can be easily Conveniently migrate to the target database.

2. Description of the default value of the sql_mode parameter in MySQL5.7 (the following is the MySQL 5.7.27 version)

• ONLY_FULL_GROUP_BY

For SQL that uses GROUP BY for query, fields that do not appear in GROUP BY are not allowed to appear in the SELECT part. That is, the fields in the SELECT query must appear in GROUP BY or use aggregate functions or be Has unique properties.

create table test(name varchar(10),value int);
insert into test values ('a',1),('a',20),('b',23),('c',15),('c',30);
#默认情况是可能会写出无意义或错误的聚合语句：
SET sql_mode='';
select * from test group by name;
select value,sum(value) from test group by name;
# 使用该模式后，写法必须标准
SET sql_mode='ONLY_FULL_GROUP_BY';
select name,sum(value) from test group by name;
-- 错误写法则报错
select value,sum(value) from test group by name;
# 报错终止
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• STRICT_TRANS_TABLES

This option only works for transactional storage engines and is invalid for non-transactional storage engines. , its function is to enable strict SQL mode. In strict sql mode, if a field value that does not meet the requirements is inserted or updated in an INSERT or UPDATE statement, an error will be reported directly and the operation will be interrupted

create table test(value int(1));
SET sql_mode=''; #默认只要第一个值
 
insert into test(value) values('a'),(1); #不报错
insert into test(value) values(2),('a'); #不报错
select * from test;
+------------+
| value      |
+------------+
|          0 |
|          1 |
|          2 |
|          0 |
+------------+
#后面删除表不再说明！
drop table test; 
create table test(value int(1));
 
SET sql_mode='STRICT_TRANS_TABLES'; #每个值都判断
 
insert into test(value) values('a'),(1);
#报错，第一行'a'错误。
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'value' at row 1

• NO_ZERO_IN_DATE

The time field value inserted in MySQL does not allow the date and month to be zero

create table test(value date);
SET sql_mode='';
insert into test(value) values('2020-00-00'); #结果为 '2020-00-00'
 
SET sql_mode='NO_ZERO_IN_DATE';
insert into test(value) values('2021-00-00'); #不符合,转为 '0000-00-00'

• NO_ZERO_DATE

The time field value inserted in MySQL is not allowed to insert the ‘0000-00-00’ date

create table test(value date);
 
SET sql_mode='';
insert into test(value) values('0000-00-00'); #无警告 warning
 
SET sql_mode='STRICT_TRANS_TABLES';
insert into test(value) values('0000-00-00'); #无警告 warning
 
SET sql_mode='NO_ZERO_DATE';
insert into test(value) values('0000-00-00'); #有警告 warning
 
SET sql_mode='NO_ZERO_DATE,STRICT_TRANS_TABLES'
insert into test(value) values('0000-00-00');
# 报错终止
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'value' at row 1

• ##ERROR_FOR_DIVISION_BY_ZERO

In an INSERT or UPDATE statement, if the data is divided by 0, a warning (in non-strict sql mode) or an error (in strict sql mode) will appear.

• When this option is turned off, the number is divided by 0, resulting in NULL and no warning is generated

• When this option is turned on and is in non-strict In sql mode, if the number is divided by 0, NULL will be obtained but a warning will be generated

• When this option is turned on and in strict sql mode, the number is divided by 0, an error will be generated and the operation will be interrupted

create table test(value int);
 
SET sql_mode='';  
select 10/0;  #无警告 warning
insert into test(value) values(10/0);   #无警告 warning
 
SET sql_mode='STRICT_TRANS_TABLES'; 
select 10/0;   #无警告 warning
insert into test(value) values(10/0);  #无警告 warning
 
SET sql_mode='ERROR_FOR_DIVISION_BY_ZERO'; 
select 10/0;  #有警告 warning
insert into test(value) values(10/0);  #有警告 warning
 
SET sql_mode='ERROR_FOR_DIVISION_BY_ZERO,STRICT_TRANS_TABLES';
select 10/0; #有警告 warning
insert into test(value) values(10/0); 
#报错：ERROR 1365 (22012): Division by 0

• ##NO_AUTO_CREATE_USER
  ##Prohibit GRANT from creating users with empty passwords

SET sql_mode='';
grant all on test.* to test01@'localhost';  #不报错（无需要设置密码）
SET sql_mode='NO_AUTO_CREATE_USER';
# 报错
ERROR 1133 (42000): Can't find any matching row in the user table

#正确 写法，需要设置密码
grant all on test.* to test01@'localhost' identified by 'test01...';

NO_ENGINE_SUBSTITUTION

• #When using CREATE TABLE or ALTER TABLE syntax to execute the storage engine, if the set storage engine is disabled or not Compiling will produce an error.

# 查看当前支持的存储引擎
show engines;

set sql_mode='';
create table test(id int) ENGINE="test";
Query OK, 0 rows affected, 2 warnings (0.03 sec)

select table_name,engine from information_schema.tables where table_schema='test' and table_name='test'; # 转为默认存储引擎
+------------+--------+
| table_name | engine |
+------------+--------+
| test       | InnoDB |
+------------+--------+
SET sql_mode='NO_ENGINE_SUBSTITUTION';
create table test(id int) ENGINE=test;
# 报错
ERROR 1286 (42000): Unknown storage engine 'test'

3. sql_mode setting and modification

Method 1: This is a modifiable global variable

> show variables like '%sql_mode%';
> set @@sql_mode="NO_ENGINE_SUBSTITUTION"
> set session sql_mode='STRICT_TRANS_TABLES';

Method 2: By modifying the configuration file (requires restart to take effect)

# vim /etc/my.cnf
[mysqld]
......
sql_mode="NO_ENGINE_SUBSTITUTION"
......

The above is the detailed content of How to set SQL_MODE in MySQL 5.7. For more information, please follow other related articles on the PHP Chinese website!