文章目录
- ON DUPLICATE KEY UPDATE
- 语法：
- 特点
- REPLACE INTO
- 语法：
- 语句1：不存在则插入
- 语句2：存在则先删除后插入
- 特点
- REPLACE
- 语法
- 参考
DUPLICATE、REPLACE INTO、REPLACE三种方式如何更新数据？
ON DUPLICATE KEY UPDATE
mysql语法支持如果数据存在则更新，不存在则插入，首先判断数据存在还是不存在的那个字段要设置成unique索引。1
语法：
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@ |
+----+-----+--------+--------------+--------+--------------+-------------+-------------+
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@ |
| 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@ |
| 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@ |
| 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@ |
| 2 | 1001 | 小李 | | | | 13112345678 | |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
特点
- DUPLICATE不会删除原有的记录。即：不会破坏索引。
REPLACE INTO
REPLACE INTO 主要作用类似 INSERT 插入操作。主要的区别是 REPLACE INTO 会根据主键或者唯一索引检查数据是否存在，如果存在就先删除再更新。2
语法：
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@ |
| 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@ |
| 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截取操作。3
语法
replace(object,search,replace)
语句1：查询结果替换
mysql> SELECT REPLACE('', 'w', 'n');
+------------------------------------+
| REPLACE('', 'w', 'n') |
+------------------------------------+
| |
+------------------------------------+
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@ |
| 2 | 1001 | 大李 | | | | 13112345678 | |
| 4 | 3000 | 大山 | | | | 14412341234 | |
+----+------+--------+--------------+--------+--------------+-------------+-------------+
3 rows in set (0.00 sec)
参考
-
IT技术院. Mysql:如果数据存在则更新，不存在则插入(面试必考) [EB/OL]. /a6711469973560115715/?tt_from=weixin&utm_campaign=client_share&wxshare_count=1×tamp=1563073321&app=news_article&utm_source=weixin&utm_medium=toutiao_android&req_id=201907141102000100280171399768E82&group_id=6711469973560115715 ↩︎
-
程序汪汪. Mysql中超级简洁语法replace into存在就更新，偷懒就这么简单 [EB/OL]. /a6643545991666467342/ ↩︎
-
波波说运维. 详解Mysql数据库中replace与replace into的用法及区别 [EB/OL]. /a6702645391197733387/ ↩︎