專欄作者新書出版：Android App程式開發剖析 第三版（適用Android 8 Oreo與Android Studio 3）

MySQL 超新手入門（17）查詢 information_schema

1 錯誤的資料

在規劃與設計一個資料庫的時候，你會針對儲存資料的需求，定義每一個表格中的欄位，包含欄位的資料型態與其它的設定，這些定義都會影響資料的查詢與維護。資料庫中儲存的資料應該是正確而且沒有誤差的，如果你嘗試儲存一個錯誤的資料，資料庫應該要發現問題並告訴你不可以這樣做；不過在不同的需求下，你可能會希望資料庫允許不太嚴重的錯誤，不要每次都產生錯誤訊息。

MySQL資料庫環境中，可以使用「sql_mode」系統變數設定資料庫對於檢查錯誤資料的「嚴格」程度，分為「strict」與「non-strict」兩種模式。在strict模式下，資料庫會嚴格的檢查與發現錯誤的資料，而且不會儲存錯誤的資料；在non-strict模式下，資料庫同樣會檢查與發現錯誤的資料，不過它會儘量試著處理這些錯誤的資料，再把資料儲存起來。

你可以依照自己的需求設定「sql_mode」系統變數，下列的指令可以設定為「non-strict」模式：

下列的敘述設定為「strict」模式：

「STRICT_TRANS_TABLES」與「STRICT_ALL_TABLES」同樣可以設定為「strict」模式，在使用支援「交易、transaction」的資料庫，應該要設定為「STRICT_TRANS_TABLES」，這樣可以確定資料的完整性。

設定為「strict」與「non-strict」兩種不同的模式，對於錯誤資料的處理會有很大的差異。下列是一個用來測試的表格「cmdev.debug」，它包含許多不同資料型態與設定的欄位：

2 Non-Strict模式

下列是使用「SET」設定「sql_mode」變數的語法：

如果沒有指定「SESSION」或「GLOBAL」的話，MySQL會把這個設定當成「SESSION」，設定的效果只有一個用戶端的連線，並不會影響其它用戶端連線的設定。下列的範例設定為「non-strict」模式後，使用「SHOW」或「SELECT」敘述查詢設定後的結果：

如果你希望將所有用戶端都設定為「non-strict」模式，那就要使用「GLOBAL」關鍵字：

設定為「non-strict」模式以後，在執行資料維護時，如果資料完全符合欄位資料型態的規定，那就不會發生任何警告或錯誤：

如果資料庫發現不符合欄位規定的資料，它會儘量試著處理這些錯誤的資料，再把資料儲存起來。以下列的範例來說，想要儲存到字串型態欄位的值有六個字元，可是「fchar」欄位只能儲存三個字元，資料庫在「non-strict」模式下，會忽略多餘的字元後再儲存起來，然後使用警告訊息通知你：

在non-strict模式運作時，下列幾種情形都有可能會啟動自動修正資料的功能：

• 執行新增或修改敘述，包含INSERT、REPLACE、UPDATE與LOAD DATA INFILE
• 使用ALTER TABLE修改表格的欄位定義
• 在欄位定義中使用「DEFAULT」指定欄位的預設值

註：「LOAD DATA INFILE」在「匯入與匯出資料、使用SQL敘述匯入資料」中討論。

2.1 數值

資料庫在「non-strict」模式下，處理數值資料型態會使用比較寬鬆的方式。以整數型態「TINYINT」來說，如果儲存的數值超過規定的範圍，資料庫會依照下列的方式來處理錯誤的數值資料：

浮點數型態與整數型態一樣有規定的範圍，如果你在定義浮點數型態欄位時，也設定了長度與小數位數，那就只能儲存設定的範圍：

註：儲存小數到整數型態的欄位，或是小數位數超過浮點數型態定義的位數，MySQL會針對小數的部份執行四捨五入，並不會有任何錯誤或警告。

2.2 列舉(ENUM)與集合(SET)

「ENUM」型態只能儲存一個規定好的成員資料，以「fenum」欄位來說，它設定了A、B、C三個成員，你也可以使用數值1、2、3表示。在「non-strict」模式下，如果你嘗試儲存錯誤的資料，資料庫都會儲存空的字串「"」，數值為0：

「SET」型態可以儲存一組規定好的成員資料，以以「fset」欄位來說，它設定了X、Y、Z三個成員。在「non-strict」模式下，如果你嘗試儲存錯誤的資料，資料庫都會儲存空的字串「"」，數值為0；如果指定的成員不正確的話，資料庫也會自動忽略它們：

