Mysql on duplicate key update用法及优缺点

根号X

已于 2023-11-01 14:57:57 修改

阅读量354

点赞数

分类专栏： MySQL 零碎知识点文章标签： mysql android 数据库

于 2023-11-01 14:43:35 首次发布

本文链接：https://blog.csdn.net/qq_43786573/article/details/134160831

版权

零碎知识点同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

MySQL

2 篇文章 0 订阅

订阅专栏

本文介绍了MySQL中on_duplicate_key_update的用法，包括插入数据并更新的场景，强调了当id为自动递增时可能带来的问题，如自增ID不连续和潜在的死锁风险。作者提醒在使用时需谨慎处理。

摘要由CSDN通过智能技术生成

Mysql on duplicate key update用法及优缺点

先建立张测试表

CREATE TABLE `insert_update_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `stu_id` int(11) DEFAULT NULL COMMENT '学生ID',
  `stu_name` varchar(50) DEFAULT NULL COMMENT '学生名字',
  `yn` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标识：1 已删除，0 未删除',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_idx_stu_id` (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='插入更新测试表'

1、第一次插入数据

INSERT INTO insert_update_test  (stu_id, stu_name, yn) VALUES (1001, '张三',0)  
  ON DUPLICATE KEY UPDATE stu_name = "插入更新";

在这里插入图片描述
2、再次执行SQL
执行结果

表数据结果

问题注意：

一、id自增过快

如果数据表id是自动递增的不建议使用该语句；id不连续，如果前面更新的比较多，新增的下一条会相应跳跃的更大

实测：

INSERT INTO insert_update_test  (stu_id, stu_name, yn) VALUES (1021, '王五',0)  
  ON DUPLICATE KEY UPDATE stu_name = "插入更新";

执行了几次张三的插入更新后插入理应为第二条数据的王五，结果发现王五的自增id是5，而不是2，这样就造成id自增过快，使用一定要慎重
在这里插入图片描述

死锁

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S（共享锁）锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上X（排他锁），最后进行update写入。

实例：
在这里插入图片描述