ApsaraDB RDS：設定RDS MySQL的sql_mode參數

組合SQL模式名稱

解釋

DB2

等同於PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS

MSSQL

等同於PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS

POSTGRESQL

等同於PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS

ORACLE

等同於PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER

MAXDB

等同於PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER

MYSQL323

等同於HIGH_NOT_PRECEDENCE加上一些特定於MYSQL323的SHOW CREATE TABLE行為：

• TIMESTAMP列顯示不包括DEFAULT或ON UPDATE屬性。

• 字串列顯示不包括字元集和定序屬性。對於CHAR和VARCHAR列，如果定序是二進位的，則會將binary附加到列類型中。

• ENGINE=engine_name表選項顯示為TYPE=engine_name。

• 對於MEMORY表，儲存引擎顯示為HEAP。

MYSQL40

等同於HIGH_NOT_PRECEDENCE加上一些特定於MYSQL323的SHOW CREATE TABLE行為：

• TIMESTAMP列顯示不包括DEFAULT或ON UPDATE屬性。

• 字串列顯示不包括字元集和定序屬性。對於CHAR和VARCHAR列，如果定序是二進位的，則會將binary附加到列類型中。

• ENGINE=engine_name表選項顯示為TYPE=engine_name。

ANSI

等同於REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY

TRADITIONAL

• 對於MySQL 5.7.3及之前版本，以及MySQL 5.7.8及更高版本，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。

• 對於MySQL 5.7.4到MySQL 5.7.7之間的版本，TRADITIONAL等同於STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION。

參數值

版本支援說明

含義

ALLOW_INVALID_DATES

MySQL 5.6、5.7和8.0均支援

允許插入或更新無效的日期值，即不符合日期格式的值。

在預設的strict 模式下，MySQL要求日期值必須符合指定的格式，例如YYYY-MM-DD。如果插入或更新的日期值格式不正確，MySQL會報錯並拒絕這個操作。

但是，當設定sql_mode為ALLOW_INVALID_DATES時，MySQL允許插入或更新不符合日期格式的值，不會報錯。

樣本：

假設有一個日期欄位"birthday"，在strict 模式下，只能插入YYYY-MM-DD格式的值，否則會報錯。但是，當設定sql_mode為ALLOW_INVALID_DATES時，可以插入如"2022-13-45"這樣的無效日期值，MySQL會將其儲存為"0000-00-00"。這在某些特定的業務情境下可能是需要的，例如需要記錄一些無效日期的情況。

ANSI_QUOTES

MySQL 5.6、5.7和8.0均支援

用於啟用ANSI_QUOTES模式，該模式下MySQL將使用雙引號（""）或反引號（``）來引用標識符。

在使用sql_mode預設值時，MySQL使用單引號（''）來引用字串值，使用反引號（``）來引用標識符（如表名、列名）。例如：

SELECT * FROM users WHERE `name` = 'John';

而當設定sql_mode為ANSI_QUOTES時，MySQL使用單引號（''）來引用字串值，使用雙引號（""）或反引號（``）來引用標識符，以符合ANSI SQL標準。例如：

SELECT * FROM "users" WHERE `name` = 'John';

啟用ANSI_QUOTES模式可以增加與ANSI SQL標準的相容性，特別是在與其他資料庫系統互動時，可以更好地保證語句的可移植性和一致性。

ERROR_FOR_DIVISION_BY_ZERO

MySQL 5.6、5.7和8.0均支援

用於在進行除零運算時發出警告（或者錯誤）而不是返回NULL值。

ERROR_FOR_DIVISION_BY_ZERO模式影響除零操作的處理，其中包括MOD(N,0)。

對於資料更改操作（INSERT、UPDATE），其效果還取決於是否啟用了嚴格的SQL模式（strict SQL mode）：

• 如果未啟用該模式，則除以零會插入NULL並不會產生警告。

• 如果啟用了該模式，則除以零會插入NULL併產生警告。

• 如果啟用了該模式和嚴格的SQL模式，則除以零會產生錯誤，除非同時使用了IGNORE。對於INSERT IGNORE和UPDATE IGNORE，除以零會插入NULL併產生警告。

對於SELECT操作，除以零會返回NULL。啟用ERROR_FOR_DIVISION_BY_ZERO會產生警告，而不管是否啟用了嚴格的SQL模式。

ERROR_FOR_DIVISION_BY_ZERO不屬於嚴格的SQL模式的一部分，但應與strict 模式一起使用，並且預設情況下該參數是啟用的。如果在未啟用strict 模式的情況下啟用了ERROR_FOR_DIVISION_BY_ZERO，或者未啟用ERROR_FOR_DIVISION_BY_ZERO的情況下，啟用了strict 模式，將產生警告。

