记一次mysql insert ignore插入与字段not null引发的数据异常

1.异常现象

        项目中封装的插入操作使用的是insert ignore关键字，在一次数据插入过程中，某些字段显式指定为NULL，但是表结构设置的这些字段非空且有默认值，真实插入的却是字段类型的默认值。

2.测试

        新建一张测试表test，建表语句如下：

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT 'aa',
  `age` int(10) unsigned NOT NULL DEFAULT '18',
  `cost` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中name，age字段非空且有默认值，cost字段非空无默认值。

        首先，我们使用通用Insert into语句分别显式指定NULL、不指定值观察执行情况：

显式指定null时：

-- 显式指定
insert into test(name, age, cost) values (null, null, null);

结果，显而易见报非空异常；

 name，age不指定值时：

-- name,age不指定值
insert into test(cost) values (10.00);

结果符合预期，name，age正常插入且为表结构中设置的默认值；

         接下来我们使用通用Insert ignore语法分别显式指定NULL、不指定值观察执行情况：

显式指定null时：

-- 显式指定null
insert ignore into test(name,age,cost) values(null, null, null);

结果：执行成功了，就连非空且无默认值的cost字段都插入成功了，可以看到，name,age插入的数据并非我们表结构中设置的默认值，而是字段类型的默认值，而未设置默认值的cost字段也是一样的处理为字段类型的默认值；

 

 再来看看非空有默认值字段不指定值时：

-- 不指定值
insert ignore into test(cost) values(20.00);

结果：正常插入，且非空字段为表结构设置的默认值

 最后来看，非空无默认值字段不指定值时：

-- cost字段不指定值
insert ignore into test(name,age) values('add', 20);

结果也是正常插入，但是cost字段默认设置为字段类型默认值0：

 3.结论

        insert ignore into语法会吞掉非空异常且在显式置空或未设置默认值时，把字段值置为字段类型的默认值。