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' |
| 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)
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 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';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t2(i) values('abc');
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_BAD_NULL_ERROR
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