国产性片在线,日本视频一区二区免费播放

主頁 > 知識庫 > MySQL5.7中的sql_mode默認值帶來的坑及解決方法

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默認值帶來的坑及解決方法，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網站的支持！

您可能感興趣的文章: