MySQL Server 的 SQL Modes

MySQL 能設定不同的 SQL 模式(SQL Modes)操作，
不同的 SQL Modes，決定 MySQL 支持的 SQL 語法和資料的驗證方式。
例如在較嚴格的 SQL Modes 下，不合法的資料可能就無法寫入資料庫。


SQL Modes 預設值：
在 5.6.5 和之前的版本，預設值是空的，沒有設置。
在 5.6.6 和之前的版本，預設值是 NO_ENGINE_SUBSTITUTION。


查詢目前 SQL Modes 目前的設定：
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
GLOBAL.sql_mode 是目前伺服器的設定值，SESSION.sql_mode 是目前連線的設定值


設定 SQL Modes 方法有以下三種：

1. 修改設定檔(my.cnf或my.ini)中的 sql-mode="設定值"
2. MySQL啟動時，加參數 --sql-mode="設定值"
3. 連線登入後修改
   SET GLOBAL sql_mode='設定值';
   SET SESSION sql_mode='設定值';
   GLOBAL 是修改目前伺服器的設定值，SESSION 是修改目前連線的設定值

Strict mode (嚴格模式)：
嚴格模式是指「STRICT_TRANS_TABLES」、「STRICT_ALL_TABLES」這兩個模式，至少有使用一個。

• STRICT_TRANS_TABLES：對有交易功能的儲存引擎，開啟嚴格模式，也可能對無交易功能的儲存引擎開啟嚴格模式(註1)。 
• STRICT_ALL_TABLES：對有所有儲存引擎，開啟嚴格模式。
• 註1：
  在「STRICT_TRANS_TABLES」模式中，
  對「無交易功能的儲存引擎」的效果，是看不合法語句是否在最前面出現而決定。
  
  • 當 insert 或 update 的不合法值，是出現在最前面，
    則不管儲存引擎是否有交易功能，都是直接出錯中止。
    例如：INSERT INTO ww (aa) VALUES (333),(44),(22);
    aa 是 tinyint 形態，333 超出範圍，所以直接報錯中止，三個值都無法寫入。
  • 當 insert 或 update 的不合法值，不是出現在最前面。
    例如：INSERT INTO ww2 (aa) VALUES (33),(444),(22);
    aa 是 tinyint 形態
    對「有交易功能的儲存引擎」而言，33、444、22 都無法寫入
    對「無交易功能的儲存引擎」而言，三個值都可以寫入，但444會變改成127，也就是變成 33、127、22
  • 結論： STRICT_TRANS_TABLES 在不合法語句在最前面時，才對 「無交易功能的儲存引擎」有作用。

其他：

1. 在嚴格模式下，可用 INSERT IGNORE、UPDATE IGNORE 將錯誤當成警告，這樣不合法的資料，就會像在沒在嚴格模式下，自動調整再寫入。
2. 如果在非嚴格模式下，想知道寫入的 SQL 是否有因超出範圍，而被自動調整後再寫入。
   可使用 SHOW WARNINGS 指令查看
   指令說明：https://dev.mysql.com/doc/refman/5.6/en/show-warnings.html

參考資料
https://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.1/zh/introduction.html#constraint-invalid-data
http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#server-sql-mode
https://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html#sql-mode-full (可用的 SQL Modes 列表)