**目錄** [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)