一 ERR 1067引發的血案

今天在Navicat中運行sql語句創建數據表出現了錯誤Err 1067。而這條語句在有些同事的mysql上是正確的，但是在有些人那里就報錯。

QQ截圖20170811143551.png

原因竟然是timestamp的默認值不正確。

查閱資料得知，mysql5.7版本中有了一個STRICT mode(嚴格模式)，而在此模式下默認是不允許設置日期的值為全0值的，所以想要解決這個問題，就需要修改sql_mode的值。

二 問題的源頭sql_mode

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

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

QQ截圖20170811141126.png

使用命令 select @@sql_mode; 可以查看sql_mode的值。如果輸入了命令卻沒有反應，只是單單出現了-> ，那么我想你多半是應該像我一樣，沒有輸入 “;”。

QQ截圖20170811141139.png

通過上圖中的結果我們可以看到sql_mode中有NO_ZERO_IN_DATE和NO_ZERO_DATE，在命令行中輸入 set sql_mode=(select replace(@@sql_mode,

'NO_ZERO_IN_DATE,NO_ZERO_DATE','')); 可以修改當前session的sql_mode。

QQ截圖20170811142804.png

之后可以查看一下sql_mode的值。可以發現已經成功去掉了NO_ZERO_IN_DATE和NO_ZERO_DATE。

QQ截圖20170811142815.png

重新運行了一下建表的sql語句，發現沒什么卵用，依然Err 1067。不要想什么姿勢不對的問題了，只是全局的sql_mode沒有設置而已，而這里設置的sql_mode對大局根本沒有影響。

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

QQ截圖20170811144154.png

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

QQ截圖20170811144607.png

完成設置之后可以在Navicat中重新運行一下sql語句了，不過在那之前要先重新連接數據庫，不然依然Rrr 1067。別問我為什么，叫我雷鋒就好。運行結果如下：

QQ截圖20170811144805.png

好了，這次表是創建成功了的，而且換了一個錯誤Err 1055，翻譯過來就是“無法給包含一個非聚合的列information_schema.PROFILING.SEQ進行分組，這個功能不再依賴分組，且與新的規則不兼容sql_mode=only_full_group_by”。也說了這是由于sql_mode中的“ONLY_FULL_GROUP_BY”導致的。可以再次修改sql_mode。

QQ截圖20170811145725.png

刪除之前創建好的表，重新連接數據庫，運行sql語句，然后就棒棒棒了。

QQ截圖20170811145933.png

當然這種解決方法只是揚湯止沸，一旦重啟mysql數據庫，之前費了狼勁設置的一堆值一夜回到解放前。

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

三 設置sql_mode

可以通過修改配置文件的方式設置sql_mode，這樣在數據庫重啟之后sql_mode的值也不會改變。

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

mysqld --verbose –help可以看到，不過這個命令的輸出結果太長了，我暫時沒有找到更合適的命令來查看。加載順序如圖：

QQ截圖20170811151737.png

這些配置文件在加載時，后加載的會將之前加載的配置文件中的相同的值覆蓋。不過我只在mysql的安裝目錄下找到了一個名字很像的配置文件，其余的都沒有找到。

QQ截圖20170811152424.png

將這個文件備份好之后，修改名稱為my.ini，與給出的加載配置文件順序中的文件對應。然后打開文件，我這里的配置文件中的sql_mode只有兩個值。

QQ截圖20170811152716.png

重啟數據庫后，使用命令查看sql_mode的值，發現與配置文件中完全吻合，搞定！

QQ截圖20170811152945.png

附加幾種常見的sql_mode值的介紹：

幾種常見的mode介紹

ONLY_FULL_GROUP_BY：

出現在select語句、HAVING條件和ORDER BY語句中的列，必須是GROUP BY的列或者依賴于GROUP BY列的函數列。

NO_AUTO_VALUE_ON_ZERO：

該值影響自增長列的插入。默認設置下，插入0或NULL代表生成下一個自增長值。如果用戶

希望插入的值為0，而該列又是自增長的，那么這個選項就有用了。

STRICT_TRANS_TABLES：

在該模式下，如果一個值不能插入到一個事務表中，則中斷當前的操作，對非事務表不做限制

NO_ZERO_IN_DATE：

這個模式影響了是否允許日期中的月份和日包含0。如果開啟此模式，2016-01-00是不允許的，但是0000-02-01是允許的。它實際的行為受到 strict mode是否開啟的影響1。

NO_ZERO_DATE：

設置該值，mysql數據庫不允許插入零日期。它實際的行為受到 strict

mode是否開啟的影響2。

ERROR_FOR_DIVISION_BY_ZERO：

在INSERT或UPDATE過程中，如果數據被零除，則產生錯誤而非警告。如

果未給出該模式，那么數據被零除時MySQL返回NULL

NO_AUTO_CREATE_USER：

禁止GRANT創建密碼為空的用戶

NO_ENGINE_SUBSTITUTION：

如果需要的存儲引擎被禁用或未編譯，那么拋出錯誤。不設置此值時，用默認的存儲引擎替代，并拋出一個異常

PIPES_AS_CONCAT：

將”||”視為字符串的連接操作符而非或運算符，這和Oracle數據庫是一樣的，也和字符串的拼接函數Concat相類似

ANSI_QUOTES：

啟用ANSI_QUOTES后，不能用雙引號來引用字符串，因為它被解釋為識別符