美文网首页
Mysql之浅析INSERT ON DUPLICATE

Mysql之浅析INSERT ON DUPLICATE

作者: 穹柏 | 来源:发表于2022-01-04 14:00 被阅读0次

    前言

    如果不特别指出,默认mysql版本为8.0

    简介

    往数据库中插入记录时,如果发生唯一索引值冲突,insert on duplicate允许进行进一步的crud操作。伪代码如下:

    insert record
    IF exist duplicate record THEN
      do something on duplicated rows
    ELSE 
      do nothing
    END IF
    

    具体用法

    先初始化将要用到的表跟数据

    create table t1
    (
        id bigint primary key auto_increment,
        a  integer unique,
        b  integer default 999
    );
    INSERT INTO test_insert_on_dup_update(id, a)
    VALUES (1, 1);
    INSERT INTO test_insert_on_dup_update(id, a)
    VALUES (5, 5);
    INSERT INTO test_insert_on_dup_update(id, a)
    VALUES (10, 10);
    

    1. 单个唯一索引插入冲突

    通过如下sql进行数据插入

    insert into t1(a,b) values(1,199) on duplicate update b = 1;
    

    因为表中已经存在a=1的记录,这个时候会触发on duplicate后面的update操作,将a=1的记录的b从999修改为1.

    在这种情况下,上面的sql等价于

    update b=1 where a = 1;
    

    1.2 多个唯一索引插入冲突

    如果插入的记录与a跟b上的索引值都发生了冲突,且发生冲突的记录有多条会怎么样呢?

    insert into t1(id, a) values(1,5) 
      on duplicate update b = 1;
    

    因为a=1跟b=5都存在,这个时候有两行记录与即将插入的记录有冲突。按照前面介绍的规则来看,貌似id=1a=5这两条记录的b都会被更新成1。但事实是只有一条有冲突的记录会应用on duplicate后面的子句。而这条被命中记录就是在所有满足条件的记录中,其id值聚集索引叶节点的链表中最靠前的那条记录。在本例中也就是id=1的那条记录。该sql的实际效果等价于

    update t1 set b=1 where id=1 or a=5 limit 1;
    

    所以,当发生这种情况时,我们很难去预料语句的行为。应当尽量避免这种情况

    1.3 子句获取插入列即将插入的值

    在8.0.19之前

    insert into t1(id, a) values(1,5) 
      on duplicate update b = values(a);
    

    等价于

    insert into t1(id, a) values(1,5) 
      on duplicate update b = 5;
    

    values(a)获取的是原本准备插入的a=5这个值.

    要注意的是:这种写法将在8.0.20版本被废弃,对应的功能在未来会被移除。

    在8.0.19之后

    insert into t1(id, a) values(1,5) as new
      on duplicate update b = new.a;
    

    这里为新插入的记录设置了一个别名new,通过这个别名可以获取到准备插入的数据。另外,还可以基于这个别名更进一步的为里面的每个列设置别名

    insert into t1(id, a) values(1,5) as new(x,y)
      on duplicate update b = x;
    

    1.4 根据查询结果进行插入

    insert into t1(id, a) select x,y from t2
      on duplicate update b = x
    

    像这类语句,由于插入的顺序依赖于select的结果集里行的顺序,而mysql不能保证这个select的结果集在主从上的顺序是完全一致的,这就会导致基于statement的主从复制会出现数据不一致的问题。而基于行的复制模式不存在这个问题。所以,如果存在这类子句中带select的sql,注意将复制模式设置为row-based或者mixed

    跟锁相关的部分

    根据不同的隔离级别,有如下特征:

    1. repeatable read

      • 普通唯一索引(非主键)发生唯一key冲突,这种情况会锁住该索引以及聚集索引。
      • 主键值发生冲突。则会为发生冲突的主键值设置行锁
    2. READ COMMITTED:会为冲突的索引值设置行锁

    相关文章

      网友评论

          本文标题:Mysql之浅析INSERT ON DUPLICATE

          本文链接:https://www.haomeiwen.com/subject/yzmqcrtx.html