概述
sql_mode
是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题。
sql_model
常用来解决下面几类问题:
- 通过设置
sql_mode
, 可以完成不同严格程度的数据校验,有效地保障数据完整性。 - 通过设置
sql_model
为宽松模式,来保证大多数 sql 符合标准的 sql 语法,这样应用在不同数据库之间进行迁移时,则不需要对业务 sql 进行较大的修改。 - 在不同数据库之间进行数据迁移之前,通过设置
sql_mode
可以使 MySQL 上的数据更方便地迁移到目标数据库中。
常用取值
sql_mode
常用值如下:
ONLY_FULL_GROUP_BY
:对于GROUP BY
聚合操作,如果在SELECT
中的列没有在GROUP BY
中出现,那么这个 SQL 是不合法的;NO_AUTO_VALUE_ON_ZERO
:该值影响自增长列的插入。默认设置下,插入0
或NULL
代表生成下一个自增长值。如果用户希望插入的值为0
,而该列又是自增长的,那么这个选项就有用了;STRICT_TRANS_TABLES
:在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制;NO_ZERO_IN_DATE
:在严格模式下,不允许日期和月份为零;NO_ZERO_DATE
:设置该值,MySQL 数据库不允许插入零日期,插入零日期会抛出错误而不是警告;ERROR_FOR_DIVISION_BY_ZERO
:在INSERT
或UPDATE
过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时 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'
当然,此种配置需要重启生效。
参考:
评论区