环境准备
mysql版本:
5.7.29
测试表创建及初始化:
--建表
CREATE TABLE `test_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`uid` bigint(20) unsigned NOT NULL COMMENT '用户id',
`name` varchar(128) NOT NULL COMMENT '用户名',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='人员表';
--初始化数据
insert into test_user(id, uid, name) values (1,10001,'lzc'),(2,10002,'lxx'),(3,10003,'abc');
--查询初始化后的数据
mysql> select * from test_user;
+----+-------+------+
| id | uid | name |
+----+-------+------+
| 1 | 10001 | lzc |
| 2 | 10002 | lxx |
| 3 | 10003 | abc |
+----+-------+------+
3 rows in set (0.00 sec)
方案一:INSERT ··· ON DUPLICATE KEY UPDATE
在MySQL数据库中,如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的惟一索引或主键中产生重复值,那么就会发生旧行的更新;如果插入的行数据与现有表中记录的唯一索引或者主键不重复,则执行新记录插入操作。另外,ON DUPLICATE KEY UPDATE不能写where条件。
唯一索引不重复,执行插入
insert into test_user(id, uid, name) values (4,10004,‘lzc’) ON DUPLICATE KEY UPDATE name = concat(name , name);
mysql> insert into test_user(id, uid, name) values (4,10004,'lzc') ON DUPLICATE KEY UPDATE name = concat(name , name);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_user;
+----+-------+------+
| id | uid | name |
+----+-------+------+
| 1 | 10001 | lzc |
| 2 | 10002 | lxx |
| 3 | 10003 | abc |
| 4 | 10004 | lzc |
+----+-------+------+
4 rows in set (0.00 sec)
唯一索引重复,执行更新,但更新值与原值不相同
insert into test_user(id, uid, name) values (5,10001,‘lzc’) ON DUPLICATE KEY UPDATE name = concat(name , name);
mysql> insert into test_user(id, uid, name) values (5,10001,'lzc') ON DUPLICATE KEY UPDATE name = concat(name , name);
Query OK, 2 rows affected (0.01 sec)
mysql> select * from test_user;
+----+-------+--------+
| id | uid | name |
+----+-------+--------+
| 1 | 10001 | lzclzc |
| 2 | 10002 | lxx |
| 3 | 10003 | abc |
| 4 | 10004 | lzc |
+----+-------+--------+
4 rows in set (0.00 sec)
唯一索引重复,执行更新,但更新值与原值相同
insert into test_user(id, uid, name) values (2,10002,‘lxx’) ON DUPLICATE KEY UPDATE name = ‘lxx’;
mysql> insert into test_user(id, uid, name) values (2,10002,'lxx') ON DUPLICATE KEY UPDATE name = 'lxx';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_user;
+----+-------+--------+
| id | uid | name |
+----+-------+--------+
| 1 | 10001 | lzclzc |
| 2 | 10002 | lxx |
| 3 | 10003 | abc |
| 4 | 10004 | lzc |
+----+-------+--------+
4 rows in set (0.01 sec)
方案二:REPLACE INTO
这中语法会自动查询主键或索引冲突,如有冲突,他会先删除原有的数据记录,然后执行插入新的数据。
replace into test_user(uid, name) values (10003,‘def’) ;
mysql> replace into test_user(uid, name) values (10003,'def') ;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from test_user;
+----+-------+--------+
| id | uid | name |
+----+-------+--------+
| 1 | 10001 | lzclzc |
| 2 | 10002 | lxx |
| 4 | 10004 | lzc |
| 6 | 10003 | def |
+----+-------+--------+
4 rows in set (0.01 sec)
两种方案对比
INSERT ··· ON DUPLICATE KEY UPDATE冲突时是将原记录更新,REPLACE INTO是删除记录重建,所以性能上前者是有优势的。
尽管如此,当并发量太高时,依旧不推荐使用INSERT ··· ON DUPLICATE KEY UPDATE,会存在死锁问题,死锁现象描述如下:
对于INSERT ON DUPLICATE UPDATE操作,当两个会话S1和S2使用INSERT ON DUPLICATE UPDATE语句操作相同数据且表中存在相同键值记录时,触发死锁场景为:
1. 由于表中已存在重复键值的记录,导致会话先后尝试INSER失败
2. 会话S1进入步骤3尝试获取记录的S锁,该记录未被其他会话加锁,获取S锁成功。
3. 会话S2进入步骤3尝试获取记录的S锁,该记录上被加持S锁,但由于S锁与S锁兼容,获取S锁成功
4. 会话S1进入步骤4尝试获取记录的X锁,由于会话S2对该记录持有S锁,S锁与X锁不兼容,获取X锁失败,会话S1被阻塞
5. 会话S2进入步骤4尝试获取记录的X锁,由于会话S1对该记录持有S锁,S锁与X锁不兼容,获取X锁失败,会话S2被阻塞
6. 会话S2被阻塞后进入死锁检查环节,发现阻塞S1->S2和S2->S1形成死锁环路,触发死锁机制强制回滚S1或S2事务。
参考文章
https://www.cnblogs.com/zjfjava/p/10296867.html
https://blog.csdn.net/jiangying09/article/details/47418439
https://blog.csdn.net/leaves_story/article/details/89373555?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param