MySQL sql_mode 說明（及處理一起 sql_mode 引發的問題）

最近測試組那邊反應資料庫部分寫入失敗，app層提示是插入成功，但表裡面裡面沒有産生資料，而兩個寫入操作的另外一個表有資料。因為 insert 失敗在資料庫層面是看不出來的，于是找php的同僚看下錯誤資訊：

很明顯2個 insert 操作，第一條成功，第二條失敗了，但因為沒有控制在一個事務當中，導緻app裡面依然提示成功，這是客戶入庫操作，心想如果線上也有這個問題得是多大的代價。

不說開發的問題，好端端的mysql怎麼突然就部分表寫入失敗呢？根據上面的問題很快能猜到是 sql_mode 問題： not null 列沒有預設值但代碼裡也沒給值，在非嚴格模式下，int列預設為0，string列預設為''了，是以不成問題；但在嚴格模式下，是直接傳回失敗的。

一看，果然：

但是一直是沒問題的的，就突然出現了，有誰會去改 sql_mode 呢，生産環境産生這個問題的風險有多大？是以必須揪出來。

先 <code>set global sql_mode=''</code> ，讓他們用着先（文後會給解決問題根本的辦法），同時打開general_log看是哪一個使用者有類似設定 sql_mode 指令：

看出是java組那邊哪個架構建立連接配接的時候使用設定了sql_mode，但這是session級别的，不影響php那邊使用者的連接配接。

那會是什麼原因在 set global 之後又變回strict模式呢，于是想到 mysqld_safe 啟動實際是一個保護程序，在mysqld異常停止之後會拉起來，會不會中間有異常導緻 mysqld 重新開機，緻使 global 失效？看了mysql錯誤日志，才想到前些天斷過電，是以決定直接改 <code>/etc/my.cnf</code>配置：

了解一下mysql配置檔案的加載順序：

mysql按照上面的順序加載配置檔案，後面的配置項會覆寫前面的。最後終于在 <code>/usr/my.cnf</code> 找到有一條<code>sql_mode=no_engine_substitution,strict_trans_tables</code>，把這個檔案删掉，/etc/my.cnf 裡面的就生效了。

但是目前沒能整明白的是，mysql運作這麼長時間怎麼突然在<code>/usr</code> （mysql_base）下多個my.cnf，也不像人為建立的。其它執行個體也沒這樣的問題。

類似還出現過一例：存儲過程裡把 '' 傳給int型的，嚴格模式是不允許，而非嚴格模式隻是一個warning。（指令行執行完語句後，<code>show warnings</code> 可看見）

那麼解決這類問題的終極（推薦）辦法其實是，考慮到資料的相容性和準确性，mysql就應該運作在嚴格模式下！無論開發環境還是生産環境，否則代碼移植到線上可能産生隐藏的問題。

sql_mode 問題可以很簡單，也可以很複雜。曾經在一個交流群裡看到有人提到，主從sql_mode設定不一緻導緻複制異常，這裡自己正好全面了解一下幾個常用的值，友善以後排除問題多個方向。

sql文法支援類

<code>only_full_group_by</code>

對于group by聚合操作，如果在select中的列、having或者order by子句的列，沒有在group by中出現，那麼這個sql是不合法的。是可以了解的，因為不在 group by 的列查出來展示會有沖突。

在5.7中預設啟用，是以在實施5.6更新到5.7的過程需要注意：

<code>ansi_quotes</code>

