MySQL Strict SQL MODE

嚴格SQL模式控制MySQL如何處理資料改變語句(insert或update)中的無效或缺失值。一個值可能由于各種原因而無效。例如，它對于列來說有錯誤的資料類型，或者超過了列的範圍。當新記錄被插入而對于非NULL且沒有顯式在定義時指定DEFAULT子句的列沒有包含值就會出現缺失值的情況。(對于一個NULL列，如果缺失值就會插入NULL值)嚴格SQL模式也會影響DDL語句比如create table。

如果嚴格SQL模式沒有生效，MySQL對于無效或者缺失值會插入調整值并生成一個警告。在嚴格SQL模式中，可以通過使用insert ignore或udpate ignore來産生這種行為。

對于select這樣不改變資料的語句，在嚴格SQL模式中無效值會生成一個警告而不是錯誤。

嚴格SQL模式對于試圖建立一個鍵值而超過列的最大鍵值長度時會産生一個錯誤。當嚴格SQL模式沒有啟用時，會産生一個警告并且截斷鍵值的長度使其滿足最大鍵值長度。

嚴格SQL模式不影響是否對外鍵限制執行檢查。foreign_key_checks可以被使用。

如果STRICT_ALL_TABLES或STRICT_TRANS_TABLES被啟用嚴格SQL模式就會生效，但這些模式的影響會有不同:

.對于事務表來說，當STRICT_ALL_TABLES或STRICT_TRANS_TABLES被啟用後當在資料出現無效或丢失值就會出現錯誤。語句就會被終止與復原。

.對于非事務表，如果在插入或更新語句中第一行記錄出現壞值這些模式的行為是一樣的:語句被終止并且表仍然保持不變。如果語句插入或修改多行記錄并且在第二行或之後的行記錄中出現壞值，那麼結果依賴于嚴格SQL模式是否被啟用。

-對于STRICT_ALL_TABLES，MySQL會傳回一個錯誤并忽略剩餘的行記錄。然而，因為早些的行記錄已經被插入或被更新，會導緻部分更新。為了避免這個問題，使用單行語句，就會終止而不會改變表資料。

-對于STRICT_TRANS_TABLES，MySQL會将一個無效的值轉換成一個最接近的有效值并插入這個調整值。如果這個值将丢失，MySQL插入這個隐式預設值。在這種情況下，MySQL生成一個敬告而不是一個錯誤并繼續處理語句。

嚴格SQL模式對除零，零日期和日期中出現零的處理如下:

.嚴格SQL模式影響對除零的處理，它包括MOD(N,0):對于資料改變操作(insert,update):

-如果嚴格SQL模式沒有被啟用，除零會插入NULL并生成一個警告。

-如果嚴格SQL模式被啟用，除非指定了ignore否則除零操作會生成一個錯誤。對于insert ignore和update ignore操作，除零操作會插入NULL并生成一個警告。

對于select，除零操作會傳回NULL。啟用嚴格SQL模式會導緻一個警告。

.嚴格SQL模式會影響伺服器是否允許'0000-00-00'為一個有效日期:

-如果嚴格SQL模式沒有被啟用，'0000-00-00'被允許并且插入操作不會産生警告。

-如果嚴格SQL模式被啟用,'0000-00-00'不被允許并且插入操作會産生錯誤，除非你指定ignore。對于insert ignore或update ignore，'0000-00-00'被允許并且插入操作會産生警告。

.嚴格SQL模式影響伺服器是否允許在日期中的年部分為非零但月和日部分允許為零(比如'2010-00-01'或'2010-01-00'):

-如果嚴格SQL模式沒有被啟用，有零的日期被允許并且插入操作不産生警告。

-如果嚴格SQL模式被啟用,有零的日期不被允許并且插入操作産生錯誤，除非指定ignore。對于insert ignore或update ignore來說，有零的日期将以'0000-00-00'形式被插入并産生一個警告。

IGNORE關鍵字與嚴格SQL模式的對比

這裡将介紹在語句執行時IGNORE關鍵字(它降級錯誤為警告)和嚴格SQL模式(它更新警告為錯誤)的對比。描述它們影響那些語句以及應用它們有那些錯誤。

IGNORE對語句執行的影響

MySQL中的一些語句支援可選的IGNORE關鍵字。此關鍵字将導緻伺服器降級某些類型的錯誤并生成警告。對于多行語句，IGNORE會導緻語句跳到下一行，而不是中止。

