MySQL的SQL_MODE

標籤：mysql   sql   mode   

   在MySQL下MySQL所使用的模型SQL_MODE對MySQL來說至關重要，SQL_MODE即用來定義字元超出的操作和模仿別的資料庫的類型，平時通過合理的設定可以MySQL能夠應用於各種情境下，但是如果設定錯誤MySQL的SQL_MODE也會帶來很大的影響，在這裡就簡要的介紹下MySQL下的SQL_MODE，MySQL的SQL_MODE變數時針對session的平時建議設定全域，在設定完後，開啟一個新的session後所指定的SQL_MODE就生效了，同大多數的MySQL變數一樣SQL_MODE僅限於本次MySQL進程，所以在設定完SQL_MODE的全域變數後需要把設定的值寫入my.cnf或my.ini中使得下一次如果重啟MySQL後SQL_MODE生效，查看MySQL當前所使用的SQL_MODE很簡單：

mysql> SET GLOBAL sql_mode = ‘NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_VALUE_ON_ZERO‘;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> SHOW GLOBAL VARIABLES LIKE ‘sql_mode‘;+---------------+------------------------------------------------------------------+| Variable_name | Value                                                            |+---------------+------------------------------------------------------------------+| sql_mode      | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |+---------------+------------------------------------------------------------------+1 row in set (0.01 sec)[[email protected] ~]# vim /etc/my.cnf #在設定完後需要記得在my.cnf或my.ini中修改…略…sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_VALUE_ON_ZERO…略…

而MySQL的SQL_MODE如下：

REAL_AS_FLOAT:將REAL視為FLOAT的同義字，而不是DOUBLE的同義字PIPES_AS_CONCAT:將‘||‘視為字串串連操作符（＋）(同CONCAT())，而不視為ORANSI_QUOTES:使用ANSI標準，即在‘"‘視為識別符引號(‘`‘反引號字元)IGNORE_SPACE:忽略空格，即在使用函數時可以在函數名與‘(‘之間可以有空格，不建議使用這種模式ONLY_FULL_GROUP_BY:group規則，即select的列都要在group中,或者本身是彙總列(SUM,AVG,MAX,MIN)才行，建議加上，這樣在平時就不會出現很多沒有意義錯誤的group by結果查詢出來NO_UNSIGNED_SUBTRACTION:數值無符號化，即可以在int的長度不夠的情況下把負值當成一般字元，不建議使用，在int不夠的情況下可以改成bigint就可以解決NO_DIR_IN_CREATE:在建立表時忽視所有INDEX DIRECTORY和DATA DIRECTORY的選項，該選項對從複製伺服器有用NO_KEY_OPTIONS:不要在SHOW CREATE TABLE的輸出中列印MySQL專用索引選項，該模式在可移植模式（portability mode）下用於mysqldumpNO_TABLE_OPTIONS:不要在SHOW CREATE TABLE的輸出中列印MySQL專用表引選項（例如ENGINE），該模式在可移植模式（portability mode）下用於mysqldumpNO_FIELD_OPTIONS:不要在SHOW CREATE TABLE的輸出中列印MySQL專用列選項，該模式在可移植模式（portability mode）下用於mysqldumpNO_AUTO_VALUE_ON_ZERO:使用MySQL的自增欄位時，MySQL的自增計數器從0開始自增，比較如果沒有加預設是從1開始自增的，如果插入和建表語句中有從0開始會從MySQL中隨機插入自增值，這個模式建議開啟NO_BACKSLASH_ESCAPES:反斜線‘\‘作為一般字元而非轉義符STRICT_TRANS_TABLES:僅對支援事務的表的strict 模式STRICT_ALL_TABLES:對所有表使用strict 模式NO_ZERO_IN_DATE:在strict 模式下，不允許日期和月份為零。如“2011-00-01”和“2011-01-00”這樣的格式是不允許的NO_ZERO_DATE:在strict 模式下，不可以插入形如“0000-00-00 00:00:00”的非法日期ALLOW_INVALID_DATES:合法日期檢查ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE過程中，如果資料被零除(或MOD(X，0))，則產生錯誤(否則為警告)。如果未給出該模式，那麼資料被零除時MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL產生被零除警告，但操作結果為NULLHIGH_NOT_PRECEDENCE:操作符的優先順序是運算式NO_ENGINE_SUBSTITUTION:如果需要的儲存引擎被禁用或未編譯，可以防止自動替換儲存引擎PAD_CHAR_TO_FULL_LENGTH:對於CHAR類型欄位，不要截斷空洞資料(值為0x20的資料)POSTGRESQL:使用PostgreSQL中的標準模式，同PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,POSTGRESQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONSORACLE：使用Oracle中的標準模式，同PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USERMSSQL:使用msSQL中的標準模式，同PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONSDB2:使用DB2中的標準模式，同PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,DB2,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONSMAXDB:使用MaxDB中的標準模式，同PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USERMYSQL323:使用MySQL323的標準模式，同NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCEMYSQL40:使用MySQL323的標準模式，同NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCEANSI:使用ANSI的標準模式，同REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSITRADITIONAL:使用傳統模式，同STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

   總之，在平時需要正確的設定合理的SQL_MODE才能使MySQL到達我們所需要的效果，平如果沒有設定，在MySQL下會有很多不合理的SQL被允許存取，在生產環境中是不被允許的，在生產環境中如果沒有特殊要求的建議用以下模式：

sql_mode=NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY

當然如果還有其他的需要注意的地方還可以根據實際情況修改

本文出自 “Jim的技術隨筆” 部落格，謝絕轉載！

MySQL的SQL_MODE