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执行报错，报错信息如下：
ERROR1055(42000):Expression#3ofXXXXXXlistisnotinGROUPBYclauseandcontainsnonaggregatedcolumn‘XXXXX.XXXXXX'whichisnotfunctionallydependentoncolumnsinGROUPBYclause;thisisincompatiblewithsql_mode=only_full_group_by
这个问题原因在于从5.6升级至5.7版本后sql_mode默认值发生了改变，在5.7版本的sql_mode默认值中有意向ONLY_FULL_GROUP_BY，该选项的含义表示：对于使用GROUPBY进行查询的SQL，不允许SELECT部分出现GROUPBY中未出现的字段，也就是SELECT查询的字段必须是GROUPBY中出现的或者使用聚合函数的。
解决方案
方案一（不推荐）：修改5.7版本sql_mode值，将ONLY_FULL_GROUP_BY去掉
ONLY_FULL_GROUP_BY是加强SQL规范的，其目的是让SQL查询出来的结果更符合规范，更准确。
如果没有ONLY_FULL_GROUP_BY规范限制，那么则能允许以下SQL的执行：SELECTa,b,cFROMtGROUPBYa。SQL按照a字段值进行分组，当同一个a字段值对应多个b或者c值时，查询结果中的b,c值是不确定的。
方案二：改写SQL
案例二：NO_ZERO_DATE&NO_ZERO_IN_DATE&time_zone
问题描述
排错阶段一
MySQL版本从5.6升级至5.7之后，创建表的过程中失败：
mysql>CREATETABLE`t_manager`( ..... ->`CREATE_DATETIME`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间', ->`MODIFIER`varchar(32)DEFAULTNULLCOMMENT'更新人', ->`MODIFY_DATETIME`timestampNOTNULLDEFAULT'0000-00-0000:00:00'ONUPDATECURRENT_TIMESTAMPCOMMENT'修改时间', ->`IS_DELETED`bit(1)DEFAULTb'0'COMMENT'删除状态1：删除0:未删除', ->`IS_ENABLE`bit(1)DEFAULTb'1'COMMENT'启用状态1:启用0:禁用', ->PRIMARYKEY(`CACHE_ID`) ->)ENGINE=InnoDBDEFAULTCHARSET=utf8; ERROR1067(42000):Invaliddefaultvaluefor'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-0101:01:01'，结果发现还是无法成功创建表：
mysql>CREATETABLE`t_manager`( ..... ->`CREATE_DATETIME`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间', ->`MODIFIER`varchar(32)DEFAULTNULLCOMMENT'更新人', ->`MODIFY_DATETIME`timestampNOTNULLDEFAULT'1001-01-0101:01:01'ONUPDATECURRENT_TIMESTAMPCOMMENT'修改时间', ->`IS_DELETED`bit(1)DEFAULTb'0'COMMENT'删除状态1：删除0:未删除', ->`IS_ENABLE`bit(1)DEFAULTb'1'COMMENT'启用状态1:启用0:禁用', ->PRIMARYKEY(`CACHE_ID`) ->)ENGINE=InnoDBDEFAULTCHARSET=utf8; ERROR1067(42000):Invaliddefaultvaluefor'MODIFY_DATETIME'
查看了所有的sql_mode值，都符合规范，但是表还是创建不成功。只好去官方手册上找找timestamp介绍：
TheTIMESTAMPdatatypeisusedforvaluesthatcontainbothdateandtimeparts.TIMESTAMPhasarangeof‘1970-01-0100:00:01'UTCto‘2038-01-1903:14:07'UTC.
排错阶段三
可以看到官方定义中timestamp字段值的范围是'1970-01-0100:00:01'到'2038-01-1903：14：07'，原来是我们设置的默认值不在timestamp范围之内。于是再次修改默认值：
mysql>CREATETABLE`t_manager`( ..... ->`CREATE_DATETIME`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间', ->`MODIFIER`varchar(32)DEFAULTNULLCOMMENT'更新人', ->`MODIFY_DATETIME`timestampNOTNULLDEFAULT'1970-01-0100:00:01'ONUPDATECURRENT_TIMESTAMPCOMMENT'修改时间', ->`IS_DELETED`bit(1)DEFAULTb'0'COMMENT'删除状态1：删除0:未删除', ->`IS_ENABLE`bit(1)DEFAULTb'1'COMMENT'启用状态1:启用0:禁用', ->PRIMARYKEY(`CACHE_ID`) ->)ENGINE=InnoDBDEFAULTCHARSET=utf8; ERROR1067(42000):Invaliddefaultvaluefor'MODIFY_DATETIME'
邪了门，居然还是无法成功创建表。实在是没辙了，向同事求救，同事说他在机器上试试，结果同样的语句在他的MySQL上执行成功，同样是5.7.23版本。
百思不得其解。
一气之下将两边的参数值拿出来对比了一下，果然找到了不同的根本。
| 测试环境 | 同事环境 |
|---|---|
| system_time_zone=CST | system_time_zoneUTC |
| time_zone='+08:00' | time_zone=SYSTEM |
回过头来看timestamp字段定义的范围：
TheTIMESTAMPdatatypeisusedforvaluesthatcontainbothdateandtimeparts.TIMESTAMPhasarangeof‘1970-01-0100:00:01'UTCto‘2038-01-1903:14:07'UTC.
这个时间范围指的是UTC时区的时间范围，测试环境设置了CST东八区的时区，则对应的时间范围上也需要对应的加8小时。所以将timestamp字段默认值修改为'1970-01-0108:00:01'，表终于创建成功。
mysql>CREATETABLE`mn_cache_refresh_manager`( ...... ->`CREATE_DATETIME`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间', ->`MODIFIER`varchar(32)DEFAULTNULLCOMMENT'更新人', ->`MODIFY_DATETIME`timestampNOTNULLDEFAULT'1970-01-0108:00:01'ONUPDATECURRENT_TIMESTAMPCOMMENT'修改时间', ->`IS_DELETED`bit(1)DEFAULTb'0'COMMENT'删除状态1：删除0:未删除', ->`IS_ENABLE`bit(1)DEFAULTb'1'COMMENT'启用状态1:启用0:禁用', ->PRIMARYKEY(`CACHE_ID`) ->)ENGINE=InnoDBDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.02sec)
总结
以上所述是小编给大家介绍的MySQL5.7中的sql_mode默认值带来的坑及解决方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！