欢迎来到淼淼之森的博客小站。  交流请加我微信好友: studyjava。  也欢迎关注同名公众号：Java学习之道

mysql避免重复插入记录方法(insert ignore 、insert...on duplicate key update、replace into) 置顶！

  |   0 评论   |   0 浏览

一、序

回顾以前写的项目，发现在规范的前提下，还是可以做点骚操作的。

假如项目使用的MySQL，首先创建测试数据表（建表语句中默认使用utf8mb4以及utf8mb4_unicode_ci，感兴趣的读者可以自行搜索这两个配置）：

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `no` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '编号',
  `name` varchar(30) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

utf8mb4是utf8的超集并完全兼容utf8，能够用四个字节存储更多的字符。例如emoji和一些不常用的汉字，如“墅”，这些需要四个字节才能编码的就不支持。

utf8mb4对应的排序字符集有utf8mb4_unicode_ci、utf8mb4_general_ci

1.1、两种字符集对比

utf8mb4_unicode_ci和utf8mb4_general_ci的对比

准确性：

  • utf8mb4_unicode_ci是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序
  • utf8mb4_general_ci没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。
  • 但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。

性能：

  • utf8mb4_general_ci在比较和排序的时候更快
  • utf8mb4_unicode_ci在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。
  • 但是在绝大多数情况下发，不会发生此类复杂比较。相比选择哪一种collation，使用者更应该关心字符集与排序规则在db里需要统一。

二、插入冲突时更新数据 ON DUPLICATE KEY UPDATE

SQL执行插入时，可能因为种种原因插入失败，比如UNIQUE索引冲突导致插入失败。比如某个不晓得DBA插入了一条错误的学生记录("3", "小明")，悲剧的是小明的编号是1。常规做法就是判断当前的数据库记录中是否存在小明的记录，如果有则更新其对应其编号，否则就插入小明的记录。当然存在更好的做法：

INSERT INTO student(no, name) VALUES (3, "xiaoming");
INSERT INTO student(no, name) VALUES (1, "xiaoming"), (2,"xiaohong")
ON DUPLICATE KEY UPDATE no=VALUES(no);

那就是使用ON DUPLICATE KEY UPDATE，这是mysql独特的语法（语句后面可以放置多个更新条件，每个条件使用逗号隔开即可）。需要注意，这里的VALUES(no)是将冲突的no数值更新为用户插入数据中的no，这样每条冲突的数据就可以动态的设置新的数值。

// 标准用法,在插入末尾添加 on duplicate key update
insert into tbl_name(...) values(...) on duplicate key update no_desc=values(no_desc);

on duplicate key update 含义：

  • 1）如果在INSERT语句末尾指定了 on duplicate key update，如果插入会导致UNIQUE索引或PRIMARY KEY出现重复值，则在出现重复值的行执行UPDATE；
  • 2）如果不会导致唯一值列重复的问题，则插入新行。
    values(col_name)函数只是取当前插入语句中的插入值，并没有累加功能。

如：no_count = values(no_count) 取前面 insert into 中的 no_count 值，并更新当有多条记录冲突，需要插入时，前面的更新值都被最后一条记录覆盖，所以呈现出取最后一条更新的现象。

如：no_count = no_count + values(no_count) 依然取前面 insert into 中的 no_count 值，并与原记录值相加后更新回数据库，这样，当多条记录冲突需要插入时， 就实现了不断累加更新的现象。

有一些场景，如日志文件解析入库，消息队列接收数据入库等情况下可能解析到或者接收到待插入的重复数据xxx，存在重复数据则更新，不存在则插入。

三、忽略插入失败中的错误 INSERT INTO

批量插入比单条数据挨个插入，普遍会提高性能以及减少总的网络开销。但是，假如批量插入的数据中心存在一个臭虫，在默认的情况下，这就会导致批量插入失败（没有一条数据插入成功）。当然，我们可以选择忽略。

// 标准用法
insert ignore into tbl_name(...) values(...)

INSERT INTO student(`no`, `name`) VALUES (1, "xiaoming");
INSERT IGNORE INTO student(`no`, `name`) VALUES (1, "xiaoming"),(2,"xiaohong"),(3, "xiaowang");

只需要在批量插入的语句中，插入IGNORE，那么某几条数据的插入失败就会被忽略掉，正确的数据依然可以插入库中。

PS: 但是，我建议这个功能谨慎使用，使用mysql数据库本身就是看中数据的正确性，没必要为了批量插入的性能而自动放弃数据的正确性。

INSERT IGNORE 还有些副作用

insert ignore的时候会对插入的每一行数据取S锁做unique id的检测，同时会对主键的自增id字段加写意向锁(insert intension)，在unique key较为复杂的时候，检测unique key的时候会一直占用主键的插入意向锁，其他insert ignore也想给主键id加插入意向锁，导致死锁。以上情况是在mysql 8.x中发现的，以前用低版本的mysql似乎没遇到过相关问题，所以不清楚低版本mysql的insert ignore是否有坑，但是8.x的版本最好insert ignore不要插入多行数据，尤其是unique key比较复杂的时候（有三四个字段共同组成）。

INSERT IGNORE其他些副作用，感兴趣的可以自行查询了解。

四、插入替换数据 REPLACE INTO

如果存在primary or unique相同的记录，则先删除掉。再插入新记录。

这种方法就是不管原来有没有相同的记录，都会先删除掉然后再插入。

注：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

// 第1种形式
replace into tbl_name(col_name, ...) values(...)
// 第2种形式
replace into tbl_name(col_name, ...) select ...
// 第3种形式
replace into tbl_name set col_name=value, ...
  • 第一种形式类似于insert into的用法，
  • 第二种replace select的用法也类似于insert select，这种用法并不一定要求列名匹配，事实上，MYSQL甚至不关心select返回的列名，它需要的是列的位置。

例如，replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2;这个例子使用replace into从tb2中将所有数据导入tb1中。

  • 第三种replace set用法类似于update set用法，使用一个例如SET col_name = col_name + 1的赋值，则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此，该赋值相当于SET col_name = DEFAULT(col_name) + 1。

PS: 前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值。

五、小结

replace 与 insert...on duplicate key update 不同之处：

  • replace是删除记录，然后再重新insert
  • 而insert...on duplicate key update是直接在该条记录上修改

所以二者的差别主要有以下两处：

  • 1、当表中存在自增值的时候，如果表中存在某条记录，replace语法会导致自增值+1，而insert...on duplicate key update语法不会；
  • 2、当表中的某些字段中包含默认值的时候，replace操作插入不完全字段的记录，会导致其他字段直接使用默认值，而insert...on duplicate key update操作会保留该条记录的原有值。

六、参考文章

标题：mysql避免重复插入记录方法(insert ignore 、insert...on duplicate key update、replace into)
作者：mmzsblog
地址：https://www.mmzsblog.cn/articles/2023/06/13/1686660443112.html

如未加特殊说明，文章均为原创，转载必须注明出处。均采用CC BY-SA 4.0 协议！

本网站发布的内容（图片、视频和文字）以原创、转载和分享网络内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。若本站转载文章遗漏了原文链接，请及时告知，我们将做删除处理！文章观点不代表本网站立场，如需处理请联系首页客服。
• 网站转载须在文章起始位置标注作者及原文连接，否则保留追究法律责任的权利。
• 公众号转载请联系网站首页的微信号申请白名单！
个人微信公众号 ↓↓↓                 

微信搜一搜 Java 学习之道