MySQL小密訣 - 如何以SQL_MODE使資料輸入更有效率:

透過MySQL的SQL_MODE參數可以影響MySQL的行為,使我們在輸入/匯入或更改資料時能使MySQL依我們希望的方式來處理資料.這個參數可在my.cnf或mysqld開機參數設定到整個資料庫,或在登入MySQL後以”set SQL_MODE =;”的方式臨時改變MySQL對當下的連線的行為模式.這些SQL_MODE中有指定個別特性的,也可組合性的以一個MODE代表好幾個特性的組合,同一個MySQL instance或連線可以有多個SQL_MODE組合,例如”set SQL_MODE=ANSI,NO_ENGINESUBSTITUTE,STRICT_TRANS_TABLES;”.這些MODE可以使資料操作(例如自外部系統匯入資料)更方便,相反的也可能造成使用者的混肴或對MySQL的誤解.主要的指定個別特性的SQL_MODE如下:

ALLOW_INVALID_DATES
使MySQL只會檢查月份是否在1到12之間,日期是否在1到31之間,而不會檢查整個日期的合理性,例如您想要存入前端使用者輸入的一模一樣的日期資料,而此時使用者輸入的日期是”2014-04-31”,如果沒將此SQL_MODE設起來,此值會轉成”0000-00-00”並傳出一個warning.

ANSI_QUOTES
能把“"”當成辨識字的引號就如同“`”一樣,在輸入的資料中有“字符時能使它順利輸入到MySQL.

ERROR_FOR_DIVISION_BY_ZERO
如果沒設此MODE以0當分母的算式會傳回NULL,也不會有warning.

HIGH_NOT_PRECEDENCE
運算式最前面的 “NOT”是否要優先處理,這和對舊版MySQL的向上相容有關,預設是NOT (boolean預算),若打開此MODE則NOT會加入boolean式運算的第一個運萛中
,以下例解釋:
#以下相當於NOT (1 BETWEEN -5 AND 5)
    mysql> SET sql_mode = '';
    mysql> SELECT NOT 1 BETWEEN -5 AND 5;
            -> 0
#以下相當於(NOT 1) BETWEEN -5 AND 5
    mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
    mysql> SELECT NOT 1 BETWEEN -5 AND 5;
            -> 1

IGNORE_SPACE
使funciton名和”(“間可否有空白,內建function名當成保留字,要以保留字當成辨識字必需在期前後加上引號,例如
    mysql> CREATE TABLE count (i INT);
    ERROR 1064 (42000): You have an error in your SQL syntax
#加上引號後:
    mysql> CREATE TABLE `count` (i INT);
    Query OK, 0 rows affected (0.00 sec)

NO_AUTO_CREATE_USER
下GRANT指令時如果指定的使用者不存在時預設會建一個新的使用者,以此mode將此功能關閉.

NO_AUTO_VALUE_ON_ZERO
在插入AUTO_INCREMENT欄位時的值為0或NULL值可產生自動跳號的值插入該欄位中,以此mode會使0值留下來而不會跳號,只有NULL值才會產生跳號值.

NO_BACKSLASH_ESCAPES
如果SQL指令中的值有'\'值會和MySQL的跳脫符號衝到,以此mode可使MySQL忽略跳脫符號,將'\'當成值插入欄位中.

NO_DIR_IN_CREATE
建表時忽略 INDEX DIRECTORY和DATA DIRECTORY, 當主從資料庫間的目錄位置不一樣時,在從服務器上有以此mode解決這個問題.

NO_ENGINE_SUBSTITUTION
如果沒設這個mode,建表時如果指定的storage engine不存在,會有warning,再以default storage engine來建這個表.

NO_FIELD_OPTIONS
下SHOW CREATE TABLE時不顯示MySQL特有的欄位選項,以使mysqldump產生的檔案較有可攜性.

NO_KEY_OPTIONS
下SHOW CREATE TABLE時不顯示MySQL特有的key選項,以使mysqldump產生的檔案較有可攜性.

NO_TABLE_OPTIONS
下SHOW CREATE TABLE指令時不顯示MySQL特有的table選項,以使mysqldump產生的檔案較有可攜性.

NO_UNSIGNED_SUBTRACTION
UNSIGNED整數相減預設產生不可以產生負值的結果,此mode可使結果為SIGNED(有負值).

NO_ZERO_DATE
決定日期欄是否可以為’0000-00-00’,5.6.17以後的版本此mode為deprecated.

NO_ZERO_IN_DATE
決定日期欄是否可有0(日或月).

ONLY_FULL_GROUP_BY
規定在SELECT命令中有GROUP BY子句時,SELECT的非總合(aggregate)欄位,HAVING清單中或ORDER BY子句中不可以有非GROUP BY子句沒有指定到的欄位.範仃如下:

    mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
    ERROR 1055 (42000): 't.address' isn't in GROUP BY

    mysql> SELECT name, MAX(age) AS max_age FROM t GROUP BY name
        -> HAVING max_age < 30;
    Empty set (0.00 sec)
    ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause
第二個例子中改寫成HAVING MAX(age)就可執行因為該欄位在aggregate function.

PAD_CHAR_TO_FULL_LENGTH
CHAR欄位中的字串長度如果不到CHAR欄位定義的長度,設此MODE可將最後面補上空白字.例子如下:

    mysql> CREATE TABLE t1 (c1 CHAR(10));
    Query OK, 0 rows affected (0.37 sec)

    mysql> INSERT INTO t1 (c1) VALUES('xy');
    Query OK, 1 row affected (0.01 sec)

    mysql> SET sql_mode = '';
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
    +------+-----------------+
    | c1   | CHAR_LENGTH(c1) |
    +------+-----------------+
    | xy   |               2 |
    +------+-----------------+
    1 row in set (0.00 sec)

    mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
    +------------+-----------------+
    | c1         | CHAR_LENGTH(c1) |
    +------------+-----------------+
    | xy         |              10 |
    +------------+-----------------+
    1 row in set (0.00 sec)

PIPES_AS_CONCAT
在後多資料庫認可以||代表字串連接,MySQL可以此Mode把||當成字串連接功能. ,而不是OR的同意字.

REAL_AS_FLOAT
把REAL當成FLOAT的同意字,沒有此設定,MySQL會將REAL當戶DOUBLE.

STRICT_ALL_TABLES
所有的storage engine,表都以strict mode處理.

STRICT_TRANS_TABLES
使交易型storage engine的表以strict mode處理.

STRICT_TRANS_TABLES
如果無法將值插入一個交易型的表,則放棄此指令.對非交易型的表,如果值出現在單一指令或在多行插入指令的第一個row則放棄此指令

TRADITIONAL
當有指令有錯誤資料時,如同典型資料庫一樣會有”error”而不時只有”warning.

組合的SQL Modes

以下組合幾個單項Mode成為一個SQL Mode,以方便使用,目的多為增加和某些第三方的資料庫的相容性:

ANSI
目當於一起使用REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE

DB2
目當於一起使用Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

MAXDB
目當於一起使用PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

MSSQL
目當於一起使用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.

ORACLE
目當於一起使用PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

POSTGRESQL
目當於一起使用_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

TRADITIONAL
目當於一起使用STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.