MySQL的sql_mode模式

MySQL系列

第一章：sql_mode模式

背景

最近工作中，以前的功能突然不能正常工作，通过一通排查，发现是sql语句执行报错，具体错误信息如下，里面相关业务字段已经去掉。

The error occurred while setting parameters
### SQL: select  id,xxx from table1 group by code order by create_time desc
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
 at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91)

刚刚看到这个错误也是一头雾水，这块的代码，sql语句都是一年前写的，最近也没有修改，而且这个是常用功能，要是有问题线上早就报故障，仔细一看发现了一个关键sql_mode=only_full_group_by，才知道这一切都是sql_mode的原因造成。

一、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是不合法的,因为列不在GROUP BY从句中
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	将"
ANSI_QUOTES	启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

二、sql_mode模式配置

方式1：

先执行select @@sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，然后执行set sql_mode = '修改后的值’或者set session sql_mode=‘修改后的值’;，例如：set session sql_mode=‘STRICT_TRANS_TABLES’;改为严格模式
此方法只在当前会话中生效，关闭当前会话就不生效了。

方式2:

先执行select @@global.sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，然后执行set global sql_mode = ‘修改后的值’。
此方法在当前服务中生效，重新MySQL服务后失效

方式3：

在mysql的安装目录下，或my.cnf文件(windows系统是my.ini文件)，新增 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION。
添加my.cnf如下：

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

然后重启mysql。
此方法永久生效.当然生产环境上是禁止重启MySQL服务的，所以采用方式二加方式三来解决线上的问题，那么即便是有一天真的重启了MySQL服务，也会永久生效了。