卍，設(shè)置和查詢sql_mode： 

       SQL_MODE可能是比較容易讓開發(fā)人員和DBA忽略的一個變量，默認(rèn)為空。SQL_MODE的設(shè)置其實(shí)是比較冒險的一種設(shè)置，因為在這種設(shè)置下可以允許一些非法操作，比如可以將NULL插入NOT NULL的字段中，也可以插入一些非法日期，如“2012-12-32”。因此在生產(chǎn)環(huán)境中強(qiáng)烈建議開發(fā)人員將這個值設(shè)為嚴(yán)格模式，這樣有些問題可以在數(shù)據(jù)庫的設(shè)計和開發(fā)階段就能發(fā)現(xiàn)，而如果在生產(chǎn)環(huán)境下運(yùn)行數(shù)據(jù)庫后發(fā)現(xiàn)這類問題，那么修改的代價將變得十分巨大。此外，正確地設(shè)置SQL_MODE還可以做一些約束(Constraint)檢查的工作。

　　對于SQL_MODE的設(shè)置，可以在MySQL的配置文件如my.cnf和my.ini中進(jìn)行，也可以在客戶端工具中進(jìn)行，并且可以分別進(jìn)行全局的設(shè)置或當(dāng)前會話的設(shè)置。下面的命令可以用來查看當(dāng)前SQL_MODE的設(shè)置情況。

　　可以看到當(dāng)前全局的SQL_MODE設(shè)置為空，而當(dāng)前會話的設(shè)置為NO_UNSIGNED_SUBTRACTION。通過以下語句可以將當(dāng)前的SQL_MODE設(shè)置為嚴(yán)格模式。

　　嚴(yán)格模式是指將SQL_MODE變量設(shè)置為STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的至少一種。現(xiàn)在來看一下SQL_MODE可以設(shè)置的選項。

• 　　STRICT_TRANS_TABLES：在該模式下，如果一個值不能插入到一個事務(wù)表(例如表的存儲引擎為InnoDB)中，則中斷當(dāng)前的操作。不影響非事務(wù)表(例如表的存儲引擎為MyISAM)。
• 　　ALLOW_INVALID_DATES：該選項并不完全對日期的合法性進(jìn)行檢查，只檢查月份是否在1～12之間，日期是否在1～31之間。該模式僅對DATE和DATETIME類型有效，而對TIMESTAMP無效，因為TIMESTAMP總是要求一個合法的輸入。
• 　　ANSI_QUOTES：默認(rèn)情況下，MySQL的關(guān)鍵詞以及表名等都可以有反引號``引起來。啟用ANSI_QUOTES后，便可以用雙引號代替反引號。當(dāng)然原來的反引號依然可用。但是引發(fā)的一個副作用是：不能用雙引號來引用字符串，因為它將被　　解釋為識別符。解決方法就是使用單引號來表示字符串。示例如下：

　　　　mysql> CREATE TABLE z ( a VARCHAR(10))ENGINE=INNODB;

　　　　mysql>INSERT INTO z SELECT "aaa";

　　　　mysql> SET sql_mode='ANSI_QUOTES';

　　　　mysql> INSERT INTO z SELECT "aaa"; // 'aaa'是可以的

　　　　ERROR 1054 (42S22): Unknown column 'aaa' in 'field list'

• 　　ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE過程中，如果數(shù)據(jù)被零除(或MOD(X，0))，則產(chǎn)生錯誤(否則為警告)。如果未給出該模式，那么數(shù)據(jù)被零除時MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE  　　中，MySQL生成被零除警告，但操作結(jié)果為NULL。
• 　　HIGH_NOT_PRECEDENCE NOT：操作符的優(yōu)先順序是表達(dá)式。例如，NOT a BETWEEN b AND c被解釋為NOT(a BETWEEN b AND c)，在一些舊版本MySQL中， 前面的表達(dá)式被解釋為(NOT a)BETWEEN b AND c。啟用  　　HIGH_NOT_PRECEDENCE SQL模式，可以獲得以前舊版本的更高優(yōu)先級的結(jié)果。下面看一個例子：

　    mysql> SELECT 0 BETWEEN -1 AND 1\G;

　　*************************** 1. row ***************************
　　0 BETWEEN -1 AND 1: 1

