sql_mode MySQL5.7默认带来的坑及解决方案

在正常的项目开发过程中，如果MySQL版本从5.6升级到5.7。作为数据库管理员，在考虑数据库版本更新的影响时，一般有以下几点需要注意：

1. sql_mode 默认值变更
2. optimizer_switch 值变更 数据库紧急更新影响主Prepare复制

本文主要内容是MySQL更新到5.7版本后，默认模式sql_mode造成的陷阱及相应解决方案。 ？ sql_mode 的默认值已更改。在5.7版本的sql_mode中，默认值为ONLY_FULL_GROUP_BY。该选项的含义表示：使用 GROUP BY SQL 进行查询时，不允许SELECT 部分出现在 GROUP 中未出现的字段中，即，选择。搜索字段必须为 GROUP BY 出现在 中或使用聚合函数。

解决方案

• 方案1（不推荐）：修改5.7版本sql_mode，删除♻❙❙LEN_FULL_GROUP_GROUP ONLY_FULL_GROUP_BY

SQL 规范旨在强化 SQL 查询结果更加兼容和准确。如果没有规范限制ONLY_FULL_GROUP_BY，则可以允许运行以下 SQL：SELECT a,b,c FROM t GROUP BY a。 SQL 按字段值分组如果同一个字段值对应多个b或c值，那么查询结果中的b和c值是不确定的。

第二个选项：重写SQL

案例2：NO_ZERO_DATE＆NO_ZERO_IN_DATE＆time_zone

问题描述

从一个MySQL阶段到MySQL版本1的故障排除

后。表创建过程失败：

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：插入MySQL的时间字段值不允许日期为零
• NO_ZERO_IN_DATE：插入MySQL的时间字段值不允许date 为零 0 日和月都为零

Prseboting Phase 2

所以解决方案是根据要求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都符合规范，但是表还是无法创建create。成功。我不得不去官方手册找到关于时间戳的介绍：

TIMESTAMP 数据类型用于同时包含日期和时间部分的值。 TIMESTAMP 范围从 '1970-01-01 00:00:01' UTC 到 '2038 -01-19 03:14:07' UTC。

故障排除第 3 阶段

该范围在官方定义中给出了字段值是'1970-01-01 00:00:01'到'2038-01-19 03:14:07'，说明我们设置的默认值不在时间戳范围内。于是我又把默认的改了：

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 = CSTSystem_time_zone Utctime_zone = '+08:00'Time_zone = System

Time❙回过头看range mp字段mp STAMP数据类型的定义sa 用于同时包含日期和时间部分的值。 TIMESTAMP 的范围是 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。

此时间范围是指 UTC 时区的时间范围。如果测试环境设置的是CST东八区时区，则对应的时间范围也需要增加8小时。于是将时间戳字段的默认值改为'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)
复制代码

解决办法

• 将时间戳字段的默认值改为CST时区对应的最小值'1970-01-01 08:00:01'

作者：Win-Man
Nugets