replace into与insert into ...on duplicate key update的区别以及陷阱

对于replaceinto以及insertinto...onduplicatekeyupd...



对于replace into 以及insert into ...on  duplicate key update 语句，上一篇以及分析了其在数据库中的执行过程，接下来，我们将讨论这两个语句在实际应用中，需要注意的区别以及"陷阱“。该篇文章对实际应用有非常重要的指导作用，请仔细看完

首先我们来分析replace into 语句。

CREATE TABLE "reptest" (

"a" int(11) NOT NULL AUTO_INCREMENT,

"b" int(11) DEFAULT NULL,

"c" varchar(200) DEFAULT NULL,

PRIMARY KEY ("a")

) ENGINE=InnoDBAUTO_INCREMENT=16 DEFAULT CHARSET=utf8 ；

mysql> select * from reptest;

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

| a | b    | c    |

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

| 1 |    1 | NULL |

| 2 |    2 | NULL |

| 3 |    4 | NULL |

| 4 |    5 | NULL |

| 5 |    6 | NULL |

| 6 |    7 | NULL |

| 7 |    8 | NULL |

| 8 | NULL | 8    |

| 9 | NULL | 9    |

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

9 rows in set (0.02 sec)

mysql> replace into   reptest(a,b) values(9,9);

Query OK, 2 rows affected (0.04 sec)

mysql> select * from reptest;

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

| a | b    | c    |

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

| 1 |    1 | NULL |

| 2 |    2 | NULL |

| 3 |    4 | NULL |

| 4 |    5 | NULL |

| 5 |    6 | NULL |

| 6 |    7 | NULL |

| 7 |    8 | NULL |

| 8 | NULL | 8    |

| 9 |    9 | NULL |

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

9 rows in set (0.01 sec)

mysql> show create table  reptest;

CREATE TABLE "reptest" (

"a" int(11) NOT NULL AUTO_INCREMENT,

"b" int(11) DEFAULT NULL,

"c" varchar(200) DEFAULT NULL,

PRIMARY KEY ("a")

) ENGINE=InnoDBAUTO_INCREMENT=16 DEFAULT CHARSET=utf8

我们看到，对于replace into语句，如果没有指定某字段的值，则以默认值代替。这个逻辑也是跟insert语句完全一样， 但当出现键值冲突时，如果没有指定某个字段的值，则这个字段就会修改成默认值----如果不注意这个细节，将导致原本不想修改的字段却被”意外“改变。

接下来我们来看看Insert into .. on duplicate key update 语句。依然是对a=9的这行操作。

mysql> select * from reptest;

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

| a | b    | c    |

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

| 1 |    1 | NULL |

| 2 |    2 | NULL |

| 3 |    4 | NULL |

| 4 |    5 | NULL |

| 5 |    6 | NULL |

| 6 |    7 | NULL |

| 7 |    8 | NULL |

| 8 | NULL | 8    |

| 9 |    9 | NULL |

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

9 rows in set (0.01 sec)

mysql> insert into  reptest (a,c) values(9,'9') on duplicate key update c=values(c);

Query OK, 2 rows affected (0.04 sec)

mysql> select * from reptest;

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

| a | b    | c    |

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

| 1 |    1 | NULL |

| 2 |    2 | NULL |

| 3 |    4 | NULL |

| 4 |    5 | NULL |

| 5 |    6 | NULL |

| 6 |    7 | NULL |

| 7 |    8 | NULL |

| 8 | NULL | 8    |

| 9 |    9 | 9    |

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

9 rows in set (0.02 sec)

我们看到，采用insert into ...on duplicate key update 语句，对于不指定修改的列，则不修改。

通过上面的分析，我们知道了这两个语句之间的区别。 接下来，我们会演示这两个语句的另外的一个”陷阱“。

我们将这个reptest在b列上增加一个唯一性索引，然后继续进行相关测试。

mysql> alter table reptest add  unique key (b);

