MySQL的sql_mode详解

MySQL的sql_mode详解

微信搜索 zze_coding 或扫描 👉 二维码关注我的微信公众号获取更多资源推送:

概述​

sql_mode 是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题。

sql_model 常用来解决下面几类问题:

  1. 通过设置 sql_mode, 可以完成不同严格程度的数据校验,有效地保障数据完整性。
  2. 通过设置 sql_model 为宽松模式,来保证大多数 sql 符合标准的 sql 语法,这样应用在不同数据库之间进行迁移时,则不需要对业务 sql 进行较大的修改。
  3. 在不同数据库之间进行数据迁移之前,通过设置 sql_mode 可以使 MySQL 上的数据更方便地迁移到目标数据库中。

常用取值

sql_mode 常用值如下:

  • ONLY_FULL_GROUP_BY:对于 GROUP BY 聚合操作,如果在 SELECT 中的列没有在 GROUP BY 中出现,那么这个 SQL 是不合法的;
  • NO_AUTO_VALUE_ON_ZERO:该值影响自增长列的插入。默认设置下,插入 0NULL 代表生成下一个自增长值。如果用户希望插入的值为 0,而该列又是自增长的,那么这个选项就有用了;
  • STRICT_TRANS_TABLES:在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制;
  • NO_ZERO_IN_DATE:在严格模式下,不允许日期和月份为零;
  • NO_ZERO_DATE:设置该值,MySQL 数据库不允许插入零日期,插入零日期会抛出错误而不是警告;
  • ERROR_FOR_DIVISION_BY_ZERO:在 INSERTUPDATE 过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时 MySQL 返回 NULL
  • ​NO_AUTO_CREATE_USER:禁止 GRANT 创建密码为空的用户;
  • NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常;
  • PIPES_AS_CONCAT:将 || 视为字符串的连接操作符而非或运算符,这和 Oracle 数据库是一样的,也和字符串的拼接函数 concat 相类似;
  • ANSI_QUOTES:启用 ANSI_QUOTES 后,不能用双引号来引用字符串,因为它被解释为识别符;

宽松与严格模式

MySQL 5.6 和 MySQL 5.7 默认的 sql_mode 模式参数是不一样的,5.6 的是 NO_ENGINE_SUBSTITUTION,其实表示的是一个空值,相当于没有什么模式设置,可以理解为宽松模式。5.7 的是 STRICT_TRANS_TABLES,也就是严格模式。

如果设置的是宽松模式,那么我们在插入数据的时候,即便是给了一个错误的数据,也可能会被接受,并且不报错。
例如:我在创建一个表时,该表中有一个字段为 name,给 name 设置的字段类型时 char(10),如果我在插入数据的时候,其中 name 这个字段对应的有一条数据的长度超过了 10,例如 '1234567890abc',超过了设定的字段长度 10,那么不会报错,并且取前十个字符存上。也就是说你这个数据被存为了 '1234567890',而 'abc' 就没有了,但是我们知道,我们给的这条数据是错误的,因为超过了字段长度,但是并没有报错,并且 MySQL 自行处理并接受了,这就是宽松模式的效果。

其实在开发、测试、生产等环境中,我们应该采用的是严格模式,出现这种错误,应该报错才对,所以 MySQL 5.7 版本就将 sql_mode 默认值改为了严格模式,并且我们即便是用的 MySQL 5.6,也应该自行将其改为严格模式。

查看与设定

可通过如下语句查看当前生效的 sql_mode

-- 查看会话级别的 sql_mode
mysql> select @@sql_mode;
-- 查看全局级别的 sql_mode
mysql> select @@global.sql_mode;

可通过如下语句设定当前生效的 sql_mode

-- 会话级别的设定 session 可省略
mysql> set session sql_mode='值'
-- 全局级别的设定
mysql> set global sql_mode='值'

永久生效

要让指定的 sql_mode 设定永久生效,只需要在 my.cnf 中的 [mysqld] 节下添加 sql_mode 配置即可,看下面示例。

如果当前使用 MySQL 但希望遵循 Oracle 的 SQL 规范,可以​在 my.cnf 添加如下对 MySQL 的 sql_mode 设置:

[mysqld]
sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,PIPES_AS_CONCAT,ANSI_QUOTES'

当然,此种配置需要重启生效。

参考:

Copyright: 采用 知识共享署名4.0 国际许可协议进行许可

Links: https://www.zze.xyz/archives/mysql-sqlmode.html

Buy me a cup of coffee ☕.