MySQL 5.7之关于SQL_MODE的设置

sql_mode是个容易被忽视的变量，在5.5默认值是空值，在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。

在5.6中强化了该值设置，5.7中更注重了安全规范性，这个值默认为严格模式

一、sql_mode用来解决下面几类问题

通过设置sql mode,可以完成不同严格程度的数据校验，有效保障数据准备性。

通过设置sql mode 为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql进行较大的修改，可以很方便的迁移到目标数据库中。

二、MySQL5.7中sql_mode参数默认值的说明（如下为MySQL 5.7.27版本）

• ONLY_FULL_GROUP_BY

对于使用 GROUP BY 进行查询的SQL，不允许 SELECT 部分出现 GROUP BY 中未出现的字段，也就是 SELECT 查询的字段必须是 GROUP BY 中出现的或者使用聚合函数的或者是具有唯一属性的。

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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

该选项针对事务性存储引擎生效，对于非事务性存储引擎无效，该选项表示开启strict sql模式。在strict sql模式下，在INSERT或者UPDATE语句中，插入或者更新了某个不符合规定的字段值，则会直接报错中断操作

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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

MySQL中插入的时间字段值，不允许日期和月份为零

?

1 2 3 4 5 6

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

MySQL中插入的时间字段值，不允许插入 ‘0000-00-00’ 日期

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

INSERT或者UPDATE语句中，如果数据被0除，则出现警告（非strict sql模式下）或者错误(strict sql模式下)。

• 当该选项关闭时，数字被0除，得到NULL且不会产生警告
• 当该选项开启且处于非strict sql模式下，数字被0除，得到NULL但是会产生警告
• 当该选项开启且处于strict sql模式下，数字被0除，产生错误且中断操作

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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

禁止GRANT创建密码为空的用户

?

1 2 3 4 5 6 7 8

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

在使用CREATE TABLE或者ALTER TABLE语法执行存储引擎的时候，如果设定的存储引擎被禁用或者未编译，会产生错误。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

# 查看当前支持的存储引擎 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'

三、sql_mode 设置和修改

方式一： 这是一个可修改全局变量

?

1 2 3

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

方式二： 通过修改配置文件（需要重启生效）

?

1 2 3 4 5

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

总结

SQL_MODE在非严格模式下，会出现很多意料不到的结果。建议线上开启严格模式。但对于线上老的环境，如果一开始就运行在非严格模式下，切忌直接调整，毕竟两者的差异性还是相当巨大。

官方默认的SQL_MODE一直在发生变化，MySQL 5.5, 5.6, 5.7就不尽相同，但总体是趋严的，在对数据库进行升级时，其必须考虑默认的SQL_MODE是否需要调整。

在进行数据库迁移时，可通过调整SQL_MODE来兼容其它数据库的语法。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://blog.csdn.net/qq_25854057/article/details/114587257