MySQL的sql_mode
1. 介绍
sql_mode是MySQL定义的一个规则模式,它会影响MySQL支持的SQL语法以及它执行的数据校验检查,通过设置sql_mode,可以完成不同严格程度的数据校验,有效地保障数据的准确性。
MySQL服务器可以在不同SQL模式下运行,且可以针对不同的客户端以不同的方式应用这些模式,具体取决于sql_mode的值。
MySQL5.6和MySQL5.7默认的sql_mode模式参数是不一样的:
- 5.6的mode默认值为空(即:NO_ENGINE_SUBSTITUTION),表示一个空值,相当于没有设置,可以理解为宽松模式。在这种设置下可以允许一些非法操作,如非法数据的插入。
- 5.7的mode是STRICT_TRANS_TABLES,即为严格模式,用于进行数据的严格校验,错误数据不能插入,否则会报error,并且回滚事物。
2. 宽松模式 & 严格模式
宽松模式
如果设置的是宽松模式,那么我们在插入数据的时候,即便是给了一个错误的数据,也可能会被接受,并且不报错。
-
举例 :我在创建一个表时,该表中有一个字段为name,给name设置的字段类型时 char(10) ,如果我在插入数据的时候,其中name这个字段对应的有一条数据的 长度超过了10 ,例如'1234567890abc',超过了设定的字段长度10,那么不会报错,并且取前10个字符存上,也就是说你这个数据被存为了'1234567890',而'abc'就没有了。但是,我们给的这条数据是错误的,因为超过了字段长度,但是并没有报错,并且mysql自行处理并接受了,这就是宽松模式的效果。
-
应用场景 :通过设置sql mode为宽松模式,来保证大多数sql符合标准的sql语法,这样应用在不同数据库之间进行 迁移 时,则不需要对业务sql 进行较大的修改。
严格模式
出现上面宽松模式的错误,应该报错才对,所以MySQL5.7版本就将sql_mode默认值改为了严格模式。所以在 生产等环境 中,我们必须采用的是严格模式,进而 开发、测试环境 的数据库也必须要设置,这样在开发测试阶段就可以发现问题。并且我们即便是用的MySQL5.6,也应该自行将其改为严格模式。
-
开发经验 :MySQL等数据库总想把关于数据的所有操作都自己包揽下来,包括数据的校验,其实开发中,我们应该在自己 开发的项目程序级别将这些校验给做了 ,虽然写项目的时候麻烦了一些步骤,但是这样做之后,我们在进行数据库迁移或者在项目的迁移时,就会方便很多。
-
改为严格模式后可能会存在的问题:若设置模式中包含了 NO_ZERO_DATE ,那么MySQL数据库不允许插入零日期,插入零日期会抛出错误而不是警告。例如,表中含字段TIMESTAMP列(如果未声明为NULL或显示DEFAULT子句)将自动分配DEFAULT '0000-00-00 00:00:00'(零时间戳),这显然是不满足sql_mode中的NO_ZERO_DATE而报错。
查看sql_mode设置
select @@session.sql_mode;
select @@global.sql_mode;
或者
show variables like 'sql_mode';
设置sql_mode
- 临时设置sql_mode:在当前窗口中设置sql_mode
set session sql_mode='modes...'; #当前会话
set global sql_mode='modes...'; #全局会话
举例:
#设置宽松模式。此方法只在当前会话中生效,关闭当前会话就不生效了
set session sql_mode='';
#改为严格模式,依然只在当前会话生效
set session sql_mode='STRICT_TRANS_TABLES';
#改为严格模式,此方法在当前服务中生效,重启MySQL服务后失效
set global sql_mode='STRICT_TRANS_TABLES';
- 永久设置方式:在/etc/my.cnf中配置sql_mode
在my.cnf文件(windows系统是my.ini文件),新增:
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
然后重启MySQL。
一般在生产环境上是禁止重启MySQL服务的,所以采用临时设置 + 永久设置
来解决线上问题,那么即使出现了MySQL服务重启的情况,也能永久生效。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 本地部署 DeepSeek:小白也能轻松搞定!
· 自己如何在本地电脑从零搭建DeepSeek!手把手教学,快来看看! (建议收藏)
· 我们是如何解决abp身上的几个痛点
· 如何基于DeepSeek开展AI项目
· 基于DeepSeek R1 满血版大模型的个人知识库,回答都源自对你专属文件的深度学习。