当前位置:首页 > 叨叨念念 > MySql之INSERT INTO…ON DUPLICATE KEY UPDATE详解

MySql之INSERT INTO…ON DUPLICATE KEY UPDATE详解

叨叨念念 / 星之宇 / 2021-8-15 8:00 / 浏览:3513 / 评论:0

在我们的日常开发中,经常会遇到过这样的情景:查看某条记录是否存在,不存在的话创建一条新记录,存在的话更新某些字段。

你会采用怎么样的处理方式呢?

$result = mysql_query('select * from xxx where id = 1');
$row = mysql_fetch_assoc($result);
if($row){
    mysql_query('update ...');
}else{
    mysql_query('insert ...');
}

其实,MySql已经考虑到了这点,提供了insert into … on duplicate key update的语法,该语法在insert的时候,如果insert的数据会引起唯一索引(包括主键索引)的冲突,即这个唯一值重复了,则不会执行insert操作,而执行后面的update操作。

注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!


例如,现在有表test,test表中有字段a,在a上有主键或者唯一索引,并且表中只有一条a=1, b=1的数据,现在执行如下的sql:

insert into test (a,b) values (1,2) on duplicate key update b = b + 1;

因为a=1的记录已存在了,所以不会执行insert,而会在该条记录上执行update语言`b=b+1`,记录会变成a=1,b=2

insert into test (a,b) values (2,2) on duplicate key update b = b + 1;

因为a=2的记录不存在,所以执行insert

如果行作为新记录被插入,则受影响的行为1;如果原有记录被更新,则受影响行为2;如果原有记录已存在,但是更新的值和原有值相同,则受影响行为0。


多唯一索引冲突的情况说明:

为了测试方便,我们建了下面的数据表:

create table test(
a int not null primary key,
b int not null UNIQUE key,
c int not null
)

输入数据:insert into test values(1,1,1), (2,2,2);

然后执行:insert into test values(1,2,3) on duplicate key update c = c + 1;

因为a和b都是唯一索引,插入的数据在两条记录上产生了冲突,然而执行后只有第一条记录被修改:

mysql> select * from test;

+---+---+---+

| a | b | c |

+---+---+---+

| 1 | 1 | 2 |

| 2 | 2 | 2 |

+---+---+---+

2 rows in set (0.00 sec)

上面的语句等同于:update test set c=c+1 where a=1 or b = 2 limit 1;

如果a=1 or b =2匹配多条记录,只有第一条记录被更新。所以,一般情况下,我们应该避免在有多个唯一索引的表中使用on duplicate key update。


使用values()方法,在update中可以使用values()方法引用在insert中的值,如:

insert into test values(1,3,5) on duplicate key update c = values( c )+ 1;

该语句会使a=1的记录中c字段的值更新为6,因为values(c)的值是引用的insert部分的值,在这个例子中就是insert into test values(1,3,5) 中的5,所以最终更新的值为6。

注意:VALUES()函数只在INSERT...UPDATE语句中有意义,其它时候会返回NULL。


last_insert_id()

如果表含有auto_increment字段,使用insert … on duplicate key update插入或更新后,last_insert_id()返回auto_increment字段的值。


并发控制

在使用例如MyISAM这样的表级锁的分区表上使用insert … on duplicate key update时,会锁住所有分区表,而在例如使用InnoDB这样的行级锁的分区表上则不会锁住所有分区表。


delayed选项

delayed选项会被忽略,当我们使用on duplicate key update时。

目前有 0 条评论

    • 昵称
    • 邮箱
    • 网址