第1章:一般信息 / 1.8. MySQL标准的兼容性 / 1.8.6. MySQL处理约束的方式
1.8.6.1. PRIMARY KEY和UNIQUE索引约束
1.8.6.2. 对无效数据的约束
1.8.6.3. ENUM和SET约束

使用MySQL,你可以使用允许回滚的事务表,以及不允许回滚的非事务表。因此,在MySQL中的约束处理功能与其他DBMS中的略有不同。在非事务性表中插入或更新大量行时,当出现错误以至于不能回滚所作的变更时,必须处理该情况。

其基本原理在于,在解析将要执行的语句的同时,MySQL服务器会尽量为检测到的问题生成错误信息,并会在执行语句的同时尽量恢复出现的错误。在大多数情况下我们均是这样作的,但不包括所有情况。

出现错误时,MySQL可选择中途中止语句,或尽可能恢复并继续执行语句。默认情况下,服务器将采取后一种路线。这意味着,服务器可能会强制将非法值变为最接近的合法值(例如)。

从MySQL 5.0.2开始,提供了数种SQL模式,使用它们,能够对如何接受可能为不良数据值的方式进行更好的控制,也能在出现错误时,对是否继续执行语句或放弃语句进行控制。使用这些选项,能够将MySQL服务器配置为更为传统的风格,类似于拒绝不恰当输入的其他DBMS。可以在运行时设置SQL模式,这样,各客户端就能选择与其需求最为贴切的行为。请参见5.3.2节,“SQL服务器模式”

在以下部分,介绍了使用不同约束类型的情况。

1.8.6.1. PRIMARY KEY和UNIQUE索引约束

通常情况下,当你试图INSERTUPDATE会导致主键、唯一键或外键冲突的行时,将出现错误。如果你正在使用事务性存储引擎时,如InnoDB,MySQL会自动回滚语句。如果你正在使用非事务性存储引擎,MySQL将在出错的行上停止执行语句,剩余的行也不再处理。

如果你希望忽略这类键冲突,可使用MySQL支持的、用于INSERTUPDATEIGNORE关键字。在这种情况下,MySQL将忽略任何键冲突,并继续处理下一行。请参见13.2.4节,“INSERT语法”。请参见3.2.10节,“UPDATE语法”

使用mysql_info() C API函数,能够获取关于实际插入或更新行数的信息。请参见25.2.3.34节,“mysql_info()”。在MySQL 4.1和更高版本中,也能使用SHOW WARNINGS语句。请参见13.5.4.22节,“SHOW WARNINGS语法”

目前,只有InnoDB表支持外键。请参见15.2.6.4节,“FOREIGN KEY约束”。计划在MySQL 5.1中实施对MyISAM表的外键支持。

1.8.6.2. 对无效数据的约束

在MySQL 5.0.2之前,MySQL对非法或不当值并不严厉,而且为了数据输入还会强制将它们变为合法值。在MySQL 5.0.2和更高版本中,保留了以前的默认行为,但你可以为不良值选择更传统的处理方法,从而使得服务器能够拒绝并放弃出现不良值的语句。本节介绍了MySQL的默认行为(宽大行为),新的严格的SQL模式,以及它们的区别。

如果你未使用严格模式,下述情况是真实的。如果将“不正确”的值插入到列,如将NULL值插入非NULL列,或将过大的数值插入数值列,MySQL会将这些列设置为“最可能的值”,而不是生成错误信息。

·         如果试图将超范围的值保存到数值列,MySQL服务器将保存0(最小的可能值)取而代之,或最大的可能值。

·         对于字符串,MySQL或保存空字符串,或将字符串尽可能多的部分保存到列中。

·         如果打算将不是以数值开头的字符串保存到数值列,MySQL将保存0。

·         MySQL允许将特定的不正确日期值保存到DATE和DATETIME列(如“2000-02-31”或“2000-02-00”)。其观点在于,验证日期不是SQL服务器的任务。如果MySQL能保存日期值并准确检索相同的值,MySQL就能按给定的值保存它。如果日期完全不正确(超出服务器能保存的范围)将在列中保存特殊的日期值“0000-00-00”取而代之。

·         如果试图将NULL值保存到不接受NULL值的列,对于单行INSERT语句,将出现错误。对于多行INSERT语句或INSERT INTO ... SELECT语句,MySQL服务器会保存针对列数据类型的隐含默认值。一般情况下,对于数值类型,它是0,对于字符串类型,它是空字符串(''),对于日期和时间类型是“zero”。在13.1.5节,“CREATE TABLE语法”一节中,讨论了隐含的默认值。

