mysql ignore into语法

mysql ignore into

该语法是mysql中可以实现类似一个插入时不存在则插入,存在则不做插入的语法(说类似的原因等会解释)
该SQL的使用主要是为了避免重复数据需要先查询一次库,根据返回值来判断执行插入还是更新的逻辑,把先查数据是否存在交给mysql判断,减少一次库连接
准备表:

CREATE TABLE `mytest`.`test`  (
  `id` varchar(255) NOT NULL,
  `school` varchar(255) NOT NULL,
  `grade` varchar(255) NOT NULL,
  `course` varchar(255) NOT NULL,
  `test_name` varchar(255) NOT NULL,
  PRIMARY KEY (`school`, `grade`, `course`),
  UNIQUE INDEX `un_id`(`id`) COMMENT '该索引是为了id保持唯一'
);

该表结构:id不做主键,由school,grade,course做联合主键来控制数据的重复,并给id字段添加唯一索引,表里的所有字段都是不可为空

ID为什么不设置成主键而是设置成非空和唯一的原因:我的逻辑中每次执行程序都会生成一个UUID来填充ID字段,如果我使用ID是主键会影响ignore的结果

首先,来看下 ignore写法的效果
使用普通的INSERT INTO 写法

将该数据再次插入,断言一定会报错,因为联合主键限制

与断言一样,这钟情况就是需要代码中先查下库看是否存在,存在则应该调用更新

使用IGNORE INTO写法修改语法后,不修改数据后再次执行

可以看到结果,并没有像INSERT INTO 写法报错,而只是影响0行,也就是没有插入数据
,这样不用查询库,全部使用IGNORE INTO写法来做插入就可以实现省略掉查询是否存在的这次连接

---

接下来解释说的另外两个问题ID为啥得取消主键和为什么在开头时候说类类似的原因

1.说类似的原因
首先来明确下IGNORE INTO 这个语法的真正含义:
网上大部分的说法是

insert into表示插入数据，数据库会检查主键（PrimaryKey），如果出现重复会报错

但是实际上IGNORE 是忽略错误(这个忽略类似于JAVA的运行时异常),也就是说,语法有问题在执行时就会报错,而如果说你语法正确但是有类似于不可为空项为空这样的错误,这条SQL也会顺利执行,而不是报错

来验证下这个结论是不是真的:
当时创建表时,所有的字段都设置不可为空,这时库中已经有一条数据:

做一个测试,我修改sql,不为主键且不可为空的test_name字段不再赋值,看查询结果,这个字段不可为空,这次的SQL不再给这个字段赋值
逻辑上的结果应该是报错test_name字段没有默认值
实际执行结果如下:

可以看到这次没有给test_name字段赋值,但是这条SQL仍然执行成功,只是没有影响行

那会不会是这个表有问题? 那使用INSERT INTO的写法来看看结果

好家伙,INSERT INTO这个字段报错,而IGNORE INTO写法正常执行
可能是因为主键冲突导致SQL没有做插入,而不是这个字段的问题?那我再变一变
把值改一次,避免主键冲突
逻辑执行结果:这条数据因为避免了主键IGNORE就不会限制,就可以正常插入
执行结果:

可以看到,这次执行仍没有给test_name这个不可为空的字段赋值,但是SQL仍正常执行,而且影响行数还是0
这次可以确定了这个说法:IGNORE 是忽略错误,想要实现类似于不存在则插入,需要保证SQL除了主键冲突不会产生其他异常,其他异常会被IGNORE 处理掉,导致明明避免了主键重复但是仍插入0

2.为什么ID不设置主键
ID这个问题就非常明显了,因为我的程序是每次生成UUID导致如果是主键就一定会被
IGNORE做条件,导致每次都是插入,另外因为我的主键是唯一索引,id不修改也会导致报错,导致影响行数是0

---

另一个问题,使用IGNORE必须要把所有要做条件的字段设置成主键吗?
修改表结构,该表取消所有的字段,将school,grade,course字段做联合唯一索引

执行SQL
执行一条已经存在的数据,断言影响行数为0
执行一条库中不存在的数据,断言:影响行数为1

结果:不一定是是主键,是唯一索引也会被IGNORE做条件

---

总结:
1.IGNORE只是类似于Oracle的merge方法,并不能完全实现根据某些字段来判断执行更新还是插入
2.IGNORE使用必须保证除了主键不会有其他异常,以此来避免次次插入0
3.IGNORE使用时你的字段可以为也可以是联合唯一索引