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 中出现的或者使用聚合函数的或者是具有唯一属性的。
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,sumvalue from test group by name;
# 使用该模式后,写法必须标准
SET sql_mode='ONLY_FULL_GROUP_BY';
select name,sumvalue from test group by name;
-- 错误写法则报错
select value,sumvalue 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语句中,插入或者更新了某个不符合规定的字段值,则会直接报错中断操作
SET sql_mode=''; #默认只要第一个值
insert into testvalue values('a',1); #不报错
insert into testvalue values(2,'a'); #不报错
select * from test;
+------------+
| value |
+------------+
| 0 |
| 1 |
| 2 |
| 0 |
+------------+
#后面删除表不再说明!;
drop table test;
create table testvalue int1));
SET sql_mode='STRICT_TRANS_TABLES'; #每个值都判断
insert into testvalue values('a',1);
#报错,第一行'a'错误。
ERROR 1366 HY000: Incorrect integer value: 'a' for column 'value' at row 1
NO_ZERO_IN_DATE MySQL中插入的时间字段值,不允许日期和月份为零
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’ 日期
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除,产生错误且中断操作
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创建密码为空的用户
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语法执行存储引擎的时候,如果设定的存储引擎被禁用或者未编译,会产生错误。
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 设置和修改
方式一: 这是一个可修改全局变量
> set @@sql_mode="NO_ENGINE_SUBSTITUTION"
> set session sql_mode='STRICT_TRANS_TABLES';
方式二: 通过修改配置文件(需要重启生效)
[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