例如，如果表t2有一個主鍵列i,試圖在多行記錄中插入相同的i值正常來說會産生一個重複鍵錯誤:

mysql> insert into t2(i) values(1),(1);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

當使用IGNORE關鍵字時，包含重複鍵值的記錄仍然不會被插入，但會使用警告來代替錯誤:

mysql> insert ignore into t2(i) values(1),(1);

Query OK, 1 row affected, 1 warning (0.15 sec)

Records: 2 Duplicates: 1 Warnings: 1

mysql> show warnings;
Level	Code	Message
Warning	1062	Duplicate entry '1' for key 'PRIMARY'

2 rows in set (0.00 sec)

以下語句支援IGNORE關鍵字:

.create table... select:ignore不能應用到語句的create table或select部分但對于由select語句所提供記錄來執行插入語句可以應用。對于唯一鍵值重複的記錄會被丢棄。

.delete:ignore會導緻MySQL在處理删除記錄時忽略錯誤。

.insert:使用ignore，對于唯一鍵值重複的記錄會被丢棄。對于重複鍵值的行記錄會導緻資料轉換為最接近的有效值被插入。

對于分區表當沒有比對指定值的分區被找到時，ignore會導緻包含那些不比對值的記錄的插入操作失敗。

.load data,load xml:使用ignore，對于唯一鍵值重複的記錄會被丢棄。

.update:使用ignore，對于在唯一鍵值出現重複鍵值沖突的記錄不會被更新。被更新的記錄可能導緻資料轉換為最接近的有效值被插入。

ignore關鍵字應用到以下錯誤:

ER_BAD_NULL_ERROR

ER_DUP_ENTRY

ER_DUP_ENTRY_WITH_KEY_NAME

ER_DUP_KEY

ER_NO_PARTITION_FOR_GIVEN_VALUE

ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT

ER_NO_REFERENCED_ROW_2

ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET

ER_ROW_IS_REFERENCED_2

ER_SUBQUERY_NO_1_ROW

ER_VIEW_CHECK_FAILED

嚴格SQL模式對語句執行的影響

MySQL伺服器可以以不同的SQL模式進行操作并且可以應用這些不同模式到不同的客房端，這依賴于sql_mode系統變量。在嚴格SQL模式中，伺服器會将特定的警告更新成錯誤。

例如，在非嚴格SQL模式中，向整數類型列插入字元串'abc'的結果是将這個字元串值轉換為0并生成一個警告:

mysql> SET sql_mode = '';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t2(i) values('abc');

Query OK, 1 row affected, 1 warning (0.02 sec)

	1366	Incorrect integer value: 'abc' for column 'i' at row 1

1 row in set (0.00 sec)

在嚴格SQL模式下，無效值會被拒絕并生成錯誤:

mysql> SET sql_mode = 'STRICT_ALL_TABLES';

ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1

在某些條件下，某些值可能超出範圍或将無效行插入或從表中删除，嚴格SQL模式适用于以下語句:

alter table

create table

create table ... select

delete(單表和多表)

insert

load data

load xml

select sleep()

update(單表和多表)

在存儲程式中，如果程式是在嚴格模式生效時定義的，則剛才列出的類型的各個語句将以嚴格SQL模式執行。

嚴格SQL模式應用于以下錯誤，代表輸入值可能無效或丢失這類錯誤。如果對于列值使用了錯誤資料類型或超過了值的

範圍那麼值就是無效的。如果被插入的新行不包含NOT NULL列值但除了在列定義時顯式指定了DEFAULT子句的那麼就

是值丢失。

ER_CUT_VALUE_GROUP_CONCAT

ER_DATA_TOO_LONG

ER_DATETIME_FUNCTION_OVERFLOW

ER_DIVISION_BY_ZERO

ER_INVALID_ARGUMENT_FOR_LOGARITHM

ER_NO_DEFAULT_FOR_FIELD

ER_NO_DEFAULT_FOR_VIEW_FIELD

ER_TOO_LONG_KEY

ER_TRUNCATED_WRONG_VALUE

ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

ER_WARN_DATA_OUT_OF_RANGE

ER_WARN_NULL_TO_NOTNULL

ER_WARN_TOO_FEW_RECORDS

ER_WRONG_ARGUMENTS

ER_WRONG_VALUE_FOR_TYPE

WARN_DATA_TRUNCATED