处理丢失和无效的数据值

1.MYSQL针对无效的值采用了一种“forgiving的方式”即采用与合法值最接近的值插入数据库。例如一个unsigned的列值时，当输入负值是会转换为0插入。可能不会被回滚。

可以通过设定SQL mode通过sql_mode系统变量来执行严格的管理。

默认的情况是slq_mode='',意味着没有限制，使用set sql_mode=' ';

Set sql_mode='STRICT_TRANS_TABLES';

Set  sql_mode='STRICT_ALL_TABLES';设置后可以阻止例如越界，null例如指定not null列。

其他的SLQ mode如tranditionnal，能够使严格的模式加上其他的限制在日期检查和除数为0.

insert into t (i) values(43);   //MySql执行自动string-to-number转换对于第二天语句

insert into t(i) values('43');

下面的语句也可以执行转换：insert into t(i) values('43x');也会进行转换但是会产生一个警告可以使用strict  SQL mode

5.8.1 处理丢失的值

create table t(

   i int null,

  j int not null,

  k int default -1

);

对于这个表

insert into t (i) values(0);

insert into t (i,k) values(1,2);

insert into t (i,k) values(1,2),(3,4);

insert into t values();

最后一个语句表示，空值表示使用默认值对于所有的列。

MySQL处理丢失的值：

1. 列的定义包含DEFAULT语句，如果没有明确的default的语句这个列将采用空值，因此i列有个DEFAULT NULL的定义

2.如果一个列定义没有DEFAULT语句，丢失的值处理取决于SQL 模式是否这个表示是transactional：

    2.1如果严格的模式不是有效的，mysql插入没有明确的默认值将产生一个警告

    2.2 如果严格模式是有效的，一个错误将发生对于transactional表（和语句产生回滚）。错误发生在非transactional表上，但是部分更新可能导致：如果错误发生在第二行或以后的行，那么前面的行可能已经被插入。

   5.8.2 处理无效的值在非严格的模式

一般，mysql将执行类型转换基于限制条件（列定义的）。如下：

1.当你插入和更新列值是如insert replace，update或者load data infile。

2.当你干部列的的定义使用alert table

3.当你指定一个默认值使用default语句时

如果MYsql没有运行在严格模式，他将修正无效的输入值到合法的值和产生警告信息。这些消息经通过show warning语句来展示。

  1.Conversion of out-of-range values to in-range values.如果你试图存储一个比最小值还小的值时就存储最小值。如果存储比最大值还大的值时就存储最大的值。

   2.String truncation  字符串值太长需要被截断来匹配列。如果你试图存储‘Sakila’到一个char（4）列，mysql存储它作为‘Saki’和抛弃剩余的字符

   3.Enumeration 和 set value conversion. 如果值不在Enum定义，mysql将转换他为‘’字符串。如果一个值不在set列，将抛弃这些元素，仍旧保留合法元素。

    4.Conversion to data type default。如果你试图存储一个值不能被转换为列数据类型。Mysql经存储不明确的默认值，例如，如果尝试存储‘Sakila’到int 列，mysql存储0.对于日期类型是0000-00-00

    5.处理Handling assignment of null to not null columns。分配NULL到非空列取决于是否这个分配发生在一个单行或多行的插入语句。对于一个单行的插入，一个错误发生和语句失败。对于一个多行的插入，mysql分配列的不明确的默认值对于它的数据类型。

使用alert来改变列的数据类型时，将会导致一些列的值改变。如果改变int到tinyint，任何值在tinyint的将被剪切到tinyint的附近。

如果列被改变为not null，那么mysql将会转换null值到这不明确的值

接下的表表明几种字符串值的类被处理转换为date或int数据类型。

5.8.3 处理无效值在严格的模式

 输入的值可能是无效的对于下面的原因：

     1.对于一个数值或临时的列，一个值坑能越界

     2. 对于string可能太长

    3.对于enum不在范围，对于set 不包含插入的元素

   4.对于not null的列，一个null值肯能插入

在严格的模式，服务器将拒绝越界，有不正确数据类型和丢失列值（对于那些没有default的列）。严格的模式能够使用strict_tans_tables和strict_all_tables

   strict_trans_table:能够采用严格的行为对于错误能够回滚或取消没有干嘛表到数据正在进入的状态。如果错误发生对于transactional表，这语句将抛弃和回滚。对于一个non-transactional表，这语句将被抛弃没有改变表如果一个无效的值发生在当行插入或者多行插入的第一行。否则，为了避免部分更新对于non-transactional表，mysql将修正任何无效的值到合法的值然后插入他和产生一个警告。

    strict_all_tables:和strict_tans_table相似但是将引发non-transaction表抛弃没有发生在第二行或者之后的多行插入。这意味着一个部分更新可能发生，因为更早插入的语句将已经被插入。

   5.8.4 Enabling additional input data restrictions

     可能的模式可以使用：

    1. Divison by zero 使用ERRO_FOR_DIVISION_BY_ZERO值，将产生错误。set sql_mode=‘stricat——all_tables,error_for_division_by_zero’;

     2.默认情况下，mysql运行‘zero’日期和有0部分的日期。例如日期被允许尽管你能够采用严格模式，但是如果你想阻止他们，可以使用NO_zero_date 和no_zero_in_date

   

    这traditional模式值是一个组合模式能够严格模式和其他限制被描述。如果你想要Mysql服务器关于输入数据检查

5.8.5  覆盖输入数据限制

为了覆盖输入数据限制，使用insert ignoreor update ignore 而不是使用insert or update。这ignore关键字经引起mysql使用non-strict行为对于这个语句。

 如果你想要使用relaxed日期检查，需要set sql_mode='allow_invalid_dates';

set sql_mode='strict_all_tables,allow_invalid_dates';