怎么使用MySQL Strict SQL MODE

這篇文章主要介紹“怎么使用MySQL Strict SQL MODE”，在日常操作中，相信很多人在怎么使用MySQL Strict SQL MODE問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”怎么使用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

到此，關于“怎么使用MySQL Strict SQL MODE”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！