MySQL如何处理无效数据值?

MySQL处理数据的基本原则是“垃圾进来,垃圾出去”,通俗一点说就是你传给 MySQL 什么样的数据,它就会存储什么样的数据。如果在存储数据时没有对它们进行验证,那么在把它们检索出来时得到的就不一定是你所期望的内容。
 
有几种 SQL 模式可以在遇到“非正常”值时抛出错误,如果你对其他数据库管理系统比较熟悉,会发现这种行为和其他的数据库管理系统很像。
 
下面介绍 MySQL 默认情况下如何处理非正常数据和启用各种 SQL 模式时会对数据处理产生哪些影响。
 
默认情况下,MySQL 会按照以下规则来处理越界(即超出取值范围)的值和其他非正常值:
  • 对于数值列或 TIME 列,超出合法取值范围的那些值将被截断到取值范围最近的那个端点,并把结果值存储起来。
  • 对于除 TIME 列以外的其他类型列,非法值会被转换成与该类型一致的“零”值。
  • 对于字符串列(不包括 ENUM 或 SET),过长的字符串将被截断到该列的最大长度。
  • 给 ENUM 或 SET 类型列进行赋值时,需要根据列定义里给出的合法取值列表进行。如果把不是枚举成员的值赋给 ENUM 列,那么列的值就会变成空字符串。如果把包含非集合成员的子字符串的值赋给 SET 列,那么这些字符串会被清理,剩余的成员才会被赋值给列。
 
如果在执行增删改查等语句时发生了上述转换,那么 MySQL 会给出警告消息。在执行完其中的某一条语句之后,可以使用 SHOW WARNINGS 语句来查看警告消息的内容。
 
如果需要在插入或更新数据时执行更严格的检查,那么可以启用以下两种 SQL 模式中的一种:

SET sql_mode = 'STRICT_ALL_TABLES' ;
SET sql_mode = 'STRICT_TRANS_TABLES';

对于支持事务的表,这两种模式都是一样的。如果发现某个值无效或缺失,那么会产生一个错误,并且语句会中止执行,并进行回滚,就像什么事都没发生过一样。
 
对于不支持事务的表,这两种模式有以下效果。
 
1) 对于这两种模式,如果在插入或修改第一个行时,发现某个值无效或缺失,那么结果会产生一个错误,语句会中止执行,就像什么事都未发生过一样。 这跟事务表的行为很相似。
 
2) 在用于插入或修改多个行的语句里,如果在第一行之后的某个行出现了错误,那么会出现某些行被修改的情况。这两种模式决定着,这条语句此时此刻是要停止执行,还是要继续执行。
  • 在 STRICT_ALL_TABLES 模式下,会抛出一个错误,并且语句会停止执行。因为受该语句影响的许多行都已被修改,所以这将会导致“部分更新”问题。
  • 在 STRICT_TRANS_TABLES 模式下,对于非事务表,MySQL 会中止语句的执行。只有这样做,才能达到事务表那样的效果。只有当第一行发生错误时,才能达到这样的效果。如果错误在后面的某个行上,那么就会出现某些行被修改的情况。由于对于非事务表,那些修改是无法撤销的,因此 MySQL 会继续执行该语句,以避免出现“部分更新”的问题。它会把所有的无效值转换为与其最接近的合法值。对于缺失的值,MySQL 会把该列设置成其数据类型的隐式默认值,
 
通过以下模式可以对输入的数据进行更加严格的检查:
  • ERROR_ FOR_ DIVISION_ BY_ ZERO:在严格模式下,如果遇到以零为除数的情况,它会阻止数值进入数据库。如果不在严格模式下,则会产生一条警告消息,并插入 NULL。
  • NO_ ZERO_ DATE:在严格模式下,它会阻止“零”日期值进入数据库。
  • NO_ ZERO_ IN_ DATE:在严格模式下,它会阻止月或日部分为零的不完整日期值进入数据库。
 
简单来说,MySQL 的严格模式就是 MySQL 自身对数据进行的严格校验,例如格式、长度、类型等。比如一个整型字段我们写入一个字符串类型的数据,在非严格模式下 MySQL 不会报错。如果定义了 char 或 varchar 类型的字段,当写入或更新的数据超过了定义的长度也不会报错。
 
虽然我们会在代码中做数据校验,但一般认为非严格模式对于编程来说没有任何好处。MySQL开启严格模式从一定程序上来讲也是对我们代码的一种测试,如果我们没有开启严格模式并且在开发过程中也没有遇到错误,那么在上线或代码移植的时候将有可能出现不兼容的情况,因此在开发过程做最好开启 MySQL 的严格模式。
 
可通过select @@sql_mode;命令查看当前是严格模式还是非严格模式。
 
例如,如果想让所有的存储引擎启用严格模式,并对“被零除”错误进行检查,那么可以像下面这样设置 SQL 模式:

SET sql_mode ‘STRICT_ALL_TABLES, ERROR_FOR_DIVISION_BY_ZERO' ;

 
如果想启用严格模式,以及所有的附加限制,那么最为简单的办法是启用 TRADITIONAL 模式:

SET sql_ mode ‘TRADITIONAL' ;

TRADITIONAL 模式的含义是“启用严格模式,当向 MySQL 数据库插入数据时,进行数据的严格校验,保证错误数据不能插入。用于事务表时,会进行事务的回滚”。
 
可以选择性地在某些方面弱化严格模式。如果启用了 SQL 的 ALLOW_ INVALID_ DATES 模式,那么MySQL将不会对日期部分做全面检查。相反,它只会要求月份值在 1~12 之间,而天数处于 1~31 之间,即允许像‘2000-02-30’或‘2000-06-31’这样的无效值。
 
另一个制止错误的办法是在 INSERT 或 UPDATE 语句里使用 IGNORE 关键字。这样那些会因无效值而导致错误的语句,将只会导致警告的出现。这些选项能让你灵活地为你的应用选择正确的有效性检查级别。

编程帮,一个分享编程知识的公众号。跟着站长一起学习,每天都有进步。

通俗易懂,深入浅出,一篇文章只讲一个知识点。

文章不深奥,不需要钻研,在公交、在地铁、在厕所都可以阅读,随时随地涨姿势。

文章不涉及代码,不烧脑细胞,人人都可以学习。

当你决定关注「编程帮」,你已然超越了90%的程序员!

编程帮二维码
微信扫描二维码关注