MySQL處理數據的基本原則是“垃圾進來，垃圾出去”，通俗一點說就是你傳給 MySQL 什么樣的數據，它就會存儲什么樣的數據。如果在存儲數據時沒有對它們進行驗證，那么在把它們檢索出來時得到的就不一定是你所期望的內容。

有幾種 SQL 模式可以在遇到“非正常”值時拋出錯誤，如果你對其他數據庫管理系統比較熟悉，會發現這種行為和其他的數據庫管理系統很像。

下面介紹 MySQL 默認情況下如何處理非正常數據和啟用各種 SQL 模式時會對數據處理產生哪些影響。

默認情況下，MySQL 會按照以下規則來處理越界（即超出取值范圍）的值和其他非正常值：

• 對于數值列或 TIME 列，超出合法取值范圍的那些值將被截斷到取值范圍最近的那個端點，并把結果值存儲起來。
• 對于除 TIME 列以外的其他類型列，非法值會被轉換成與該類型一致的“零”值。
• 對于字符串列（不包括 ENUM 或 SET），過長的字符串將被截斷到該列的最大長度。
• 給 ENUM 或 SET 類型列進行賦值時，需要根據列定義里給出的合法取值列表進行。如果把不是枚舉成員的值賦給 ENUM列，那么列的值就會變成空字符串。如果把包含非集合成員的子字符串的值賦給 SET 列，那么這些字符串會被清理，剩余的成員才會被賦值給列。

如果在執行增刪改查等語句時發生了上述轉換，那么 MySQL 會給出警告消息。在執行完其中的某一條語句之后，可以使用 SHOW WARNINGS 語句來查看警告消息的內容。

如果需要在插入或更新數據時執行更嚴格的檢查，那么可以啟用以下兩種 SQL 模式中的一種：

SET sql_mode = 'STRICT_ALL_TABLES' ; SET sql_mode = 'STRICT_TRANS_TABLES';

對于支持事務的表，這兩種模式都是一樣的。如果發現某個值無效或缺失，那么會產生一個錯誤，并且語句會中止執行，并進行回滾，就像什么事都沒發生過一樣。

對于不支持事務的表，這兩種模式有以下效果。

對于這兩種模式，如果在插入或修改第一個行時，發現某個值無效或缺失，那么結果會產生一個錯誤，語句會中止執行，就像什么事都未發生過一樣。 這跟事務表的行為很相似。

在用于插入或修改多個行的語句里，如果在第一行之后的某個行出現了錯誤，那么會出現某些行被修改的情況。這兩種模式決定著，這條語句此時此刻是要停止執行，還是要繼續執行。

• 在 STRICT_ALL_TABLES模式下，會拋出一個錯誤，并且語句會停止執行。因為受該語句影響的許多行都已被修改，所以這將會導致“部分更新”問題。
• 在 STRICT_TRANS_TABLES 模式下，對于非事務表，MySQL會中止語句的執行。只有這樣做，才能達到事務表那樣的效果。只有當第一行發生錯誤時，才能達到這樣的效果。如果錯誤在后面的某個行上，那么就會出現某些行被修改的情況。由于對于非事務表，那些修改是無法撤銷的，因此MySQL 會繼續執行該語句，以避免出現“部分更新”的問題。它會把所有的無效值轉換為與其最接近的合法值。對于缺失的值，MySQL會把該列設置成其數據類型的隱式默認值，

通過以下模式可以對輸入的數據進行更加嚴格的檢查：

• ERROR_ FOR_ DIVISION_ BY_ZERO：在嚴格模式下，如果遇到以零為除數的情況，它會阻止數值進入數據庫。如果不在嚴格模式下，則會產生一條警告消息，并插入 NULL。
• NO_ ZERO_ DATE：在嚴格模式下，它會阻止“零”日期值進入數據庫。
• NO_ ZERO_ IN_ DATE：在嚴格模式下，它會阻止月或日部分為零的不完整日期值進入數據庫。

簡單來說，MySQL 的嚴格模式就是 MySQL 自身對數據進行的嚴格校驗，例如格式、長度、類型等。比如一個整型字段我們寫入一個字符串類型的數據，在非嚴格模式下 MySQL 不會報錯。如果定義了 char 或 varchar 類型的字段，當寫入或更新的數據超過了定義的長度也不會報錯。

雖然我們會在代碼中做數據校驗，但一般認為非嚴格模式對于編程來說沒有任何好處。MySQL開啟嚴格模式從一定程序上來講也是對我們代碼的一種測試，如果我們沒有開啟嚴格模式并且在開發過程中也沒有遇到錯誤，那么在上線或代碼移植的時候將有可能出現不兼容的情況，因此在開發過程做最好開啟 MySQL 的嚴格模式。

可通過select @@sql_mode;命令查看當前是嚴格模式還是非嚴格模式。

例如，如果想讓所有的存儲引擎啟用嚴格模式，并對“被零除”錯誤進行檢查，那么可以像下面這樣設置 SQL 模式：

SET sql_mode ‘STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO' ;

如果想啟用嚴格模式，以及所有的附加限制，那么最為簡單的辦法是啟用 TRADITIONAL 模式：

SET sql_ mode ‘TRADITIONAL' ;

TRADITIONAL 模式的含義是“啟用嚴格模式，當向 MySQL 數據庫插入數據時，進行數據的嚴格校驗，保證錯誤數據不能插入。用于事務表時，會進行事務的回滾”。

可以選擇性地在某些方面弱化嚴格模式。如果啟用了 SQL 的 ALLOW_ INVALID_ DATES 模式，那么MySQL將不會對日期部分做全面檢查。相反，它只會要求月份值在 1~12 之間，而天數處于 1~31 之間，即允許像‘2000-02-30’或‘2000-06-31’這樣的無效值。

另一個制止錯誤的辦法是在 INSERT 或 UPDATE 語句里使用 IGNORE 關鍵字。這樣那些會因無效值而導致錯誤的語句，將只會導致警告的出現。這些選項能讓你靈活地為你的應用選擇正確的有效性檢查級別。

總結

以上是生活随笔為你收集整理的23、MySQL如何处理无效数据值的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。