在正常項(xiàng)目開(kāi)發(fā)過(guò)程中，如果MySQL版本從5.6升級(jí)到5.7版本。作為DBA在考慮數(shù)據(jù)庫(kù)版本升級(jí)帶來(lái)的影響時(shí)，一般會(huì)有幾個(gè)注意點(diǎn)：
sql_mode
optimizer_switch
本文主要內(nèi)容是MySQL升級(jí)到5.7版本之后，由于默認(rèn)的 sql_mode 值帶來(lái)的坑以及對(duì)應(yīng)的解決方案。
案例一：ONLY_FULL_GROUP_BY
問(wèn)題描述
MySQL版本從5.6升級(jí)至5.7之后，部分SQL執(zhí)行報(bào)錯(cuò)，報(bào)錯(cuò)信息如下：
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
這個(gè)問(wèn)題原因在于從5.6升級(jí)至5.7版本后 sql_mode 默認(rèn)值發(fā)生了改變，在5.7版本的 sql_mode 默認(rèn)值中有意向 ONLY_FULL_GROUP_BY ，該選項(xiàng)的含義表示：對(duì)于使用 GROUP BY 進(jìn)行查詢(xún)的SQL，不允許 SELECT 部分出現(xiàn) GROUP BY 中未出現(xiàn)的字段，也就是 SELECT 查詢(xún)的字段必須是 GROUP BY 中出現(xiàn)的或者使用聚合函數(shù)的。
解決方案
方案一（不推薦）：修改5.7版本 sql_mode 值，將 ONLY_FULL_GROUP_BY 去掉
ONLY_FULL_GROUP_BY 是加強(qiáng)SQL規(guī)范的，其目的是讓SQL查詢(xún)出來(lái)的結(jié)果更符合規(guī)范，更準(zhǔn)確。
如果沒(méi)有 ONLY_FULL_GROUP_BY 規(guī)范限制，那么則能允許以下SQL的執(zhí)行： SELECT a,b,c FROM t GROUP BY a 。SQL按照a字段值進(jìn)行分組，當(dāng)同一個(gè)a字段值對(duì)應(yīng)多個(gè)b或者c值時(shí)，查詢(xún)結(jié)果中的b,c值是不確定的。
方案二：改寫(xiě)SQL
案例二：NO_ZERO_DATE NO_ZERO_IN_DATE time_zone
問(wèn)題描述
排錯(cuò)階段一
MySQL版本從5.6升級(jí)至5.7之后，創(chuàng)建表的過(guò)程中失敗：
mysql> CREATE TABLE `t_manager` (
.....
-> `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
-> `MODIFIER` varchar(32) DEFAULT NULL COMMENT '更新人',
-> `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
-> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT '刪除狀態(tài) 1：刪除 0:未刪除',
-> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT '啟用狀態(tài) 1:啟用 0:禁用',
-> PRIMARY KEY (`CACHE_ID`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'
錯(cuò)誤提示 MODIFY_DATETIME 字段設(shè)置的默認(rèn)值是無(wú)效的，考慮到剛從5.6版本升級(jí)到5.7版本，于是又去翻了翻5.7中默認(rèn)的 sql_mode 值。結(jié)果發(fā)現(xiàn)了兩個(gè)可能存在影響的選項(xiàng)：
NO_ZERO_DATE
NO_ZERO_IN_DATE
排錯(cuò)階段二
于是解決方案就是按照 NO_ZERO_DATE 以及 NO_ZERO_IN_DATE 的要求設(shè)置默認(rèn)值，將 MODIFY_DATETIME 字段默認(rèn)值設(shè)置為'1001-01-01 01:01:01'，結(jié)果發(fā)現(xiàn)還是無(wú)法成功創(chuàng)建表：
mysql>CREATE TABLE `t_manager` (
.....
-> `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
-> `MODIFIER` varchar(32) DEFAULT NULL COMMENT '更新人',
-> `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '1001-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
-> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT '刪除狀態(tài) 1：刪除 0:未刪除',
-> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT '啟用狀態(tài) 1:啟用 0:禁用',
-> PRIMARY KEY (`CACHE_ID`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'
查看了所有的 sql_mode 值，都符合規(guī)范，但是表還是創(chuàng)建不成功。只好去官方手冊(cè)上找找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.
排錯(cuò)階段三
可以看到官方定義中timestamp字段值的范圍是'1970-01-01 00:00:01'到'2038-01-19 03：14：07'，原來(lái)是我們?cè)O(shè)置的默認(rèn)值不在timestamp范圍之內(nèi)。于是再次修改默認(rèn)值：
mysql>CREATE TABLE `t_manager` (
.....
-> `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
-> `MODIFIER` varchar(32) DEFAULT NULL COMMENT '更新人',
-> `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
-> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT '刪除狀態(tài) 1：刪除 0:未刪除',
-> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT '啟用狀態(tài) 1:啟用 0:禁用',
-> PRIMARY KEY (`CACHE_ID`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'
邪了門(mén)，居然還是無(wú)法成功創(chuàng)建表。實(shí)在是沒(méi)轍了，向同事求救，同事說(shuō)他在機(jī)器上試試，結(jié)果同樣的語(yǔ)句在他的MySQL上執(zhí)行成功，同樣是5.7.23版本。
百思不得其解。
一氣之下將兩邊的參數(shù)值拿出來(lái)對(duì)比了一下，果然找到了不同的根本。
| 測(cè)試環(huán)境 |
同事環(huán)境 |
| system_time_zone=CST |
system_time_zone UTC |
| time_zone='+08:00' |
time_zone=SYSTEM |
回過(guò)頭來(lái)看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.
這個(gè)時(shí)間范圍指的是UTC時(shí)區(qū)的時(shí)間范圍，測(cè)試環(huán)境設(shè)置了CST東八區(qū)的時(shí)區(qū)，則對(duì)應(yīng)的時(shí)間范圍上也需要對(duì)應(yīng)的加8小時(shí)。所以將timestamp字段默認(rèn)值修改為'1970-01-01 08:00:01'，表終于創(chuàng)建成功。
mysql>CREATE TABLE `mn_cache_refresh_manager` (
......
-> `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
-> `MODIFIER` varchar(32) DEFAULT NULL COMMENT '更新人',
-> `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
-> `IS_DELETED` bit(1) DEFAULT b'0' COMMENT '刪除狀態(tài) 1：刪除 0:未刪除',
-> `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT '啟用狀態(tài) 1:啟用 0:禁用',
-> PRIMARY KEY (`CACHE_ID`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
總結(jié)
以上所述是小編給大家介紹的MySQL5.7中的sql_mode默認(rèn)值帶來(lái)的坑及解決方法，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
您可能感興趣的文章:- MySQL sql_mode修改不生效的原因及解決
- 詳解MySQL的sql_mode查詢(xún)與設(shè)置
- MySQL中SQL Mode的查看與設(shè)置詳解
- mysql中的sql_mode模式實(shí)例詳解
- Django2 連接MySQL及model測(cè)試實(shí)例分析
- 關(guān)于MySQL的sql_mode合理設(shè)置詳解
- MySQL關(guān)于sql_mode解析與設(shè)置講解
- MySql版本問(wèn)題sql_mode=only_full_group_by的完美解決方案
- 解決MySQL 5.7.9版本sql_mode=only_full_group_by問(wèn)題
- Mysql之SQL Mode用法詳解
- mysql sql_mode="" 的作用說(shuō)明
- MySQL sql_mode的使用詳解
