跟我一起学习MySQL技术内幕（第五版）：（第三章学习日记12）

3.3MySQL如何处理无效数据值
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

在过去,MySQL处理数据的基本原则是”垃圾进来,垃圾出去”.换句话说,你给MySQL什么样的数据,它就会存储什么样的数据.但是,如果在存储数据的时候并没有对他们进行验证,那么在把他们检索出来得到的就不一定是你所期望的内容.还好有几种SQL模式可与让你拒绝”坏”值,并且会抛出一个错误.

默认情况下,MySQL会按照以下规则处理越界(即超出取值范围的)值和其他非正常值.

1.对于数值列或time列,超出合法取值范围的值将被截断到取值范围最近的那个端点,并把结果保存起来.
2.对于除time以外的其他时态类型列,非法值会被转换为与该列类型相一致的"零"值.
3.对于字符串列(不包括enum或set)过长的字符串将被截断到该列的最大长度.
4.给enum或set类型列进行赋值时,需要根据列定义里的合法取值列表进行.如果把不是枚举成员的值赋给enum列,那么列的值会变成出错成员(与零值成员相对应的空字符串).如果把包含非集合成员的子字符串值赋给set列,那么这些字符串会被清理,剩余的成员才会被赋值给列.

如果在执行insert replace update load 或者alter table等语句时发生了上述转换,那么MySQL会发出警告.在执行完其中的一条语句之后,便可以使用show warnings语句来查看这种消息的内容.

如果需要在插入或更新数据时执行更严格的检查,那么可以使用以下两种SQL模式的一种L:

set sql_mode = 'strict_all_tables';
set sql_mode = 'strict_trans_tables';

对于支持事务的表,这两种模式都是一样的:如果发现某个值无效或缺失,那么结果会产生一个错误,语句会中止执行,并进行回滚.对于不支持事务的表,这两种模式有以下效果.

1.对于这两种模式,如果在插入或者修改第一行时,发现某个值无效或者缺失,那么结果会产生一个错误,鱼聚会中止执行
2.在用于修改或者修改多个行的语句里,如果在第一行之后的某个行里出现了错误,那么会出现某些行被修改的的情况.这两种严格模式决定着,这条语句此时此刻是要停止执行,还是要继续执行.
  1) 在strict_all_tables模式下,会抛出一个错误,并且语句会停止执行.因为受该语句影响的许多行都已被修改,所以这将会导致"部分更新"问题.
  2)在strict_trans_tables模式下,对于非事务表,MySQL会终止语句的执行,只有这样做,才能达到事务表那样的效果.只有当第一行发生错误时,才能够达到这样的效果,如果错误在后面的某个行上,那么就会出现某些行被修改的情况.由于对于非事务表,那些修改是无法撤销的,因此MySQL会继续执行该语句,以避免出现部分更新的问题.他会把所有的无效值转换为与其最接近的合法值.对于缺失的值,MySQL会把该列设置为隐式默认值    

严格模式实际上并不是MySQL能够执行的最严格检查 .通过以下模式当中的任何一个或者全部,都可以对输入数据进行更加严格的检查

1.error_for_divisioin_by_zero :在严格模式下,如果遇到以零位除数的情况,它会组织数据进入数据库(不在严格模式下,会产生一条警告并在数据库中插入NULL)
2.no_zero_date:在严格模式下吗,它会阻止零日期值进入数据库
3.no_zero_in_date:在严格模式下,它会阻止月或日部分为零的不完整日期值进入数据库.

例如,如果想启用所有的严格模式,并对被清零错误进行检查,那么可以这样设置SQL模式:

set sql_mode = 'strict_all_tables,error_for_division_by_zero';

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

set sql_mode = 'traditional';

traditional 模式的含义是”启用两种严格模式,外加一大堆的其他限制”.这与其他传统的SQL DBMS在数据检查方面的行为比较接近

也可以选择性的在某些方面弱化严格模式,如果启用了SQL的allow_invalid_dates模式,那么MySQL将不会对日期部分做全面检查.相反,只会要求月份值在1~12而天数处于1~31(即允许像02-30之类的无效日期).

另一个制止错误的方法是,在insert或update语句里使用ignore关键字.有了ignore之后,那些会因无效值而导致错误的语句,将只会导致警告的出现.