慎用replace into

不记得是在哪里看到说replace into的工作流程是根据主键或者唯一索引来判断记录是否存在,不存在就插入,存在则更新.

然后在框架的orm里面针对mysql的驱动实现了一个replace的方法,而然今天使用的时候出现了问题:

mysql> select * from tbl_user;
+----+--------+--------+
| id | name   | status |
+----+--------+--------+
|  1 | eslizn |      0 |
+----+--------+--------+
1 row in set (0.00 sec)

这里是当前表数据,然后我需要更新这条记录的status字段,但是我不确定这条id=1的记录是否存在,于是我使用replace into:

mysql> replace into tbl_user (id, status) values (1, 1);
Query OK, 2 rows affected (0.00 sec)

是不是哪里不对?受影响的行数为何为2?我们再看下数据:

mysql> select * from tbl_user;
+----+------+--------+
| id | name | status |
+----+------+--------+
|  1 | NULL |      1 |
+----+------+--------+
1 row in set (0.00 sec)

可以看到name已经变成null,其原因就是replace into并非是存在则更新,而是存在则删除并插入.

在mysql里不存在就写入,存在则更新的正确写法是这样:

insert into tbl_user (id, status) values (1, 1) on duplicate key update status = 1;

主键冲突时则更新

附上oracle的merge into:

merge into tbl_user using (
    select
        count (*) as nums
    from
        tbl_user
    where
        tbl_user.pid = 'pid'
) p on (p.nums <> 0)
when matched then update set tbl_user.status = 1 where tbl_user.pid = 'pid'
when not matched then insert (tbl_user.pid, tbl_user.status) values ('pid', 1);