MySQL5.7中的sql_mode預設值

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

1. sql_mode 預設值的改變

2. optimizer_switch 值的改變

3. 備庫升級影響主備複製

本文主要內容是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值是不確定的。

• 方案二：對於不符合ONLY_FULL_GROUP_BY限制的欄位，新增unique索引

• 方案三：改寫SQL，按照規範編寫SQL

• 方案四：使用ANY_VALUE()，對於不符合ONLY_FULL_GROUP_BY的欄位使用ANY_VALUE()函式，讓MySQL跳過ONLY_FULL_GROUP_BY檢測

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by
mysql>SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

| 案例二：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 :MySQL中插入的時間欄位值，不允許日期為零

• NO_ZERO_IN_DATE ：MySQL中插入的時間欄位值，不允許日期和月份為零

排 錯階段二

於是解決方案就是按照 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版本。 
百思不得其解。 
一氣之下將兩邊的引數值拿出來對比了一下，果然找到了不同的根本。 

回過頭來看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)

解決方案

將timestamp欄位預設值修改為對應CST時區的最小值'1970-01-01 08:00:01'

| 總結

最後總結一下MySQL中5.7中sql_mode引數預設值的幾個意思：

• ONLY_FULL_GROUP_BY 
  對於使用 GROUP BY 進行查詢的SQL，不允許 SELECT 部分出現 GROUP BY 中未出現的欄位，也就是 SELECT 查詢的欄位必須是 GROUP BY 中出現的或者使用聚合函式的或者是具有唯一屬性的。

• STRICT_TRANS_TABLES 
  該選項針對事務性儲存引擎生效，對於非事務性儲存引擎無效，該選項表示開啟strict sql模式。在strict sql模式下，在INSERT或者UPDATE語句中，插入或者更新了某個不符合規定的欄位值，則會直接報錯中斷操作

• NO_ZERO_IN_DATE 
  MySQL中插入的時間欄位值，不允許日期和月份為零

• NO_ZERO_DATE 
  MySQL中插入的時間欄位值，不允許日期為零

• ERROR_FOR_DIVISION_BY_ZERO 
  INSERT或者UPDATE語句中，如果資料被0除，則出現警告（非strict sql模式下）或者錯誤(strict sql模式下)。

• 當該選項關閉時，數字被0除，得到NULL且不會產生警告

• 當該選項開啟且處於非strict sql模式下，數字被0除，得到NULL但是會產生警告

• 當該選項開啟且處於strict sql模式下，數字被0除，產生錯誤且中斷操作

• NO_AUTO_CREATE_USER 
  之前版本中使用GRANT語法，如果使用者不存在則會自動建立使用者，該選項限制該功能

• NO_ENGINE_SUBSTITUTION 
  在使用CREATE TABLE或者ALTER TABLE語法執行儲存引擎的時候，如果設定的儲存引擎被禁用或者未編譯，會產生錯誤。

| 作者簡介

沈 剛·沃趣科技資料庫技術專家

熟悉MySQL資料庫執行機制，豐富的資料庫及複製架構故障診斷、效能調優、資料庫備份恢復及遷移經驗。