[DB][MySQL] SQL-Mode的設定

[DB][MySQL] SQL-Mode的設定

定義

  1. MySQL應支援的SQL語法
  2. 資料上執行確認檢查

SQL語法支援類

  • ONLY_FULL_GROUP_BY

    對於GROUP BY聚合操作,如果在SELECT中的列、HAVING或者ORDER BY子句的列,沒有在GROUP BY中出現,那麼這個SQL是不合法的。是可以理解的,因為不在 group by 的列查出來展示會有矛盾。在5.7中默認啟用,所以在實施5.6升級到5.7的過程需要注意

  • ANSI_QUOTES

    啟用 ANSI_QUOTES 後,不能用雙引號來引用字串,因為它被解釋為識別符,作用與 ` 一樣。 設置它以後,update t set f1="" …,會報 Unknown column ‘’ in ‘field list 這樣的語法錯誤

  • PIPES_AS_CONCAT

    將 || 視為字串的連接操作符而非 或(OR)運算子,這和Oracle資料庫是一樣的,也和字串的拼接函數 CONCAT() 相類似

  • NO_TABLE_OPTIONS

    使用 SHOW CREATE TABLE 時不會輸出MySQL特有的語法部分,如 ENGINE ,這個在使用 mysqldump 跨DB種類遷移的時候需要考慮

  • NO_AUTO_CREATE_USER

    字面意思不自動創建用戶。在給MySQL用戶授權時,我們習慣使用 GRANT … ON … TO dbuser順道一起創建用戶。設置該選項後就與oracle操作類似,授權之前必須先建立用戶。5.7.7開始也默認了

資料檢查類

  • NO_ZERO_DATE

    認為日期 ‘0000-00-00’ 非法,與是否設置後面的嚴格模式有關。

    1. 如果設置了嚴格模式,則 NO_ZERO_DATE 自然滿足。但如果是 INSERT IGNORE 或 UPDATE IGNORE,’0000-00-00’依然允許且只顯示warning
    2. 如果在非嚴格模式下,設置了NO_ZERO_DATE,效果與上面一樣,’0000-00-00’允許但顯示warning;如果沒有設置NO_ZERO_DATE,no warning,當做完全合法的值。 3.NO_ZERO_IN_DATE情況與上面類似,不同的是控制日期和天,是否可為 0 ,即 2010-01-00 是否合法
  • NO_ENGINE_SUBSTITUTION

    使用 ALTER TABLE或CREATE TABLE 指定 ENGINE 時, 需要的存儲引擎被禁用或未編譯,該如何處理。啟用NO_ENGINE_SUBSTITUTION時,那麼直接拋出錯誤;不設置此值時,CREATE用預設的存儲引擎替代,ATLER不進行更改,並拋出一個 warning

  • STRICT_TRANS_TABLES

    設置它,表示啟用嚴格模式。 注意 STRICT_TRANS_TABLES 不是幾種策略的組合,單獨指 INSERT、UPDATE出現少值或無效值該如何處理:

    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 是 ANSI、STRICT_TRANS_TABLES、TRADITIONAL,ansi和traditional是上面的幾種組合

  • ANSI:

    更改語法和行為,使其更符合標準SQL 相當於REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE

  • TRADITIONAL:

    更像傳統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

  • ORACLE:

    相當於 PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER

相關指令

查看當前配置

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
SELECT @@sql_mode;

SHOW VARIABLES LIKE "sql_mode";

修改配置

服務運行中

## 一般設定
SET GLOBAL sql_mode = 'modes...';
SET SESSION sql_mode = 'modes...';

## 新增設定的整合方法
# 新增
SET GLOBAL sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));
SET SESSION sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));

# 取代
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

設定檔

[mysqld]
#set the SQL mode to strict
#sql-mode="modes..." 
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Reference

5.1.10 Server SQL Modes

SQL_MODE

mysql的sql_mode合理设置

Mysql only_full_group_by以及其他關於sql_mode原因報錯詳細解決方案

MySQL sql_mode 说明(及处理一起 sql_mode 引发的问题)

Add a Comment