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

on duplicate key update:针对主健与唯一健,当插入值中的主健值与表中的主健值,若相同的主健值,就更新on duplicate key update 后面的指定的字段值,若没有相同主健值,就插入该记录

mysql> create table tab_test(fd1 int NOT NULL,fd2  varchar(50),primary key(fd1))engine=innodb;
Query OK, 0 rows affected (0.22 sec) mysql> insert into tab_test(fd1,fd2) values(1,"matt");
Query OK, 1 row affected (0.16 sec) mysql> insert into tab_test(fd1,fd2) values(2,"toto");
Query OK, 1 row affected (0.17 sec) mysql> insert into tab_test values(3,"toto2");
Query OK, 1 row affected (0.17 sec) mysql> insert into tab_test values(3,"toto2") on duplicate key update fd2="test";
Query OK, 2 rows affected (0.17 sec) mysql> select * from tab_test;
+-----+------+
| fd1 | fd2 |
+-----+------+
| 1 | matt |
| 2 | toto |
| 3 | test |
+-----+------+
3 rows in set (0.00 sec) mysql> insert into tab_test values(5,"toto2") on duplicate key update fd2="test1";
Query OK, 1 row affected (0.17 sec) mysql> select * from tab_test;
+-----+-------+
| fd1 | fd2 |
+-----+-------+
| 1 | matt |
| 2 | toto |
| 3 | test |
| 5 | toto2 |
+-----+-------+
4 rows in set (0.00 sec)

replace :

针对主健与唯一健,当插入值中的主健值与表中的主健值,若相同的主健值,删除该记录,再插入值,若没有相同主健值,就插入该记录

mysql> replace tab_test set fd1=1,fd2="Matt";
Query OK, 2 rows affected (0.17 sec) mysql> select * from tab_test;
+-----+-------+
| fd1 | fd2 |
+-----+-------+
| 1 | Matt |
| 2 | toto |
| 3 | test |
| 5 | toto2 |
+-----+-------+
4 rows in set (0.00 sec) mysql> replace tab_test set fd1=1,fd2="xx";
Query OK, 2 rows affected (0.17 sec) mysql> select * from tab_test;
+-----+-------+
| fd1 | fd2 |
+-----+-------+
| 1 | xx |
| 2 | toto |
| 3 | test |
| 5 | toto2 |
+-----+-------+
4 rows in set (0.00 sec)
上一篇:ASP.NET MVC 学习之路由(URL Routing)


下一篇:(转载)[MySQL技巧]INSERT INTO… ON DUPLICATE KEY UPDATE