MySQL5.7中的sql_mode默认值带来的坑及解决方法

在正常项目开发过程中，如果mysql版本从5.6升级到5.7版本。作为dba在考虑数据库版本升级带来的影响时，一般会有几个注意点：

sql_mode
optimizer_switch

本文主要内容是mysql升级到5.7版本之后，由于默认的 sql_mode 值带来的坑以及对应的解决方案。

案例一：only_full_group_by

问题描述

mysql版本从5.6升级至5.7之后，部分sql执行报错，报错信息如下：

error 1055 (42000): expression #3 of xxxxxx list is not in group by clause and contains nonaggregated column ‘xxxxx.xxxxxx' which is not functionally dependent on columns in group by clause; this is incompatible with sql_mode=only_full_group_by

这个问题原因在于从5.6升级至5.7版本后 sql_mode 默认值发生了改变，在5.7版本的 sql_mode 默认值中有意向 only_full_group_by ，该选项的含义表示：对于使用 group by 进行查询的sql，不允许 select 部分出现 group by 中未出现的字段，也就是 select 查询的字段必须是 group by 中出现的或者使用聚合函数的。

解决方案

方案一（不推荐）：修改5.7版本 sql_mode 值，将 only_full_group_by 去掉

only_full_group_by 是加强sql规范的，其目的是让sql查询出来的结果更符合规范，更准确。

如果没有 only_full_group_by 规范限制，那么则能允许以下sql的执行： select a,b,c from t group by a 。sql按照a字段值进行分组，当同一个a字段值对应多个b或者c值时，查询结果中的b,c值是不确定的。

方案二：改写sql

案例二：no_zero_date & no_zero_in_date & time_zone

问题描述

排错阶段一

mysql版本从5.6升级至5.7之后，创建表的过程中失败：

mysql> create table `t_manager` (
  .....
  ->  `create_datetime` timestamp not null default current_timestamp comment '创建时间',
  ->  `modifier` varchar(32) default null comment '更新人',
  ->  `modify_datetime` timestamp not null default '0000-00-00 00:00:00' on update current_timestamp comment '修改时间',
  ->  `is_deleted` bit(1) default b'0' comment '删除状态 1：删除 0:未删除',
  ->  `is_enable` bit(1) default b'1' comment '启用状态 1:启用 0:禁用',
  ->  primary key (`cache_id`)
  -> ) engine=innodb default charset=utf8;
error 1067 (42000): invalid default value for 'modify_datetime'

错误提示 modify_datetime 字段设置的默认值是无效的，考虑到刚从5.6版本升级到5.7版本，于是又去翻了翻5.7中默认的 sql_mode 值。结果发现了两个可能存在影响的选项：

no_zero_date
no_zero_in_date

排错阶段二

于是解决方案就是按照 no_zero_date 以及 no_zero_in_date 的要求设置默认值，将 modify_datetime 字段默认值设置为'1001-01-01 01:01:01'，结果发现还是无法成功创建表：

mysql>create table `t_manager` (
  .....
  ->  `create_datetime` timestamp not null default current_timestamp comment '创建时间',
  ->  `modifier` varchar(32) default null comment '更新人',
  ->  `modify_datetime` timestamp not null default '1001-01-01 01:01:01' on update current_timestamp comment '修改时间',
  ->  `is_deleted` bit(1) default b'0' comment '删除状态 1：删除 0:未删除',
  ->  `is_enable` bit(1) default b'1' comment '启用状态 1:启用 0:禁用',
  ->  primary key (`cache_id`)
  -> ) engine=innodb default charset=utf8;
error 1067 (42000): invalid default value for 'modify_datetime'

查看了所有的 sql_mode 值，都符合规范，但是表还是创建不成功。只好去官方手册上找找timestamp介绍：

the timestamp data type is used for values that contain both date and time parts. timestamp has a range of ‘1970-01-01 00:00:01' utc to ‘2038-01-19 03:14:07' utc.

排错阶段三

可以看到官方定义中timestamp字段值的范围是'1970-01-01 00:00:01'到'2038-01-19 03：14：07'，原来是我们设置的默认值不在timestamp范围之内。于是再次修改默认值：

mysql>create table `t_manager` (
  .....
  ->  `create_datetime` timestamp not null default current_timestamp comment '创建时间',
  ->  `modifier` varchar(32) default null comment '更新人',
  ->  `modify_datetime` timestamp not null default '1970-01-01 00:00:01' on update current_timestamp comment '修改时间',
  ->  `is_deleted` bit(1) default b'0' comment '删除状态 1：删除 0:未删除',
  ->  `is_enable` bit(1) default b'1' comment '启用状态 1:启用 0:禁用',
  ->  primary key (`cache_id`)
  -> ) engine=innodb default charset=utf8;
error 1067 (42000): invalid default value for 'modify_datetime'

邪了门，居然还是无法成功创建表。实在是没辙了，向同事求救，同事说他在机器上试试，结果同样的语句在他的mysql上执行成功，同样是5.7.23版本。

百思不得其解。

一气之下将两边的参数值拿出来对比了一下，果然找到了不同的根本。

测试环境	同事环境
system_time_zone=cst	system_time_zone utc
time_zone='+08:00'	time_zone=system

回过头来看timestamp字段定义的范围：

the timestamp data type is used for values that contain both date and time parts. timestamp has a range of ‘1970-01-01 00:00:01' utc to ‘2038-01-19 03:14:07' utc.

这个时间范围指的是utc时区的时间范围，测试环境设置了cst东八区的时区，则对应的时间范围上也需要对应的加8小时。所以将timestamp字段默认值修改为'1970-01-01 08:00:01'，表终于创建成功。

mysql>create table `mn_cache_refresh_manager` (
  ......
  ->  `create_datetime` timestamp not null default current_timestamp comment '创建时间',
  ->  `modifier` varchar(32) default null comment '更新人',
  ->  `modify_datetime` timestamp not null default '1970-01-01 08:00:01' on update current_timestamp comment '修改时间',
  ->  `is_deleted` bit(1) default b'0' comment '删除状态 1：删除 0:未删除',
  ->  `is_enable` bit(1) default b'1' comment '启用状态 1:启用 0:禁用',
  ->  primary key (`cache_id`)
  -> ) engine=innodb default charset=utf8;
query ok, 0 rows affected (0.02 sec)

总结

以上所述是小编给大家介绍的mysql5.7中的sql_mode默认值带来的坑及解决方法，希望对大家有所帮助