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 中出现的或者使用聚合函数的或者是具有唯一属性的。
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语句中,插入或者更新了某个不符合规定的字段值,则会直接报错中断操作
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中插入的时间字段值,不允许日期和月份为零
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 日期
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除,产生错误且中断操作 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创建密码为空的用户
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
在使用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
方式一: 这是一个可修改全局变量
show variables like %sql_mode%set @@sql_mode= NO_ENGINE_SUBSTITUTION
set session sql_mode= STRICT_TRANS_TABLES
方式二: 通过修改配置文件(需要重启生效)
# 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来兼容其它数据库的语法。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 MySQL 5.7之关于SQL_MODE的设置
相关文章
- WAMP服务器安装MySQL设置指南(wamp设置mysql)
- MySQL数据库存储格式研究(mysql存储格式)
- MySQL连接IP:快速简单的设置方法(mysql连接ip)
- MySQL自增特性及其设置方法(mysql自增设置)
- MySQL中默认的约束条件及其使用(mysql默认约束)
- 妥善设置MySQL密码保护数据安全(如何给mysql设置密码)
- MySQL 系统变量调优:路径指向最佳性能(mysql系统变量设置)
- MySQL数据分析之透视表法(mysql数据透视)
- MySQL:调整参数以优化性能(mysql设置参数)
- MySQL教程视频,百度云盘上有你想要的!(mysql教程视频百度云)
- 如何在MySQL中设置最大连接池数?(mysql最大连接池数)
- MySQL字段的默认值设置技巧(mysql字段默认值)
- 如何在MySQL中修改SQL语句?(mysql修改sql语句)
- MySQL查询:使用时间戳搜索数据(mysql时间戳查询)
- 掌握MySQL关键字的字段使用技巧(mysql关键字字段)
- 学习如何在MySQL中建立组合索引,提升查询效率(mysql建立组合索引)
- 如何授予 MySQL 建库权限:一步步教你设置正确的权限控制(mysql建库权限)
- 解决MySQL数据库写入数据时的乱码问题(写入mysql数据库乱码)
- MySQL表空间高效配置的技巧(mysql 表空间设置)
- MySQL访问时慢,CS怎么办(c s mysql访问慢)
- 如何解决 MySQL 中的 2006 错误(mysql中2006)
- 千万级MySQL数据处理之路(1千万数据mysql)
- MySQL实例三表内连接案例(mysql三表内连接例子)
- 深入了解MySQL空间管理是必须的(mysql一般要空)
- MySQL视图提高数据分析效率的好帮手(mysql _视图)
- 使用SQL语句查询Redis数据库的操作方法(sql语句查询redis)
- MySQL视图整合两张表数据(mysql两张表视图)
- MySQL安装后的配置文件设置(mysql下载后配置文件)
- MySQL中批量删除指定前缀表的sql语句