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 对于更新的控制粒度更细
