replace into 与insert into on duplicate key update的区别

大多数熟悉mysql的用户来说,碰到表数据替换的需求一般会想到下面两种SQL来处理: 1.replace into

2.insert into …on duplicate key update

以上都不属于标准SQL,也就是mysql中的"方言"。

那么用那种方式比较好呢?

首先我们来分析下这两种语句的差异:

1.replace into 语句

CREATE TABLE dianwoba (

d int(11) NOT NULL AUTO

INCREMENT,

w int(11) DEFAULT NULL,

b varchar(200) DEFAULT NULL,

PRIMARY KEY (d)

) ENGINE=InnoDB AUTO

INCREMENT=1 DEFAULT CHARSET=utf8;

select * from dianwoba;

mysql> replace into dianwoba(d,w) values(5,6);

Query OK, 2 rows affected (0.04 sec)

mysql> select * from dianwoba;

我们看到,对于replaceinto语句,当出现键值冲突时,如果没有指定某个字段的值,则这个字段就会修改成默认值如:(5,null,6)->(5,6,null)

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

mysql> insert into dianwoba (d,w) values(5,'7') on duplicate key update b=values(b);

Query OK, 2 rows affected (0.04 sec)

mysql> select * from dianwoba;

我们看到,采用insert into …on duplicate key update 语句,对于不指定修改的列,则不修改如:(5,6,null)->(5,6,7)

通过上面的分析,我们知道了这两个语句之间的区别。

接下来,我们会演示这两个语句的另外的一个”陷阱“。我们将这个dianwoba在w列上增加一个唯一性索引,然后继续进行相关测试:

mysql> alter table dianwoba add unique key (w);

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

然后我们执行下面的replace into语句。

mysql> replace into dianwoba(w) values(6);

Query OK, 2 rows affected (0.03 sec)

mysql> select * from dianwoba;

(5,6,7)->(6,6,null)

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

咱们再来做一次replace into 操作,语句如下:

mysql> replace into dianwoba(d,w) values(6,5);

Query OK, 3 rows affected (0.07 sec)

请问一下,再操作完之后,会是什么结果?

mysql> select * from dianwoba;

我们来仔细分析一下 replace into dianwoba(d,w) values(6,5); 的执行过程:

第一步: (6,6,null)->(6,5,null)

第二步: (4,5,null)-> 删除

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

同样场景我们再来分析一下insert into .on duplicate key update 语句的影响。

mysql> insert into dianwoba(d,w) values(6,4) on duplicate key update w=values(w);

ERROR 1062 (23000): Duplicate entry '4' for key 'w'

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

mysql> insert into dianwoba(d,w,b) values(6,4,'4') on duplicate key update b=values(b);

Query OK, 2 rows affected (0.05 sec)

(6,5,null)->(6,5,4)

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

我来评几句
登录后评论

已发表评论数()

相关站点

+订阅
热门文章