Query OK, 0 rows affected (0.14 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from reptest;

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

| a | b    | c    |

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

| 1 |    1 | NULL |

| 2 |    2 | NULL |

| 3 |    4 | NULL |

| 4 |    5 | NULL |

| 5 |    6 | NULL |

| 6 |    7 | NULL |

| 7 |    8 | NULL |

| 8 | NULL | 8    |

| 9 |    9 | 9    |

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

9 rows in set (0.01 sec)

本来的数据如上，然后我们执行下面的replace into语句。

mysql> replace into reptest(b) values(9);

Query OK, 2 rows affected (0.03 sec)

结果发现原来的a=9的行，完全消失。新行是a=16,b=9,c=null.

mysql> select * from reptest;

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

| a  | b    | c    |

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

|  1 |    1 | NULL |

|  2 |    2 | NULL |

|  3 |    4 | NULL |

|  4 |    5 | NULL |

|  5 |    6 | NULL |

|  6 |    7 | NULL |

|  7 |    8 | NULL |

|  8 | NULL | 8    |

| 16 |    9 | NULL |

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

9 rows in set (0.03 sec)

这个完全符合replace into 语句发现冲突时，把原来的行删除，然后插入新行的逻辑。

咱们再来做一次replace into 操作，语句如下：

replace into reptest(a,b) values(16,7);   请问一下，再操作完之后，不去查询表的记录，哪些朋友会知道最后的结果？最后表中是否有a=16,b=7的行？我们还是执行之后，查看表的数据然后再做确认。

mysql> select * from reptest;

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

| a  | b    | c    |

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

|  1 |    1 | NULL |

|  2 |    2 | NULL |

|  3 |    4 | NULL |

|  4 |    5 | NULL |

|  5 |    6 | NULL |

|  6 |    7 | NULL |

|  7 |    8 | NULL |

|  8 | NULL | 8    |

| 16 |    9 | NULL |

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

9 rows in set (0.03 sec)

mysql> replace into reptest(a,b) values(16,7);

Query OK, 3 rows affected (0.05 sec)

mysql> select * from  reptest where a=16 and b=7;

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

| a  | b    | c    |

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

| 16 |    7 | NULL |

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

1 row in set (0.02 sec)

mysql> select * from reptest;

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

| a  | b    | c    |

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

|  1 |    1 | NULL |

|  2 |    2 | NULL |

|  3 |    4 | NULL |

|  4 |    5 | NULL |

|  5 |    6 | NULL |

|  7 |    8 | NULL |

|  8 | NULL | 8    |

| 16 |    7 | NULL |

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

8 rows in set (0.01 sec)

确实存在a=16,b=7的数据，但有没有发现，执行完replace into语句之后，表中的记录反而少了一行？ 原来是9行，执行完replace into语句之后，只有了8行。从replace into 语句返回了3 rows affected  的结果来看，猜测是删除了2行，插入了1行，导致表的总行数少了1行。仔细观察结果发现，少了原来a=6,b=7的那一行数据。

我们来仔细分析一下replace into reptest(a,b) values(16,7) 的执行过程：

第一步： 首先在主键（表）上进行插入，发现已经存在a=16，b=9的记录，即键值冲突，然后调用ha_update_row(table->record[1],table->record[0]))将其更新为a=16,b=7的记录。原来a=16,b=9的记录消失。

第二步：在主键上插入（实际上最后因为key冲突而了更新）完毕之后，就去b列索引上插入，在b列索引上插入时，同样发现键值冲突，但在唯一性索引上发现冲突后，不能跟在表上以同样的方式处理----因为仅仅替换唯一性索引上的记录是不够的，还得把表上的记录也更新了（实际上是删除）。所以得把表上的b=7记录的删除----即对应的就是a=6的记录删除。 因此a=6的记录完全消失。

因此，对于有主键，且有唯一性索引的情况下，执行replace into语句，最终在数据库中产生的影响，需要遵循先操作主键（表），然后再操作唯一性索引的顺序。假如有多个唯一性索引，则一个replace语句，可能导致多条记录被删除。这个陷阱，对于研发人员来说，是务必知晓的细节，否则可能酿成大错。

讲完了replace into 语句，我们再来分析一下insert into .on duplicate key update 语句的影响。

mysql>  select * from reptest;

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

| a  | b    | c    |

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

|  1 |    1 | NULL |

|  2 |    2 | NULL |

|  3 |    4 | NULL |

|  4 |    5 | NULL |

|  5 |    6 | NULL |

|  7 |    8 | NULL |

|  8 | NULL | 8    |

| 16 |    7 | NULL |

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

8 rows in set (0.01 sec)

mysql> insert  into reptest(a,b) values(16,7) on duplicate  key  update b=values(b);

Query OK, 0 rows affected (0.01 sec)

mysql> select * from reptest;

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

| a  | b    | c    |

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

|  1 |    1 | NULL |

|  2 |    2 | NULL |

|  3 |    4 | NULL |

|  4 |    5 | NULL |

|  5 |    6 | NULL |

|  7 |    8 | NULL |

|  8 | NULL | 8    |

| 16 |    7 | NULL |

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

8 rows in set (0.03 sec)

当我们执行insert  into reptest(a,b) values(16,7) on duplicate  key  update b=values(b); 语句时，返回的结果是 0 rows affected ，很好，完全符合逻辑，因为数据库已经存在a=16,b=7的行，所以没有行发生变更。

