快捷導(dǎo)航

mysql修改sql_mode報(bào)錯(cuò)的解決

更新時(shí)間：2021年09月15日 09:51:43 作者：守護(hù)者ly

今天在Navicat中運(yùn)行sql語(yǔ)句創(chuàng)建數(shù)據(jù)表出現(xiàn)了錯(cuò)誤Err 1067。本文主要介紹了mysql修改sql_mode報(bào)錯(cuò)的解決，感興趣的可以了解一下

一 ERR 1067引發(fā)的血案

今天在Navicat中運(yùn)行sql語(yǔ)句創(chuàng)建數(shù)據(jù)表出現(xiàn)了錯(cuò)誤Err 1067。而這條語(yǔ)句在有些同事的mysql上是正確的，但是在有些人那里就報(bào)錯(cuò)。你說(shuō)氣不氣人。

原因竟然是timestamp的默認(rèn)值不正確。

查閱資料得知，mysql5.7版本中有了一個(gè)STRICT mode（嚴(yán)格模式），而在此模式下默認(rèn)是不允許設(shè)置日期的值為全0值的，所以想要

解決這個(gè)問(wèn)題，就需要修改sql_mode的值。

二問(wèn)題的源頭sql_mode

我們可以進(jìn)入到mysql中一探sql_mode的究竟。首先進(jìn)入到mysql的安裝目錄下的bin目錄，使用管理員用戶登錄mysql數(shù)據(jù)庫(kù)。

使用命令 mysql –h localhost –u root–p 其中-h是指定主機(jī)名或IP地址， -u是指定用戶， -p是使用密碼登錄。

使用命令 select @@sql_mode; 可以查看sql_mode的值。如果輸入了命令卻沒(méi)有反應(yīng)，只是單單出現(xiàn)了 -> ，那么我想

你多半是應(yīng)該像我一樣，沒(méi)有輸入“;”。

通過(guò)上圖中的結(jié)果我們可以看到sql_mode中有NO_ZERO_IN_DATE和NO_ZERO_DATE，在命令行中輸入

set sql_mode=(select replace(@@sql_mode,'NO_ZERO_IN_DATE,NO_ZERO_DATE','')); 可以修改sql_mode。

之后可以查看一下sql_mode的值?？梢园l(fā)現(xiàn)已經(jīng)成功去掉了NO_ZERO_IN_DATE和NO_ZERO_DATE。

重新運(yùn)行了一下建表的sql語(yǔ)句，發(fā)現(xiàn)沒(méi)什么卵用，依然Err 1067。不要想什么姿勢(shì)不對(duì)的問(wèn)題了，只是全局的sql_mode沒(méi)有設(shè)置而已，而這里設(shè)置的sql_mode對(duì)大局根本沒(méi)有影響。

使用命令select @@global.sql_mode; 可以查看全局sql_mode的值。

剩下的操作與之前的sql_mode設(shè)置是同理可證的，只是將之前sql_mode的地方都換成了@@global.sql_mode，如圖。

完成設(shè)置之后可以在Navicat中重新運(yùn)行一下sql語(yǔ)句了，不過(guò)在那之前要先重新連接數(shù)據(jù)庫(kù)，不然依然Rrr 1067。

別問(wèn)我為什么，叫我雷鋒就好。運(yùn)行結(jié)果如下：

好了，這次表是創(chuàng)建成功了的，而且換了一個(gè)錯(cuò)誤Err 1055，翻譯過(guò)來(lái)就是“無(wú)法給包含一個(gè)非聚合的列information_schema.

PROFILING.SEQ進(jìn)行分組，這個(gè)功能不再依賴分組，且與新的規(guī)則不兼容sql_mode=only_full_group_by”。也說(shuō)了這是由于

sql_mode中的“ONLY_FULL_GROUP_BY”導(dǎo)致的?？梢栽俅涡薷膕ql_mode。

刪除之前創(chuàng)建好的表，重新連接數(shù)據(jù)庫(kù)，運(yùn)行sql語(yǔ)句，然后就棒棒棒了。

