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