我们再来执行跟上面的replace into的类似insert into .. on duplicate key update  。。。的sql,冲突既发生在主键上，又发生在唯一性索引上面的的。

mysql> insert  into reptest(a,b) values(16,8) on duplicate  key  update b=values(b);

ERROR 1062 (23000): Duplicate entry '8' for key 'b'

出现的结果是操作失败。没有像replace insert 那样，来删除一行来成全自己的事务。

作者依然不甘心， 还想继续测试。。。。。。

再来试试下面这个sql .

mysql> insert  into reptest(a,b,c) values(16,8,'8') on duplicate  key  update c=values(c);

Query OK, 2 rows affected (0.05 sec)

执行成功了，perfect , 表中最后的记录会是什么样的呢？是否会像replace into那样的操作，最后少一行？如果少，少的是哪一行？

咱们还是来查查表中的数量吧。

mysql> select * from reptest;

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

| a  | b    | c    |

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

|  1 |    1 | NULL |

|  2 |    2 | NULL |

|  3 |    4 | NULL |

|  4 |    5 | NULL |

|  5 |    6 | NULL |

|  7 |    8 | NULL |

|  8 | NULL | 8    |

| 16 |    7 | 8    |

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

8 rows in set (0.01 sec)

然后还是8行，数据没有少。到此，你是否觉得insert into ... on  duplicate  key 比replace into 完美呢？ 既然这样，replace into 还有毛用？ 好像是多余的。。。。。

再此，作者可以批评亲对实验现象观察得不够仔细莫？（。。。哈哈哈，纯属玩笑。IT屌丝太闷，开开玩笑也蛮好，请见谅。。。 ）

我们再来看看我们执行的语句。insert  into reptest(a,b,c) values(16,8,'8') on duplicate  key  update c=values(c);  我们执行语句的目的是想插入（16，8，8)的行，sql也执行成功了，但数据库有这样的记录不？

mysql> select * from  reptest where a=16 and b=8 and c=8;

Empty set (0.06 sec)

压根没有这条记录------又是一个好大的”坑“啊。

原因：对于在mysql中执行上面的允许冲突的语句，首先在主键上进行insert,如果发现冲突，则执行语句中update后面的更新操作。 然后就变更就完成。 在此列中，没有再去管b是什么值。

作者还想进行一个测试，因为a列是自增字段，我们把a列拿掉在做insert,看结果为如何？（初始数据还是上面的查询结果).

mysql> insert into  reptest (b,c) values(8,'8') on duplicate key update c=values(c);

Query OK, 2 rows affected (0.02 sec)

最后表中的结果会如何呢？是有（17，8，8）的记录，还是（7，8，8）的记录？ 让我们拭目以待吧。

mysql> select * from reptest;

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

| a  | b    | c    |

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

|  1 |    1 | NULL |

|  2 |    2 | NULL |

|  3 |    4 | NULL |

|  4 |    5 | NULL |

|  5 |    6 | NULL |

|  7 |    8 | 8    |

|  8 | NULL | 8    |

| 16 |    7 | 8    |

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

8 rows in set (0.02 sec)

您是否猜（答）对？老实说，作者在没有仔细研究之前，也猜不对，不知道replace into与insert into .. on duplicate key update 语句的区别有多少。

结果出炉，原来是（7,8,8),同时，表的自增字段的下一个值也增加了1.

到此，我们是否明白了insert into ... on duplicate key update语句，应对冲突，到底采取何种方式？

在作者看来：1. 对于insert into .. on duplicate key update这样的语句，如果没有冲突，跟replace into ,insert into的执行过程完全一样，结果也一样。（这点是废话，接触过mysql的人都知道）。2.当执行insert的过程中，发现失败且是key冲突，则进入冲突处理，对于insert into .. on duplicate key update这样的语句，则直接在原来的行上进行修改。即冲突发生在哪行，就在哪个行上修改。对于上面的语句insert into  reptest (b,c) values(8,'8') on duplicate key update c=values(c); ，本来是要执行 insert into (a,b,c) values(17,8,'8'),执行过程是:首先在主键上进行插入（17，8，‘8') ,然后再去唯一性索引上插入（8，17），因为唯一性索引上已经有（8，7），（即b=8,a=7的索引行），所以冲突。因此，就在这样行进行更新，之前在主键（表）上插入的行（ 17,8,'8'）进行回滚。

所以最终结果是（7，8，’8').

关注微信工众号，实时获取pig分享的纯干货原创文章。

    关注 数据库随笔