1.8　sql_mode

1.8.1　sql_mode简介

可将sql_mode翻译为SQL模式，它是一组MySQL支持的基本语法及数据校验规则。sql_mode的取值不同会影响MySQL支持的基本语法及数据校验规则。通过设置sql_mode的值，可以完成不同严格程度的数据校验，保证数据的准确性。MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode的值。在MySQL 5.6和MySQL 5.7及其后续版本中，sql_mode的默认值是不一样的。

●在MySQL 5.6中，sql_mode的默认值为空（NO_ENGINE_SUBSTITUTION），它其实表示的是一个空值，相当于没有设置模式，可以理解为宽松模式。在这种设置下是允许一些非法操作的，如允许一些错误数据的插入。

●在MySQL 5.7及其后续版本中，sql_mode的默认值为STRICT_TRANS_TABLES，可以理解为严格模式，用于进行数据的严格校验。例如，不能将错误数据插入表中，否则将报错，并且导致事务回滚。

1.8.2　宽松模式与严格模式对比

如果MySQL设置的是宽松模式，那么，在插入数据的时候，即便插入了一条错误数据，也可能会被接受，并且不会报错。

例如，在创建一张表时，该表中有一个字段为name，该字段的数据类型是CHAR（10）。如果在插入数据的时候，name字段对应的数据长度超过10，如插入数据“1234567890abc”，那么此时MySQL并不会报错，而会截取前10个字符存储到表中。也就是说，name字段的数据存储为“1234567890”，“abc”被舍弃了。但是这条数据是错误的，因为其长度超过字段长度。这就是宽松模式的效果。

通过设置sql_mode的值为宽松模式，可以保证大多数SQL语句符合标准的SQL语法，在进行数据库迁移或项目迁移时，就不需要对与业务相关的SQL语句进行较大的修改。

严格模式就是当出现宽松模式下的错误时，MySQL会给出报错信息。从MySQL 5.7开始，将sql_mode的默认值更改为严格模式。生产环境下的数据库必须设置为严格模式，开发环境、测试环境下的数据库也必须设置为严格模式，这样在开发测试阶段就可以发现问题。即便我们使用的是MySQL 5.6，也应该自行将sql_mode的值更改为严格模式。

根据以往的开发经验，MySQL等数据库总想包揽关于数据的所有操作，包括数据校验。其实，我们应该在自己开发的项目程序中进行数据校验，虽然在编写程序代码的时候多了一些数据校验流程，但是在进行数据库迁移或项目迁移时会方便很多。

例如，在严格模式中包含NO_ZERO_DATE值，那么MySQL将不允许插入零日期，否则会抛出错误。使用如下语句创建表test_time。

分别测试下面两条插入语句，从结果中可以看到，当日期设置为“0000-00-00”时，因不满足sql_mode中的NO_ZERO_DATE条件而抛出错误。

1.8.3　模式的查看和设置

使用如下语句查看sql_mode的值，从结果中可以看到，sql_mode有多种不同的值。

sql_mode的常用值及其含义如表1-4所示。

设置sql_mode有两种方式，分别是临时设置和永久设置。

（1）临时设置就是在命令行窗口中设置sql_mode，语法如下所示。

举例如下：

（2）永久设置就是在my.cnf配置文件中设置sql_mode。

首先在my.cnf配置文件（Windows平台下是my.ini配置文件）中新增如下配置，然后重启MySQL服务。

当然，在生产环境中对于重启MySQL服务是严格控制的。因此，无论是采用临时设置方式，还是采用永久设置方式，即便有一天真的重启MySQL服务，对MySQL的相关设置也会永久生效。