·         如果INSERT语句未为列指定值,如果列定义包含明确的DEFAULT子句,MySQL将插入默认值。如果在定义中没有这类DEFAULT子句,MySQL会插入列数据类型的隐含默认值。

采用前述规则的原因在于,在语句开始执行前,无法检查这些状况。如果在更新了数行后遇到这类问题,我们不能仅靠回滚解决,这是因为存储引擎可能不支持回滚。中止语句并不是良好的选择,在该情况下,更新完成了“一半”,这或许是最差的情况。对于本例,较好的方法是“仅可能做到最好”,然后就像什么都未发生那样继续。

在MySQL 5.0.2和更高版本中,可以使用STRICT_TRANS_TABLESSTRICT_ALL_TABLES SQL模式,选择更严格的处理方式。请参见5.3.2节,“SQL服务器模式”

STRICT_TRANS_TABLES的工作方式:

·         对于事务性存储引擎,在语句中任何地方出现的不良数据值均会导致放弃语句并执行回滚。

·         对于非事务性存储引擎,如果错误出现在要插入或更新的第1行,将放弃语句。(在这种情况下,可以认为语句未改变表,就像事务表一样)。首行后出现的错误不会导致放弃语句。取而代之的是,将调整不良数据值,并给出告警,而不是错误。换句话讲,使用STRICT_TRANS_TABLES后,错误值会导致MySQL执行回滚操作,如果可以,所有更新到此为止。

要想执行更严格的检查,请启用STRICT_ALL_TABLES。除了非事务性存储引擎,它与STRICT_TRANS_TABLES等同,即使当不良数据出现在首行后的其他行,所产生的错误也会导致放弃语句。这意味着,如果错误出现在非事务性表多行插入或更新过程的中途,仅更新部分结果。前面的行将完成插入或更新,但错误出现点后面的行则不然。对于非事务性表,为了避免这种情况的发生,可使用单行语句,或者在能接受转换警告而不是错误的情况下使用STRICT_TRANS_TABLES。要想在第1场合防止问题的出现,不要使用MySQL来检查列的内容。最安全的方式(通常也较快)是,让应用程序负责,仅将有效值传递给数据库。

有了严格的模式选项后,可使用INSERT IGNOREUPDATE IGNORE而不是不带IGNORE的INSERTUPDATE,将错误当作告警对待。

1.8.6.3. ENUM和SET约束

ENUMSET列提供了定义仅能包含给定值集合的列的有效方式。但是,从MySQL 5.0.2起,ENUMSET不是实际约束。其原因与不重视NOT NULL的原因一样。请参见1.8.6.2节,“对无效数据的约束”

ENUM列总有1个默认值。如果未指定默认值,对于包含NULL的列,默认值为NULL;否则,第1个枚举值将被当作默认值。

如果在ENUM列中插入了不正确的值,或者,如果使用IGNORE将值强制插入了ENUM列,会将其设置为保留的枚举值0,对于字符串情形,将显示为空字符串。请参见11.4.4节,“ENUM类型”

如果在SET列中插入了不正确值,该值将被忽略。例如,如果列能包含值“a”、“b”和“c”,并赋值“a,x,b,y”,结果为“a,b”。请参见11.4.5节,“SET类型”

从5.0.2开始,可以对服务器进行配置,以使用严格的SQL模式。请参见5.3.2节,“SQL服务器模式”。启用严格模式后,ENUMSET列的定义可作为对输入至列的值的约束。如果值不满足下述条件,将出现错误:

·         ENUM值必须是在列定义中给出的值之一,或内部的数字等同物。该值不能是错误值(即,0或空字符串)。对于定义为ENUM('a','b','c')的列,诸如'''d''ax'等,均是非法的,并将被拒。

·         SET值必须是空字符串,或由1个或多个在列定义中给出的且用逗号隔开的值组成。 对于定义为SET('a','b','c')的列,诸如'd''a,b,c,d'等,均是非法的,并将被拒。

如果使用了INSERT IGNOREUPDATE IGNORE,在严格模式下,可抑制无效值导致的错误。在这种情况下,将生成警告而不是错误。对于ENUM,值将作为错误成员(0)插入。对于SET,会将给定值插入,但无效的子字符串将被删除。例如,'a,x,b,y'的结果是'a,b',就像前面介绍的那样。