HIGH_NOT_PRECDENCE SQL

MySQL 5.6、5.7和8.0均支援

用於提高NOT運算子的優先順序。

預設情況下，NOT的優先順序低於比較子，例如，NOT a BETWEEN b AND c運算式被解析為NOT（a BETWEEN b AND c）。

在一些舊版本的MySQL中，NOT a BETWEEN b AND c運算式被解析為（NOT a）BETWEEN b AND c。

可以通過HIGH_NOT_PRECEDENCE SQL模式來使NOT運算子的優先順序更高。

IGNORE_SPACE

MySQL 5.6、5.7和8.0均支援

允許函數名稱和（字元之間有空格。這會導致內建函數名稱被視為保留字。

IGNORE_SPACE SQL模式適用於內建函數，而不適用於可載入函數或儲存函數。無論是否啟用IGNORE_SPACE，始終允許在可載入函數或儲存函數名稱後面有空格。

NO_AUTO_VALUE_ON_ZERO

MySQL 5.6、5.7和8.0均支援

用于禁止將自動遞增列的預設值設定為0。

在預設情況下，當插入一行資料時，如果自動遞增列的值被設定為0，MySQL會自動將其替換為下一個可用的自動遞增值。然而，當設定sql_mode為NO_AUTO_VALUE_ON_ZERO時，MySQL將禁止將自動遞增列的預設值設定為0。

這個參數的作用是為了避免意外地將0作為自動遞增列的預設值。在某些情況下，將自動遞增列的預設值設定為0可能會導致資料不一致或錯誤的結果。

例如：