當(dāng)然這種解決方法只是揚(yáng)湯止沸，一旦重啟mysql數(shù)據(jù)庫(kù)，之前費(fèi)了狼勁設(shè)置的一堆值一夜回到解放前。

這也是有解決辦法的，下面會(huì)給大家介紹。

三設(shè)置sql_mode

可以通過(guò)修改配置文件的方式設(shè)置sql_mode，這樣在數(shù)據(jù)庫(kù)重啟之后sql_mode的值也不會(huì)改變。

首先我們需要知道的是mysql的配置文件的加載順序。進(jìn)入到數(shù)據(jù)庫(kù)安裝目錄的bin目錄下，使用命令

mysqld --verbose –help可以看到，不過(guò)這個(gè)命令的輸出結(jié)果太長(zhǎng)了，我暫時(shí)沒(méi)有找到更合適的命令來(lái)查看。

加載順序如圖：

這些配置文件在加載時(shí)，后加載的會(huì)將之前加載的配置文件中的相同的值覆蓋。不過(guò)我只在mysql的安裝目錄下找到了

一個(gè)名字很像的配置文件，其余的都沒(méi)有找到。

將這個(gè)文件備份好之后，修改名稱為my.ini，與給出的加載配置文件順序中的文件對(duì)應(yīng)。然后打開(kāi)文件，我這里的配置

文件中的sql_mode只有兩個(gè)值。

重啟數(shù)據(jù)庫(kù)后，使用命令查看sql_mode的值，發(fā)現(xiàn)與配置文件中完全吻合，搞定！

附加幾種常見(jiàn)的sql_mode值的介紹：

幾種常見(jiàn)的mode介紹

ONLY_FULL_GROUP_BY：出現(xiàn)在select語(yǔ)句、HAVING條件和ORDER BY語(yǔ)句中的列，必須是GROUP BY的列或者依賴于GROUP BY列的函數(shù)列。
NO_AUTO_VALUE_ON_ZERO：該值影響自增長(zhǎng)列的插入。默認(rèn)設(shè)置下，插入0或NULL代表生成下一個(gè)自增長(zhǎng)值。如果用戶希望插入的值為0，而該列又是自增長(zhǎng)的，那么這個(gè)選項(xiàng)就有用了。
STRICT_TRANS_TABLES：在該模式下，如果一個(gè)值不能插入到一個(gè)事務(wù)表中，則中斷當(dāng)前的操作，對(duì)非事務(wù)表不做限制
NO_ZERO_IN_DATE：這個(gè)模式影響了是否允許日期中的月份和日包含0。如果開(kāi)啟此模式，2016-01-00是不允許的，但是0000-02-01是允許的。它實(shí)際的行為受到 strict mode是否開(kāi)啟的影響。
NO_ZERO_DATE：設(shè)置該值，mysql數(shù)據(jù)庫(kù)不允許插入零日期。它實(shí)際的行為受到 strictmode是否開(kāi)啟的影響。
ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE過(guò)程中，如果數(shù)據(jù)被零除，則產(chǎn)生錯(cuò)誤而非警告。如果未給出該模式，那么數(shù)據(jù)被零除時(shí)MySQL返回NULL
NO_AUTO_CREATE_USER：禁止GRANT創(chuàng)建密碼為空的用戶
NO_ENGINE_SUBSTITUTION：如果需要的存儲(chǔ)引擎被禁用或未編譯，那么拋出錯(cuò)誤。不設(shè)置此值時(shí)，用默認(rèn)的存儲(chǔ)引擎替代，并拋出一個(gè)異常
PIPES_AS_CONCAT：將”||”視為字符串的連接操作符而非或運(yùn)算符，這和Oracle數(shù)據(jù)庫(kù)是一樣的，也和字符串的拼接函數(shù)Concat相類似
ANSI_QUOTES：?jiǎn)⒂肁NSI_QUOTES后，不能用雙引號(hào)來(lái)引用字符串，因?yàn)樗唤忉尀樽R(shí)別符