　　0在-1到1之間，所以返回1，如果加上NOT，則返回0，過程如下：

　　

　　mysql> SELECT @@sql_mode\G;
　　*************************** 1. row ***************************
　　@@sql_mode:
　　

　　mysql> SELECT not 0 BETWEEN -1 AND 1\G;
　　*************************** 1. row ***************************
　　NOT 0 BETWEEN -1 AND 1: 0

　　但是如果啟用HIGH_NOT_PRECEDENCE模式，則SELECT NOT 0 BETWEEN -1 AND 1被解釋為SELECT(NOT 0)BETWEEN -1 AND 1，結(jié)果就完全相反，如下所示：

　　

　　mysql> SELECT NOT 0 BETWEEN -1 AND 1\G;
　　*************************** 1. row ***************************
　　NOT 0 BETWEEN -1 AND 1: 1　　

　　從上述例子中還能看出，在MySQL數(shù)據(jù)庫中BETWEEN a AND b被解釋為[a，b]。下面做兩個簡單的測試。

　　mysql> SELECT 1 BETWEEN -1 AND 1\G;
　　*************************** 1. row ***************************
　　1 BETWEEN -1 AND 1: 1
　　mysql> SELECT -1 BETWEEN -1 AND 1\G;
　　*************************** 1. row ***************************
　　-1 BETWEEN -1 AND 1: 1

• 　　IGNORE_SPACE：  參考 http://www.rzrgm.cn/JiangLe/p/6892831.html
• 　　NO_AUTO_CREATE_USER：禁止GRANT創(chuàng)建密碼為空的用戶。
• 　　NO_AUTO_VALUE_ON_ZERO：該選項影響列為自增長的插入。在默認(rèn)設(shè)置下，如果自增長字段插入了0或NULL，實(shí)際上是生成了下一個自增長值。啟用此選項后，只有NULL可以自動生成下一個序列號。此時自增長字段插入0是可以正常插入的。
• 　　NO_BACKSLASH_ESCAPES：反斜杠“\”作為普通字符而非轉(zhuǎn)義符，示例如下：

　　mysql> SET sql_mode='';
　　mysql> SELECT '\\'\G;
　　*************************** 1. row ***************************
　　\: \　　[一個鍵一個值]
　　mysql> SET sql_mode='NO_BACKSLASH_ESCAPES';
　　mysql> SET '\\'\G;
　　*************************** 1. row ***************************
　　\\: \\

• 　　NO_DIR_IN_CREATE：在創(chuàng)建表時忽視所有INDEX DIRECTORY和DATA DIRECTORY的選項。
• 　　NO_ENGINE_SUBSTITUTION：如果需要的存儲引擎被禁用或未編譯，那么拋出錯誤。默認(rèn)用默認(rèn)的存儲引擎替代，并拋出一個異常。
• 　　NO_UNSIGNED_SUBTRACTION：之前已經(jīng)介紹過，啟用這個選項后兩個UNSIGNED類型相減返回SIGNED類型。
• 　　NO_ZERO_DATE：在非嚴(yán)格模式下，可以插入形如“0000-00-00 00:00:00”的非法日期，MySQL數(shù)據(jù)庫僅拋出一個警告。而啟用該選項后，MySQL數(shù)據(jù)庫不允許插入零日期，插入零日期會拋出錯誤而非警告。
• 　　NO_ZERO_IN_DATE：在嚴(yán)格模式下，不允許日期和月份為零。如“2011-00-01”和“2011-01-00”這樣的格式是不允許的。采用日期或月份為零的格式時MySQL都會直接拋出錯誤而非警告，示例如下：

　　mysql> SET sql_mode='NO_ZERO_IN_DATE';
　　mysql> CREATE TABLE a ( a DATETIME );
　　mysql> INSERT INTO a SELECT '2011-01-00';
　　ERROR 1292 (22007): Incorrect datetime value: '2011-01-00' for column 'a' at row 1

　　ONLY_FULL_GROUP_BY：對于GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現(xiàn)，那么這個SQL是不合法的，因為列不在GROUP BY從句中，所以對于設(shè)置了這個mode的數(shù)據(jù)庫，在使用group by 的時候，就要用MAX(),SUM(),ANT_VALUE()這種聚合函數(shù)，才能完成GROUP BY 的聚合操作。。示例如下：