CREATE TABLE mytable (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO mytable (id, name) VALUES (0, 'John');

如果NO_AUTO_VALUE_ON_ZERO參數被禁用（即未設定），則MySQL會將id列的值自動替換為下一個可用的自動遞增值（也就是1）。

然而，當設定sql_mode為NO_AUTO_VALUE_ON_ZERO時：

SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
CREATE TABLE mytable (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO mytable (id, name) VALUES (0, 'John');

在這種情況下，MySQL將返回錯誤並拒絕將0作為自動遞增列的預設值。

NO_BACKSLASH_ESCAPES

MySQL 5.6、5.7和8.0均支援

用于禁止使用反斜線（\）進行轉義。

在預設情況下，MySQL允許使用反斜線作為逸出字元，在字串中插入特殊字元或逸出字元本身。例如，可以使用\'表示單引號，\"表示雙引號，\\表示反斜線本身。

然而，當設定sql_mode為NO_BACKSLASH_ESCAPES時，MySQL將禁止使用反斜線進行轉義。這意味著反斜線字元將被視為一般字元，而不是逸出字元。

這個參數的作用是為了避免使用反斜線進行轉義可能引起的混淆和錯誤。有時，使用反斜線進行轉義可能會導致意外的結果，特別是在處理包含大量反斜線的資料時。

例如：

SET sql_mode = 'NO_BACKSLASH_ESCAPES';
SELECT 'It\'s a test';

在預設情況下，上述查詢中的兩個單引號之間的反斜線是用於轉義的。然而，當設定sql_mode為NO_BACKSLASH_ESCAPES時，反斜線將被視為一般字元，而不是逸出字元。因此，上述查詢將返回It\'s a test，而不是It's a test。

NO_DIR_IN_CREATE

MySQL 5.6、5.7和8.0均支援

用於在CREATE TABLE語句中禁止使用DIRECTORY選項。

在預設情況下，CREATE TABLE語句允許指定資料目錄（DATA DIRECTORY）和索引目錄（INDEX DIRECTORY）。然而，當設定sql_mode為NO_DIR_IN_CREATE時，MySQL將禁止在CREATE TABLE語句中使用DIRECTORY選項。

該參數的作用是為了限制或防止在CREATE TABLE語句中指定特定的目錄用於儲存表資料。目錄選項可能會引發安全風險或造成資料管理上的混亂。

例如：

SET sql_mode = 'NO_DIR_IN_CREATE';
CREATE TABLE mytable (
id INT PRIMARY KEY,
name VARCHAR(50)
) DIRECTORY = '/path/to/directory';

如果NO_DIR_IN_CREATE參數被啟用（即設定），則MySQL將返回錯誤並拒絕使用DIRECTORY選項。

在主從複製情境下，可在從庫上開啟該mode。

NO_ENGINE_SUBSTITUTION

MySQL 5.6、5.7和8.0均支援

用於在建立或修改表時禁止使用預設儲存引擎的替代。

在MySQL中，如果在建立或修改表時指定的儲存引擎不可用或不支援，MySQL會自動替換為預設的儲存引擎。然而，當設定sql_mode為NO_ENGINE_SUBSTITUTION時，MySQL將禁止使用預設儲存引擎的替代。

該參數的作用是為了確保使用指定的儲存引擎，並避免意外或不一致的儲存引擎替換。

例如：

SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE mytable (
id INT PRIMARY KEY,
name VARCHAR(50)
) ENGINE = 'NonexistentEngine';

如果NO_ENGINE_SUBSTITUTION參數被啟用（即設定），則MySQL將返回錯誤並拒絕使用指定的不存在的儲存引擎。它將不會自動替換為預設的儲存引擎，並且不會建立或更改表。

NO_UNSIGNED_SUBTRACTION

MySQL 5.6、5.7和8.0均支援

用于禁止在不帶正負號的整數之間執行結果為負數的減法運算。

整數值之間的相減（其中一個為UNSIGNED類型）預設情況下會產生無符號結果。如果結果是負數，則會出現錯誤。

然而，當設定sql_mode為NO_UNSIGNED_SUBTRACTION時，結果可以為負數。

該參數的作用是為了避免在不帶正負號的整數之間執行減法運算時產生意外的結果。在某些情況下，不帶正負號的整數之間的減法運算可能會導致負數的結果，這可能與預期不符。

例如：

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
select cast(0 as unsigned)-1;

如果NO_UNSIGNED_SUBTRACTION參數被啟用（即設定），則結果為-1。

NO_ZERO_DATE

MySQL 5.6、5.7和8.0均支援

用于禁止在日期或日期時間列中使用“0000-00-00”作為合法的零日期值。

在預設情況下，MySQL允許在日期或日期時間列中使用“0000-00-00”作為合法的零日期值。然而，當設定sql_mode為NO_ZERO_DATE時，MySQL將禁止使用“0000-00-00”作為合法的日期值。其效果還取決於是否啟用了嚴格的SQL模式：

• 如果未啟用NO_ZERO_DATE模式，則允許使用“0000-00-00”，並且插入不會產生警告。

• 如果啟用NO_ZERO_DATE模式，則允許使用“0000-00-00”，並且插入會產生警告。

• 如果啟用NO_ZERO_DATE模式和嚴格的SQL模式，則不允許使用“0000-00-00”，並且插入會產生錯誤，除非也給出IGNORE。對於INSERT IGNORE和UPDATE IGNORE，允許使用“0000-00-00”，並且插入會產生警告。

該參數的作用是為了避免使用無效或不合適的日期值。“0000-00-00”並不是一個真實存在的日期，因此禁止使用它可以避免出現混亂和不正確的結果。

例如：

SET sql_mode = 'NO_ZERO_DATE';
INSERT INTO mytable (id, date_column) VALUES (1, '0000-00-00');

如果NO_ZERO_DATE參數被啟用（即設定），則MySQL將返回錯誤並拒絕將“0000-00-00”插入到date_column列中。

NO_ZERO _IN_DATE

MySQL 5.6、5.7和8.0均支援

用於控制是否允許年的部分為非零，但月或日的部分為0的日期。

NO_ZERO _IN_DATE的效果還取決於是否啟用了嚴格的SQL模式：

• 如果未啟用NO_ZERO _IN_DATE模式，則允許日期中包含零，並且插入不會產生任何警告。

• 如果啟用NO_ZERO _IN_DATE模式，則包含零的日期將插入為“0000-00-00”併產生警告。

• 如果啟用NO_ZERO _IN_DATE模式和嚴格的SQL模式，則不允許包含零的日期，並且插入會產生錯誤，除非也給出IGNORE。對於INSERT IGNORE和UPDATE IGNORE，包含零的日期插入為“0000-00-00”併產生警告。

ONLY_FULL_GROUP_BY

MySQL 5.6、5.7和8.0均支援

用於設定GROUP BY語句的strict 模式。

在預設情況下，MySQL允許在GROUP BY查詢中，SELECT列表中的列不被GROUP BY子句包含。這種行為屬於MySQL的非標準擴充。例如，以下查詢在預設模式下是合法的：

SELECT id, name, MAX(score)
FROM mytable
GROUP BY id;

在這個查詢中，name列並沒有被包含在GROUP BY子句中，但MySQL仍然返回了結果。這種行為可能導致結果的不確定性，因為在不同資料庫中可能有不同的彙總規則。

當設定sql_mode為ONLY_FULL_GROUP_BY時，MySQL將啟用GROUP BY的strict 模式，要求在SELECT列表中的每個非彙總列都必須在GROUP BY子句中出現。如果不符合要求，將出現報錯1055 (42000): SELECT list is not in GROUP BY clause and contains nonaggregated column。

例如，在ONLY_FULL_GROUP_BY模式下，以下查詢會返回錯誤：

SET sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT id, name, MAX(score)
FROM mytable
GROUP BY id;

由於name列未包含在GROUP BY子句中，因此MySQL會返回錯誤。

通過啟用ONLY_FULL_GROUP_BY模式，可以確保編寫的查詢語句遵循標準的GROUP BY規則，提高查詢結果的準確性和可靠性。

PAD_CHAR_TO_FULL_LENGTH

MySQL 5.6、5.7和8.0均支援

用於控制不刪除CHAR類型欄位的尾隨空格。

在對CHAR欄位進行儲存時，在Compact格式下，會佔用固定長度的位元組。預設情況下，檢索時會從CHAR列值中刪除尾部空格。

如果啟用了PAD_CHAR_TO_FULL_LENGTH，則不會刪除，並且檢索到的CHAR值將填充到其全長。

PIPES_AS_CONCAT

MySQL 5.6、5.7和8.0均支援

將||視為字串串聯運算子（作用與CONCAT()相同），而不是OR的同義字。

REAL_AS_FLOAT

MySQL 5.6、5.7和8.0均支援

將REAL視為FLOAT的同義字。

預設情況下，MySQL將REAL視為DOUBLE的同義字。

STRICT_ALL_TABLES

MySQL 5.6、5.7和8.0均支援

為所有儲存引擎啟用嚴格的SQL模式，無效的資料值會被拒絕。

在預設情況下，MySQL允許一些隱式的資料類型轉換和插入、更新操作中的警告。但是，當設定sql_mode為STRICT_ALL_TABLES時，MySQL會啟用更嚴格的模式，要求更嚴格的資料類型匹配和插入、更新操作。

啟用STRICT_ALL_TABLES模式後，包括但不限於以下情況將觸發錯誤：

• 資料類型不匹配：如果在插入或更新操作中對列賦值，資料類型不匹配，MySQL將返回錯誤。

• 非空列：如果在插入或更新操作中，非空列沒有提供值，MySQL將返回錯誤。

• 無效的日期或時間：如果在插入或更新操作中提供了無效的日期或時間值，MySQL將返回錯誤。

• 非零預設值：如果在插入操作中，列的預設值不是0或NULL，並且沒有為該列提供值，則MySQL將返回錯誤。

使用STRICT_ALL_TABLES模式可以協助開發人員在資料庫操作中更好地遵循資料類型規範和約束，從而提高資料的完整性和一致性。

STRICT_TRANS_TABLES

MySQL 5.6、5.7和8.0均支援

為事務儲存引擎啟用嚴格的SQL模式。具體解釋參見上文的STRICT_ALL_TABLES。

NO_AUTO_CREATE_USER

• MySQL 5.6、5.7支援

• MySQL 8.0不支援

用于禁止在GRANT語句中自動建立使用者。

在預設情況下，MySQL允許在GRANT語句中建立新使用者，即當使用GRANT語句指定的使用者不存在時，MySQL會自動建立該使用者。然而，當設定sql_mode為NO_AUTO_CREATE_USER時，MySQL將禁止自動建立使用者。

該參數的作用是在安全性方面提供更嚴格的控制。通過禁止自動建立使用者，可以確保只有已經存在的使用者才能被授權訪問資料庫。

例如：

GRANT SELECT ON mydb.* TO 'new_user'@'localhost';

如果new_user使用者不存在，但NO_AUTO_CREATE_USER參數被禁用（即未設定），則MySQL會自動建立new_user使用者並授予SELECT許可權。

然而，當設定sql_mode為NO_AUTO_CREATE_USER時，如果new_user使用者不存在，MySQL將返回錯誤並拒絕建立該使用者。

SET sql_mode = 'NO_AUTO_CREATE_USER';
GRANT SELECT ON mydb.* TO 'new_user'@'localhost';

需要注意的是，NO_AUTO_CREATE_USER參數僅適用於GRANT語句中的自動使用者建立，不會影響手動建立使用者的操作或其他與使用者相關的操作。

NO_FIELD_OPTIONS

• MySQL 5.6、5.7支援

• MySQL 8.0不支援

不允許在SHOW CREATE TABLE的輸出中列印MySQL特定的列選項。此模式由mysqldump在可移植模式下使用。

NO_KEY_OPTIONS

• MySQL 5.6、5.7支援

• MySQL 8.0不支援

不允許在SHOW CREATE TABLE的輸出中列印MySQL特定的索引選項。此模式由mysqldump在可移植模式下使用。

NO_TABLE_OPTIONS

• MySQL 5.6、5.7支援

• MySQL 8.0不支援

不允許在SHOW CREATE TABLE的輸出中列印MySQL特定的表選項（如ENGINE）。此模式由mysqldump在可移植模式下使用。