DUPLICATE、REPLACE INTO、REPLACE三种方式如何更新数据？

ON DUPLICATE KEY UPDATE

mysql语法支持如果数据存在则更新，不存在则插入，首先判断数据存在还是不存在的那个字段要设置成unique索引。¹

语法：

INSERT INTO 表名(唯一索引列, 列2, 列3) VALUE(值1, 值2, 值3) ON DUPLICATE KEY UPDATE 列=值, 列=值

例如表tb_addrbook如下。

mysql> show create table tb_addrbook;
+-------------+-------------------------------------------+
| Table       | Create Table                              |
+-------------+-------------------------------------------+
| tb_addrbook | CREATE TABLE `tb_addrbook` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) NOT NULL DEFAULT '0',
  `name` varchar(16) NOT NULL DEFAULT '',
  `company` varchar(48) NOT NULL DEFAULT '',
  `job` varchar(32) NOT NULL DEFAULT '',
  `tel` varchar(16) NOT NULL DEFAULT '',
  `mobile` varchar(11) NOT NULL DEFAULT '',
  `mail` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `num` (`num`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4  |
+-------------+-------------------------------------------+
1 row in set (0.00 sec)

注意：表结构中唯一索引UNIQUE KEY `num` (`num`) USING BTREE

mysql> select * from tb_addrbook;
+----+-----+--------+--------------+--------+--------------+-------------+-------------+
| id | num | name   | company      | job    | tel          | mobile      | mail        |
+----+-----+--------+--------------+--------+--------------+-------------+-------------+
|  1 |   0 | 小张   | 小张科技     | 支援   | 010-12345678 | 13600000000 | 123@123.com |
+----+-----+--------+--------------+--------+--------------+-------------+-------------+
1 row in set (0.00 sec)

语句1:不存在则插入

INSERT INTO tb_addrbook(num,name,mobile) VALUE('1001','小李','13112345678') ON DUPLICATE KEY UPDATE name= '小李',mobile='13112345678'

mysql> select * from tb_addrbook;                                                                                        
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num  | name   | company      | job    | tel          | mobile      | mail        |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
|  1 |    0 | 小张   | 小张科技     | 支援   | 010-12345678 | 13600000000 | 123@123.com |
|  2 | 1001 | 小李   |              |        |              | 13112345678 |             |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
2 rows in set (0.00 sec)

语句2：存在则更新

INSERT INTO tb_addrbook(num,name,mobile) VALUE('1001','小李','18800000000') ON DUPLICATE KEY UPDATE name= '小李',mobile='18800000000'

mysql> select * from tb_addrbook;                                                                                        
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num  | name   | company      | job    | tel          | mobile      | mail        |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
|  1 |    0 | 小张   | 小张科技     | 支援   | 010-12345678 | 13600000000 | 123@123.com |
|  2 | 1001 | 小李   |              |        |              | 18800000000 |             |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
2 rows in set (0.00 sec)

语句3：存在则更新。insert部分 与 update部分 不同时，update中的部分生效。
INSERT INTO tb_addrbook(num,name,mobile) VALUE('1001','小李','18800000000') ON DUPLICATE KEY UPDATE name= '小李',mobile='1880'

mysql> select * from tb_addrbook;                                                                                        
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num  | name   | company      | job    | tel          | mobile      | mail        |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
|  1 |    0 | 小张   | 小张科技     | 支援   | 010-12345678 | 13600000000 | 123@123.com |
|  2 | 1001 | 小李   |              |        |              | 1880        |             |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
2 rows in set (0.00 sec)

语句4：INSERT部分，未指明唯一索引列；则更新第一行记录
INSERT INTO tb_addrbook(id, name, mobile) VALUES(3, '小王', '33333333333') ON DUPLICATE KEY UPDATE name='小王',mobile='12ile='1234'

mysql> SELECT * FROM tb_addrbook;                                                                                        
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num  | name   | company      | job    | tel          | mobile      | mail        |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
|  1 |    0 | 小王   | 小张科技     | 支援   | 010-12345678 | 1234        | 123@123.com |
|  2 | 1001 | 小李   |              |        |              | 13112345678 |             |
+----+------+--------+--------------+--------+--------------+-------------+-------------+

特点

• DUPLICATE不会删除原有的记录。即：不会破坏索引。

REPLACE INTO

REPLACE INTO 主要作用类似 INSERT 插入操作。主要的区别是 REPLACE INTO 会根据主键或者唯一索引检查数据是否存在，如果存在就先删除再更新。²

语法：

REPLACE INTO 表名称(列1, 列2, 列3) VALUES(值1, 值2, 值3)

语句1：不存在则插入

mysql> REPLACE INTO tb_addrbook(num, name, mobile) VALUES(3000, '小山', '14412341234');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb_addrbook;
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num  | name   | company      | job    | tel          | mobile      | mail        |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
|  1 |    0 | 小王   | 小张科技     | 支援   | 010-12345678 | 1234        | 123@123.com |
|  2 | 1001 | 小李   |              |        |              | 13112345678 |             |
|  3 | 3000 | 小山   |              |        |              | 14412341234 |             |
+----+------+--------+--------------+--------+--------------+-------------+-------------+

注意：此时的主键id=3；

语句2：存在则先删除后插入

mysql> REPLACE INTO tb_addrbook(num, name, mobile) VALUES(3000, '小山', '14412341234');
Query OK, 2 rows affected (0.00 sec)

注意：上述语句导致2行数据受影响。是什么原因呢？

mysql> SELECT * FROM tb_addrbook;
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num  | name   | company      | job    | tel          | mobile      | mail        |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
|  1 |    0 | 小王   | 小张科技     | 支援   | 010-12345678 | 1234        | 123@123.com |
|  2 | 1001 | 小李   |              |        |              | 13112345678 |             |
|  4 | 3000 | 小山   |              |        |              | 14412341234 |             |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
3 rows in set (0.00 sec)

注意：此时的主键id=3的一条记录被删除，重新插入了一条主键id=4的新记录。

特点

• REPLACE INTO底层是先删除后插入数据，会破坏索引、重新维护索引
• 必须要有主键或唯一索引才能有效，否则replace into就只新增了

REPLACE

replace是mysql 里面处理字符串比较常用的函数，可以替换字符串中的内容。类似的处理字符串的还有trim截取操作。³

语法

replace(object,search,replace)

语句1：查询结果替换

mysql> SELECT REPLACE('www.baidu.com', 'w', 'n');
+------------------------------------+
| REPLACE('www.baidu.com', 'w', 'n') |
+------------------------------------+
| nnn.baidu.com                      |
+------------------------------------+
1 row in set (0.00 sec)

语句2：更新数据

mysql> UPDATE tb_addrbook SET name=REPLACE(name, '小', '大');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM tb_addrbook;
+----+------+--------+--------------+--------+--------------+-------------+-------------+
| id | num  | name   | company      | job    | tel          | mobile      | mail        |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
|  1 |    0 | 大王   | 小张科技     | 支援   | 010-12345678 | 1234        | 123@123.com |
|  2 | 1001 | 大李   |              |        |              | 13112345678 |             |
|  4 | 3000 | 大山   |              |        |              | 14412341234 |             |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
3 rows in set (0.00 sec)

参考

---

1. IT技术院. Mysql:如果数据存在则更新，不存在则插入(面试必考) [EB/OL]. https://www.toutiao.com/a6711469973560115715/?tt_from=weixin&utm_campaign=client_share&wxshare_count=1&timestamp=1563073321&app=news_article&utm_source=weixin&utm_medium=toutiao_android&req_id=201907141102000100280171399768E82&group_id=6711469973560115715 ↩︎

2. 程序汪汪. Mysql中超级简洁语法replace into存在就更新，偷懒就这么简单 [EB/OL]. https://www.toutiao.com/a6643545991666467342/ ↩︎

3. 波波说运维. 详解Mysql数据库中replace与replace into的用法及区别 [EB/OL]. https://www.toutiao.com/a6702645391197733387/ ↩︎