MySQL5.7中的sql_mode默認值帶來的坑及解決方法

2024-07-24 12:49:44

字體：大中小

來源：轉載

供稿：網友

在正常項目開發過程中，如果MySQL版本從5.6升級到5.7版本。作為DBA在考慮數據庫版本升級帶來的影響時，一般會有幾個注意點：

sql_modeoptimizer_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 值。結果發現了兩個可能存在影響的選項：