insert...on duplicate key update语法

result =  select * from table;
if result = 0
   insert the record into table;
else
   update the record;

这样的操作乍一看没有什么问题，但是仔细分析分析，还是有些瓶颈的，目前来看，我能分析到的瓶颈有两个，

其一:

每次要执行2个SQL，效率比较差；

其二：

当我们在高并发的情况下跑这条语句，如果程序崩溃，不能保证操作的原子性。

基于这个问题，同事提出了insert...on duplicate key update这个办法，而我的第一反应是replace方法，因为我之前从来没用过insert...on duplicate key这个语法，所以专门把这两个语法研究了一下，下面简单分析下这两个语法:

01

insert...on duplicate key方法

首先我们看看这个语法的具体写法，我简单写一个例子,内容大致如下：

1、首先创建一个包含id,name,age的表，其中id是主键；

2、在这个表中插入一条id=1的记录；

3、使用insert...on duplicate key update语法插入一条id=2的记录；

4、使用同样的语法更新id=1的记录；

mysql 23:12:32>>create table test_1( 
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> age int not null
    -> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql 23:13:26>>insert into test_1 values (1,'yyz',16);
Query OK, 1 row affected (0.01 sec)

mysql 23:13:58>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | yyz  |  16 |
+----+------+-----+
1 row in set (0.00 sec)


mysql 23:14:36>>insert into test_1 (id,name,age) values (2,'yyz',18) on duplicate key update age=18;
Query OK, 1 row affected (0.01 sec)

mysql 23:15:08>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | yyz  |  16 |
|  2 | yyz  |  18 |
+----+------+-----+
2 rows in set (0.00 sec)

mysql 23:15:17>>insert into test_1 (id,name,age) values (1,'yyz',18) on duplicate key update age=18;
Query OK, 2 rows affected (0.00 sec)

mysql 23:15:28>>select * from test_1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | yyz  |  18 |
|  2 | yyz  |  18 |
+----+------+-----+
2 rows in set (0.00 sec)

我们可以看到，当主键存在的时候，相当对id=1的那条记录执行了:

update test_1 set age=18 where id=1;

这就是insert...on duplicate key update语法的作用，可以分析到，当发生主键冲突的时候，可以直接进行update操作，这个update操作里面可以更新任意想要更新的列；而没有主键冲突的时候，相当于对这个表进行了一次插入操作。

使用这个方法可以替代业务方的那种2个SQL的写法，也能够解决数据的原子性问题。

需要注意的是，这个语法不是SQL的通用语法，而是MySQL特有的一种语法。还有一点需要注意，当表中存在两个列都冲突的情况下(例如age也是一个unique索引，id和age同时冲突的情况)，只会更新其中的一条记录，而且，当出现多个unique索引的时候，对于SQL语法where条件的先后顺序会变得很敏感，不同的顺序修改的记录行也不相同(这需要了解索引部分的知识)。

简单的分析这个语句，个人认为，在多个客户端同时执行这个SQL的时候，也就是高并发情况下，虽然解决了数据的原子性问题，但是这个语句会带来另外一个问题，那就是死锁，当我们执行这个语句的时候，这个语句会先在表test_1中找到是否存在id=1的行，因为id是主键，所以很快就定位到这一行上面。在对该现有的行加上S（共享锁）锁，然后返回这行数据给mysql，mysql执行duplicate后的update操作，先对该记录加上X（排他锁），接下来需要执行update操作。由于大量的连接都在执行这个操作，因此在抢夺行锁上产生了大量的竞争，因为行锁的分配也涉及了自旋锁。很多连接就卡在了自旋锁上面，白白的消耗cpu资源(这个我还没测试，但是想想应该也是这样)。

02

Replace操作

Replace语句。使用Replace插入一条记录时，如果不重复，Replace就和Insert的功能一样，如果有重复记录，Replace就使用新记录的值来替换原来的记录值。

使用REPLACE的最大好处就是可以将Delete和Insert合二为一，形成一个原子操作。这样就可以不必考虑在同时使用Delete和Insert时添加事务等复杂操作了。

在使用Replace时，表中必须有唯一索引，而且这个索引所在的字段不能允许空值，否则Replace就和Insert完全一样的。

在执行Replace后，系统返回了所影响的行数，如果返回1，说明在表中并没有重复的记录，如果返回2，说明有一条重复记录，系统自动先调用了Delete删除这条记录，然后再记录用Insert来插入这条记录。

03

不同之处

有了上面的知识储备，这两条命令的不同之处就显而易见了，replace是删除记录，然后再重新insert，而insert...on duplicate key update是直接在该条记录上修改，所以二者的差别主要有以下两处：

1、当表中存在自增值的时候，如果表中存在某条记录，replace语法会导致自增值+1，而insert...on duplicate key update语法不会；

2、当表中的某些字段中包含默认值的时候，replace操作插入不完全字段的记录，会导致其他字段直接使用默认值，而insert...on duplicate key update操作会保留该条记录的原有值。