註：重複的集合成員不會造成任何錯誤或警告。例如儲存「’X,X,Y,Y,Z,Z’」的值到「fset」欄位，實際儲存的是「’X,Y,Z’」。

2.3 字串轉換為其它型態

資料庫設定為「non-strict」模式的時候，如果你想要儲存字串資料到非字串型態的欄位，資料庫都會幫你轉換為欄位的型態後再儲存。如果字串的內容不能轉換為欄位的型態，例如想要儲存字串「Hello!」到數值型態欄位，資料庫會儲存下列的預設值，然後產生警告訊息：

在執行字串轉換型態的時候，資料庫會使用很寬鬆的方式，盡量把你的資料儲存起來，尤其是字串轉換為數值與日期型態：

2.4 NULL與NOT NULL

在規劃表格欄位的時候，你會根據需求設定欄位是否可以儲存「NULL」值。如果你設定某一個欄位不可以儲存「NULL」值，不論在「non-strict」或「strict」模式下，儲存「NULL」值的敘述都會發生錯誤訊息：

資料庫設定為「non-strict」模式的時候，下列的情況只會產生警告訊息：

2.5 Strict模式與IGNORE關鍵字

你也可以將資料庫設定為「strict」模式，在這個模式下，只有在儲存字串資料到非字串型態的欄位時，資料庫會嘗試幫你指定的字串轉換為欄位型態；其它任何違反資料型態的問題，資料庫不會儲存錯誤的資料，而且會產生錯誤訊息。

在「strict」模式模式下執行新增與修改時，可以依照需求加入「IGNORE」關鍵字：

3 其它設定

「sql_mode」變數設定為「non-strict」或「strict」模式後，還可以依照自己的需求加入額外的設定：

如果你希望資料庫設定為「strict」模式，可是對於日期資料的檢查又可以寬鬆一些，你可以執行下列的設定：

加入「ALLOW_INVALID_DATES」的設定以後，就算是「2000-02-31」這樣一個錯誤的日期資料，資料庫也會儲存它，不會有任何警告或錯誤訊息：

日期型態的欄位，不論在「non-strict」或「strict」模式下，你都可以儲存年月日為0的日期資料，不會產生任何警告或錯誤訊息。如果不希望儲存這樣的日期資料，你可以加入「NO_ZERO_DATE」與「NO_ZERO_IN_DATE」的設定：

如果在你執行的敘述中出現除以零的運算式，資料庫會產生「NULL」值，並不會產生任何警告或錯誤訊息。你可以加入「ERROR_FOR_DIVISION_BY_ZERO」設定：

在敘述中出現除以零的運算式時，資料庫會產生除以零的錯誤訊息：

你可以使用不同的設定項目，讓資料庫中的資料更符合自己的需求。MySQL也為你準備了許多不同的設定組合，讓你可以方便的完成「sql_mode」的設定：

註：「sql_mode」的完整設定可以參考MySQL參考手冊中的「5.2.6. SQL Modes」。

4 查詢錯誤與警告

在執行SQL敘述後，如果發生警告或錯誤，你可能需要根據這些訊息來執行一些補救工作。MySQL提供的「SHOW」指令可以查詢這些訊息：

以下列的新增敘述來說，在「non-strict」模式下，雖然會新增一筆紀錄到「debug」表格中，不過想要儲存的三個資料都是有問題的：

執行上列的新增敘述後，你可以使用「SHOW WARNINGS」查詢所有的問題：

下列這個刪除表格的敘述，因為使用了「IF EXISTS」，可以預防因為要刪除的表格不存在而產生錯誤，所以執行敘述以後，指會產生一個「Note」告訴你要刪除的表格不存在：

如果查詢敘述中指定的欄位不存在的話，就會產生錯誤訊息，在執行敘述以後，可以使用「SHOW ERRORS」查詢發生了哪些錯誤：

如果是因為執行SQL敘述，導致資料庫產生的警告或錯誤，都可以使用「SHOW WARNINGS」或「SHOW ERRORS」查詢；不過也有可能是因為作業系統發生問題，例如下列執行匯出資料的敘述，執行敘述以後，資料庫應該建立一個「C:\hello\mydata.sql」檔案，不過因為指定的資料夾並不存在，所以會產生錯誤訊息：

如果發生這類的錯誤，資料庫只會告訴你不能儲存檔案，詳細的錯誤訊息要在命令提示字元下，使用「perror」程式來查詢：

註：匯出資料會在「匯入與匯出資料」中詳細討論。

如果需要知道警告或錯誤的數量，可以使用下列的查詢敘述：

後續 >> MySQL 超新手入門（19）匯入與匯出資料