MySQL中怎么處理無效數據值

MySQL中怎么處理無效數據值，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

　　MySQL如何處理無效數據值

　　默認情況下，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';

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

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

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

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

　　在 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 關鍵字。這樣那些會因無效值而導致錯誤的語句，將只會導致警告的出現。這些選項能讓你靈活地為你的應用選擇正確的有效性檢查級別。

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。