mysql中 replace into 和 on duplica

replace into 和 ON DUPLICATE KEY UPDATE的区别

主要分析replace into 和ON DUPLICATE KEY UPDATE在具备唯一索引(primary key ,unique key)下的区别

如下表的id为主键

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  2 | hhhh   |
|  3 | cheung |
|  5 | kkll   |
|  6 | f      |
| 10 | test   |
+----+--------+
5 rows in set (0.00 sec)

replace into test(id)  values(2);

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  2 | NULL   |
|  3 | cheung |
|  5 | kkll   |
|  6 | f      |
| 10 | test   |
+----+--------+
5 rows in set (0.00 sec)

可以看到replaceinto 在没有设置值的列设置为null

mysql> insert into test(id) values(3) ON DUPLICATE KEY UPDATE id=4;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  2 | NULL   |
|  4 | cheung |
|  5 | kkll   |
|  6 | f      |
| 10 | test   |
+----+--------+
5 rows in set (0.00 sec)

on duplicate update 只会更新重复的那一列，没有重复的那一列，没有更新为null,可以看到on duplicate update 对于更新的控制粒度更细