August 29, 2016

MySQL入门 (十七) : 错误处理与查询

By Gideon Php / Pear / Mysql / Node.js 0 Comments

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」，它包含许多不同资料型态与设定的栏位：

栏位名称	型态	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」变数的语法：

如果没有指定「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!」到数值型态栏位，资料库会储存下列的预设值，然后产生警告讯息：

栏位型态	预设值	栏位型态	预设值
数值	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」值的叙述都会发生错误讯息：

资料库设定为「non-strict」模式的时候，下列的情况只会产生警告讯息：

2.5 Strict模式与IGNORE关键字

你也可以将资料库设定为「strict」模式，在这个模式下，只有在储存字串资料到非字串型态的栏位时，资料库会尝试帮你指定的字串转换为栏位型态；其它任何违反资料型态的问题，资料库不会储存错误的资料，而且会产生错误讯息。

在「strict」模式模式下执行新增与修改时，可以依照需求加入「IGNORE」关键字：

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」模式，可是对于日期资料的检查又可以宽松一些，你可以执行下列的设定：

加入「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」的设定：

设定值	设定项目
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」指令可以查询这些讯息：

以下列的新增叙述来说，在「non-strict」模式下，虽然会新增一笔纪录到「debug」表格中，不过想要储存的三个资料都是有问题的：

执行上列的新增叙述后，你可以使用「SHOW WARNINGS」查询所有的问题：

下列这个删除表格的叙述，因为使用了「IF EXISTS」，可以预防因为要删除的表格不存在而产生错误，所以执行叙述以后，指会产生一个「Note」告诉你要删除的表格不存在：

如果查询叙述中指定的栏位不存在的话，就会产生错误讯息，在执行叙述以后，可以使用「SHOW ERRORS」查询发生了哪些错误：

如果是因为执行SQL叙述，导致资料库产生的警告或错误，都可以使用「SHOW WARNINGS」或「SHOW ERRORS」查询；不过也有可能是因为作业系统发生问题，例如下列执行汇出资料的叙述，执行叙述以后，资料库应该建立一个「C:\hello \mydata.sql」档案，不过因为指定的资料夹并不存在，所以会产生错误讯息：