啟用 ansi_quotes 後，不能用雙引号來引用字元串，因為它被解釋為識别符，作用與 ` 一樣。

設定它以後，<code>update t set f1="" ...</code>，會報 unknown column '' in 'field list 這樣的文法錯誤。

<code>pipes_as_concat</code>

将 <code>||</code> 視為字元串的連接配接操作符而非 或 運算符，這和oracle資料庫是一樣的，也和字元串的拼接函數 concat() 相類似

<code>no_table_options</code>

使用 <code>show create table</code> 時不會輸出mysql特有的文法部分，如 <code>engine</code> ，這個在使用 mysqldump 跨db種類遷移的時候需要考慮。

<code>no_auto_create_user</code>

字面意思不自動建立使用者。在給mysql使用者授權時，我們習慣使用 <code>grant ... on ... to dbuser</code> 順道一起建立使用者。設定該選項後就與oracle操作類似，授權之前必須先建立使用者。5.7.7開始也預設了。

資料檢查類

<code>no_zero_date</code>

認為日期 '0000-00-00' 非法，與是否設定後面的嚴格模式有關。

1.如果設定了嚴格模式，則 no_zero_date 自然滿足。但如果是 insert ignore 或 update ignore，'0000-00-00'依然允許且隻顯示warning

2.如果在非嚴格模式下，設定了<code>no_zero_date</code>，效果與上面一樣，'0000-00-00'允許但顯示warning；如果沒有設定<code>no_zero_date</code>，no warning，當做完全合法的值。

3.<code>no_zero_in_date</code>情況與上面類似，不同的是控制日期和天，是否可為 0 ，即 <code>2010-01-00</code> 是否合法。

<code>no_engine_substitution</code>

使用 <code>alter table</code>或<code>create table</code> 指定 engine 時， 需要的存儲引擎被禁用或未編譯，該如何處理。啟用<code>no_engine_substitution</code>時，那麼直接抛出錯誤；不設定此值時，create用預設的存儲引擎替代，atler不進行更改，并抛出一個 warning .

<code>strict_trans_tables</code>

設定它，表示啟用嚴格模式。

注意 <code>strict_trans_tables</code> 不是幾種政策的組合，單獨指 <code>insert</code>、<code>update</code>出現少值或無效值該如何處理:

1.前面提到的把 '' 傳給int，嚴格模式下非法，若啟用非嚴格模式則變成0，産生一個warning

2.out of range，變成插入最大邊界值

3.a value is missing when a new row to be inserted does not contain a value for a non-null column that has no explicit default clause in its definition

sql_mode一般來說很少去關注它，沒有遇到實際問題之前不會去啟停上面的條目。我們常設定的 sql_mode 是 <code>ansi</code>、<code>strict_trans_tables</code>、<code>traditional</code>，ansi和traditional是上面的幾種組合。

<code>ansi</code>：更改文法和行為，使其更符合标準sql

相當于real_as_float, pipes_as_concat, ansi_quotes, ignore_space

<code>traditional</code>：更像傳統sql資料庫系統，該模式的簡單描述是當在列中插入不正确的值時“給出錯誤而不是警告”。

相當于 strict_trans_tables, strict_all_tables, no_zero_in_date, no_zero_date, error_for_division_by_zero, no_auto_create_user, no_engine_substitution

<code>oracle</code>：相當于 pipes_as_concat, ansi_quotes, ignore_space, no_key_options, no_table_options, no_field_options, no_auto_create_user

無論何種mode，産生error之後就意味着單條sql執行失敗，對于支援事務的表，則導緻目前事務復原；但如果沒有放在事務中執行，或者不支援事務的存儲引擎表，則可能導緻資料不一緻。mysql認為，相比直接報錯終止，資料不一緻問題更嚴重。于是 <code>strict_trans_tables</code> 對非事務表依然盡可能的讓寫入繼續，比如給個"最合理"的預設值或截斷。而對于 <code>strict_all_tables</code>，如果是單條更新，則不影響，但如果更新的是多條，第一條成功，後面失敗則會出現部分更新。

5.6.6 以後版本預設就是<code>no_engine_substitution,strict_trans_tables</code>，5.5預設為 '' 。

檢視

設定

配置檔案裡面設定<code>sql-mode=""</code> 。

<a href="https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict">mysql manual sql-mode</a>

<a href="http://xstarcd.github.io/wiki/mysql/mysql-sql-mode.html">mysql的sql_mode合理設定</a>

<a href="http://dba.stackexchange.com/questions/109053/set-sql-mode-blank-after-upgrading-to-mysql-5-6">set-sql-mode-blank-after-upgrading-to-mysql-5-6</a>

<a href="http://blog.itpub.net/29773961/viewspace-1813501/">mysql sql_mode詳解</a>