**目錄**
[TOC]
# 1 錯誤的資料
在規劃與設計一個數據庫的時候,你會針對儲存資料的需求,定義每一個表格中的字段,包含字段的資料型態與其它的設定,這些定義都會影響資料的查詢與維護。數據庫中儲存的資料應該是正確而且沒有誤差的,如果你嘗試儲存一個錯誤的資料,數據庫應該要發現問題并告訴你不可以這樣做;不過在不同的需求下,你可能會希望數據庫允許不太嚴重的錯誤,不要每次都產生錯誤訊息。
MySQL數據庫環境中,可以使用“sql_mode”系統變量設定數據庫對于檢查錯誤資料的“嚴格”程度,分為“strict”與“non-strict”兩種模式。在strict模式下,數據庫會嚴格的檢查與發現錯誤的資料,而且不會儲存錯誤的資料;在non-strict模式下,數據庫同樣會檢查與發現錯誤的資料,不過它會盡量試著處理這些錯誤的資料,再把資料儲存起來。
你可以依照自己的需求設定“sql_mode”系統變量,下列的指令可以設定為“non-strict”模式:
[](http://box.kancloud.cn/2015-07-18_55a9d50fef0ae.png)
下列的敘述設定為“strict”模式:
[](http://box.kancloud.cn/2015-07-18_55a9d527046ab.png)
“STRICT_TRANS_TABLES”與“STRICT_ALL_TABLES”同樣可以設定為“strict”模式,在使用支援“交易、transaction”的數據庫,應該要設定為“STRICT_TRANS_TABLES”,這樣可以確定資料的完整性。
設定為“strict”與“non-strict”兩種不同的模式,對于錯誤資料的處理會有很大的差異。下列是一個用來測試的表格“cmdev.debug”,它包含許多不同資料型態與設定的字段:
| 字段名稱 | 型態 | NULL | 索引 | 默認值 | 其它資訊 |
| --- | --- | --- | --- | --- | --- |
| fint | tinyint(4) | NO | NULL |
| fchar | varchar(3) | YES | NULL |
| fdouble | double(5, 2) | YES | NULL |
| fdate | date | YES | NULL |
| ftime | time | YES | NULL |
| fenum | enum('A','B','C') | YES | NULL |
| fset | set('A','B','C') | YES | NULL |
# 2 Non-Strict模式
下列是使用“SET”設定“sql_mode”變量的語法:
[](http://box.kancloud.cn/2015-07-18_55a9d53149871.png)
如果沒有指定“SESSION”或“GLOBAL”的話,MySQL會把這個設定當成“SESSION”,設定的效果只有一個用戶端的連線,并不會影響其它用戶端連線的設定。下列的范例設定為“non-strict”模式后,使用“SHOW”或“SELECT”敘述查詢設定后的結果:
[](http://box.kancloud.cn/2015-07-18_55a9d545054e9.png)
如果你希望將所有用戶端都設定為“non-strict”模式,那就要使用“GLOBAL”關鍵字:
[](http://box.kancloud.cn/2015-07-18_55a9d546eb92f.png)
設定為“non-strict”模式以后,在執行資料維護時,如果資料完全符合字段資料型態的規定,那就不會發生任何警告或錯誤:
[](http://box.kancloud.cn/2015-07-18_55a9d54e6ae1c.png)
如果數據庫發現不符合字段規定的資料,它會盡量試著處理這些錯誤的資料,再把資料儲存起來。以下列的范例來說,想要儲存到字串型態字段的值有六個字符,可是“fchar”字段只能儲存三個字符,數據庫在“non-strict”模式下,會忽略多余的字符后再儲存起來,然后使用警告訊息通知你:
[](http://box.kancloud.cn/2015-07-18_55a9d54f3d6ed.png)
在non-strict模式運作時,下列幾種情形都有可能會啟動自動修正資料的功能:
* 執行新增或修改敘述,包含INSERT、REPLACE、UPDATE與LOAD DATA INFILE
* 使用ALTER TABLE修改表格的字段定義
* 在字段定義中使用“DEFAULT”指定字段的默認值
注:“LOAD DATA INFILE”在“匯入與匯出資料、使用SQL敘述匯入資料”中討論。
## 2.1 數值
數據庫在“non-strict”模式下,處理數值資料型態會使用比較寬松的方式。以整數型態“TINYINT”來說,如果儲存的數值超過規定的范圍,數據庫會依照下列的方式來處理錯誤的數值資料:
[](http://box.kancloud.cn/2015-07-18_55a9d5512f1c5.png)
浮點數型態與整數型態一樣有規定的范圍,如果你在定義浮點數型態字段時,也設定了長度與小數位數,那就只能儲存設定的范圍:
[](http://box.kancloud.cn/2015-07-18_55a9d5558db88.png)
注:儲存小數到整數型態的字段,或是小數位數超過浮點數型態定義的位數,MySQL會針對小數的部份執行四舍五入,并不會有任何錯誤或警告。
## 2.2 列舉(ENUM)與集合(SET)
“ENUM”型態只能儲存一個規定好的成員資料,以“fenum”字段來說,它設定了A、B、C三個成員,你也可以使用數值1、2、3表示。在“non-strict”模式下,如果你嘗試儲存錯誤的資料,數據庫都會儲存空的字串“"”,數值為0:
[](http://box.kancloud.cn/2015-07-18_55a9d557cf49a.png)
“SET”型態可以儲存一組規定好的成員資料,以以“fset”字段來說,它設定了X、Y、Z三個成員。在“non-strict”模式下,如果你嘗試儲存錯誤的資料,數據庫都會儲存空的字串“"”,數值為0;如果指定的成員不正確的話,數據庫也會自動忽略它們:
[](http://box.kancloud.cn/2015-07-18_55a9d56ea8399.png)
注:重復的集合成員不會造成任何錯誤或警告。例如儲存“’X,X,Y,Y,Z,Z’”的值到“fset”字段,實際儲存的是“’X,Y,Z’”。
## 2.3 字串轉換為其它型態
數據庫設定為“non-strict”模式的時候,如果你想要儲存字串資料到非字串型態的字段,數據庫都會幫你轉換為字段的型態后再儲存。如果字串的內容不能轉換為字段的型態,例如想要儲存字串“Hello!”到數值型態字段,數據庫會儲存下列的默認值,然后產生警告訊息:
| 字段型態 | 默認值 | 字段型態 | 默認值 |
| --- | --- | --- | --- |
| 數值 | 0 | TIMESTAMP | '0000-00-00 00:00:00' |
| DATE | '0000-00-00' | YEAR | 0000或00 |
| TIME | '00:00:00' | ENUM | '' |
| DATETIME | '0000-00-00 00:00:00' | SET | '' |
在執行字串轉換型態的時候,數據庫會使用很寬松的方式,盡量把你的資料儲存起來,尤其是字串轉換為數值與日期型態:
| 字串值 | fint | fdate |
| --- | --- | --- |
| '10-10-10' | 10 | '2010-10-10' |
| '007' | 7 | '0000-00-00' |
| 'SAM36' | 0 | '0000-00-00' |
| '36SAM' | 36 | '0000-00-00' |
| '25-SAM' | 25 | '0000-00-00' |
| '12 SAM' | 12 | '0000-00-00' |
| 'SAM' | 0 | '0000-00-00' |
## 2.4 NULL與NOT NULL
在規劃表格字段的時候,你會根據需求設定字段是否可以儲存“NULL”值。如果你設定某一個字段不可以儲存“NULL”值,不論在“non-strict”或“strict”模式下,儲存“NULL”值的敘述都會發生錯誤訊息:
[](http://box.kancloud.cn/2015-07-18_55a9d56f17ad7.png)
數據庫設定為“non-strict”模式的時候,下列的情況只會產生警告訊息:
[](http://box.kancloud.cn/2015-07-18_55a9d57082753.png)
## 2.5 Strict模式與IGNORE關鍵字
你也可以將數據庫設定為“strict”模式,在這個模式下,只有在儲存字串資料到非字串型態的字段時,數據庫會嘗試幫你指定的字串轉換為字段型態;其它任何違反資料型態的問題,數據庫不會儲存錯誤的資料,而且會產生錯誤訊息。
在“strict”模式模式下執行新增與修改時,可以依照需求加入“IGNORE”關鍵字:
[](http://box.kancloud.cn/2015-07-18_55a9d57aa2bf6.png)
# 3 其它設定
“sql_mode”變量設定為“non-strict”或“strict”模式后,還可以依照自己的需求加入額外的設定:
| 設定值 | 說明 |
| --- | --- |
| ALLOW_INVALID_DATES | 允許錯誤的日期資料 |
| NO_ZERO_DATE | 不允許全部是0的日期資料 |
| NO_ZERO_IN_DATE | 日期資料中不可以有0 |
| ERROR_FOR_DIVISION_BY_ZERO | 除以0時產生錯誤,而不是產生NULL值 |
如果你希望數據庫設定為“strict”模式,可是對于日期資料的檢查又可以寬松一些,你可以執行下列的設定:
[](http://box.kancloud.cn/2015-07-18_55a9d58d4bc73.png)
加入“ALLOW_INVALID_DATES”的設定以后,就算是“2000-02-31”這樣一個錯誤的日期資料,數據庫也會儲存它,不會有任何警告或錯誤訊息:
[](http://box.kancloud.cn/2015-07-18_55a9d59c3d99a.png)
日期型態的字段,不論在“non-strict”或“strict”模式下,你都可以儲存年月日為0的日期資料,不會產生任何警告或錯誤訊息。如果不希望儲存這樣的日期資料,你可以加入“NO_ZERO_DATE”與“NO_ZERO_IN_DATE”的設定:
[](http://box.kancloud.cn/2015-07-18_55a9d5ada2442.png)
如果在你執行的敘述中出現除以零的運算式,數據庫會產生“NULL”值,并不會產生任何警告或錯誤訊息。你可以加入“ERROR_FOR_DIVISION_BY_ZERO”設定:
[](http://box.kancloud.cn/2015-07-18_55a9d5af8ac4e.png)
在敘述中出現除以零的運算式時,數據庫會產生除以零的錯誤訊息:
[](http://box.kancloud.cn/2015-07-18_55a9d5c54ea34.png)
你可以使用不同的設定項目,讓數據庫中的資料更符合自己的需求。MySQL也為你準備了許多不同的設定組合,讓你可以方便的完成“sql_mode”的設定:
| 設定值 | 設定項目 |
| --- | --- |
| ANSI | REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE |
| DB2 | PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、NO_FIELD_OPTIONS |
| MAXDB | PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER |
| MSSQL | PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、NO_FIELD_OPTIONS |
| MYSQL323 | NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCE |
| MYSQL40 | NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCE |
| ORACLE | PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER |
| POSTGRESQL | PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、NO_FIELD_OPTIONS |
| TRADITIONAL | STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER |
注:“sql_mode”的完整設定可以參考MySQL參考手冊中的“5.2.6\. SQL Modes”。
# 4 查詢錯誤與警告
在執行SQL敘述后,如果發生警告或錯誤,你可能需要根據這些訊息來執行一些補救工作。MySQL提供的“SHOW”指令可以查詢這些訊息:
[](http://box.kancloud.cn/2015-07-18_55a9d5d30f207.png)
以下列的新增敘述來說,在“non-strict”模式下,雖然會新增一筆紀錄到“debug”表格中,不過想要儲存的三個資料都是有問題的:
[](http://box.kancloud.cn/2015-07-18_55a9d5d40eff4.png)
執行上列的新增敘述后,你可以使用“SHOW WARNINGS”查詢所有的問題:
[](http://box.kancloud.cn/2015-07-18_55a9d5ea8133a.png)
下列這個刪除表格的敘述,因為使用了“IF EXISTS”,可以預防因為要刪除的表格不存在而產生錯誤,所以執行敘述以后,指會產生一個“Note”告訴你要刪除的表格不存在:
[](http://box.kancloud.cn/2015-07-18_55a9d5f61a91e.png)
如果查詢敘述中指定的字段不存在的話,就會產生錯誤訊息,在執行敘述以后,可以使用“SHOW ERRORS”查詢發生了哪些錯誤:
[](http://box.kancloud.cn/2015-07-18_55a9d5f9c9f72.png)
如果是因為執行SQL敘述,導致數據庫產生的警告或錯誤,都可以使用“SHOW WARNINGS”或“SHOW ERRORS”查詢;不過也有可能是因為作業系統發生問題,例如下列執行匯出資料的敘述,執行敘述以后,數據庫應該建立一個“C:\hello\mydata.sql”檔案,不過因為指定的資料夾并不存在,所以會產生錯誤訊息:
[](http://box.kancloud.cn/2015-07-18_55a9d60414624.png)
如果發生這類的錯誤,數據庫只會告訴你不能儲存盤案,詳細的錯誤訊息要在命令提示字符下,使用“perror”程式來查詢:
[](http://box.kancloud.cn/2015-07-18_55a9d63227def.png)
注:匯出資料會在“匯入與匯出資料”中詳細討論。
如果需要知道警告或錯誤的數量,可以使用下列的查詢敘述:
[](http://box.kancloud.cn/2015-07-18_55a9d63f0f667.png)