　　mysql> SET sql_mode='ONLY_FULL_GROUP_BY';
　　mysql> SELECT a,SUM(b) FROM t GROUP BY b;
　　ERROR 1055 (42000): 'test.t.a' isn't in GROUP BY

• 　　PAD_CHAR_TO_FULL_LENGTH：對于CHAR類型字段，不要截斷空洞數(shù)據(jù)?？斩磾?shù)據(jù)就是自動填充值為0x20的數(shù)據(jù)。先來看MySQL數(shù)據(jù)庫在默認(rèn)情況下的表現(xiàn)。

　　mysql> CREATE TABLE t ( a CHAR(10) );
　　mysql> INSERT INTO t SELECT 'a';
　　mysql>SELECT a,CHAR_LENGTH(a),HEX(a) FROM t\G;
　　*************************** 1. row ***************************
　　a: a
　　CHAR_LENGTH(a): 1
　　HEX(a): 61
　　1 row in set (0.04 sec)

　　可以看到，在默認(rèn)情況下，雖然a列是CHAR類型，但是返回的長度是1，這是因為MySQL數(shù)據(jù)庫已經(jīng)對后面的空洞數(shù)據(jù)進(jìn)行了截斷。若啟用PAD_CHAR_TO_FULL_LENGTH選項，則反映的是實(shí)際存儲的內(nèi)容，例如：

　　mysql> SELECT a,CHAR_LENGTH(a),HEX(a) FROM t\G;
　　*************************** 1. row ***************************
　　a: a
　　CHAR_LENGTH(a): 10
　　HEX(a): 61202020202020202020
　　1 row in set (0.00 sec)

　　可以看到在CHAR列a中實(shí)際存儲的值為0x61202020202020202020。

• 　　PIPES_AS_CONCAT：將“||”視為字符串的連接操作符而非或運(yùn)算符，這和Oracle數(shù)據(jù)庫是一樣的，也和字符串的拼接函數(shù)Concat相類似，例如：

　　mysql> SET sql_mode='pipes_as_concat';
　　mysql> SELECT 'a'||'b'||'c'\G;
　　*************************** 1. row ***************************
　　'a'||'b'||'c': abc
　　1 row in set (0.00 sec)

• 　　REAL_AS_FLOAT：將REAL視為FLOAT的同義詞，而不是DOUBLE的同義詞。
• 　　STRICT_ALL_TABLES：對所有引擎的表都啟用嚴(yán)格模式。(STRICT_TRANS_TABLES只對支持事務(wù)的表啟用嚴(yán)格模式)。

　　在嚴(yán)格模式下，一旦任何操作的數(shù)據(jù)產(chǎn)生問題，都會終止當(dāng)前的操作。對于啟用STRICT_ALL_TABLES選項的非事務(wù)引擎來說，這時數(shù)據(jù)可能停留在一個未知的狀態(tài)。這可能不是所有非事務(wù)引擎愿意看到的一種情況，因此需要非常小心這個選項可能帶來的潛在影響。

　　下面的幾種SQL_MODE設(shè)置是之前討論的幾種選項的組合。

• 　　ANSI：等同于REAL_AS_FLOAT、PIPES_AS_CONCAT和ANSI_QUOTES、IGNORE_SPACE的組合。
• 　　ORACLE：等同于PIPES_AS_CONCAT、 ANSI_QUOTES、IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS和NO_AUTO_CREATE_USER的組合。
• 　　TRADITIONAL：等同于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的組合。
• 　　MSSQL：等同于PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS和 NO_FIELD_OPTIONS的組合。
• 　　DB2：等同于PIPES_AS_CONCAT、ANSI_QUOTES、 IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS和NO_FIELD_OPTIONS的組合。
• 　　MYSQL323：等同于NO_FIELD_OPTIONS和HIGH_NOT_PRECEDENCE的組合。
• 　　MYSQL40：等同于NO_FIELD_OPTIONS和HIGH_NOT_PRECEDENCE的組合。
• 　　MAXDB：等同于PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS和 NO_AUTO_CREATE_USER的組合。