MySQL中实现“不存在则插入”的方法
ztj100 2025-05-05 20:15 27 浏览 0 评论
技术背景
在MySQL数据库操作中,当需要向已有大量数据(如拥有约1400万条记录的表)的表中添加新数据时,为避免数据重复,需要实现“不存在则插入”的功能。传统的使用一对查询(一个查询检查,一个查询插入)的方式效率较低,因此需要探索更高效的方法。
实现步骤
1. 使用INSERT IGNORE INTO
这种方法会忽略插入过程中出现的错误,若插入的记录已存在(违反唯一约束),则会被默默跳过。 示例代码如下:
INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;
该方法的优点是简单直接,缺点是如果出现其他问题(如非唯一约束相关的错误),查询也不会中止,可能会掩盖其他问题。因此,建议先在不使用IGNORE关键字的情况下进行测试。
2. 使用REPLACE INTO
若记录已存在,它会先删除原记录,再插入新记录;若记录不存在,则直接插入。 示例代码如下:
REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;
不过,此方法效率不高,因为它涉及删除和插入操作,且可能会触发相关的删除触发器,导致其他约束对象被删除。
3. 使用INSERT … ON DUPLICATE KEY UPDATE
当插入的记录违反唯一约束时,执行UPDATE操作;若不违反,则正常插入。 示例代码如下:
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
该方法的优势在于只忽略重复键事件,遇到其他错误时仍会中止查询。
4. 使用SELECT结合WHERE NOT EXISTS
通过子查询判断要插入的数据是否已存在,若不存在则插入。 示例代码如下:
INSERT INTO `table` (`value1`, `value2`)
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM `table`
WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1)
这种方法适用于表中没有唯一键的情况。
核心代码
以下是使用INSERT … ON DUPLICATE KEY UPDATE实现“不存在则插入,存在则更新”的示例代码:
INSERT INTO products (product_name, price) VALUES ('iPhone 15', 999)
ON DUPLICATE KEY UPDATE price = 999;
若products表中存在product_name为iPhone 15的记录,则更新其price为999;若不存在,则插入该记录。
最佳实践
- 若表中有唯一键,优先考虑使用INSERT … ON DUPLICATE KEY UPDATE,它能在处理重复记录时避免不必要的删除和插入操作,同时能正确处理其他错误。
- 若仅需忽略重复记录,不关心其他错误,可使用INSERT IGNORE INTO。
- 若表中无唯一键,可使用SELECT结合WHERE NOT EXISTS的方法。
常见问题
1. INSERT IGNORE INTO会导致主键出现间隙吗?
会。INSERT IGNORE INTO无论插入是否成功,都会像普通INSERT一样增加主键值,可能导致主键出现间隙。若应用程序依赖完美递增的主键,这可能会带来问题。可考虑设置innodb_autoinc_lock_mode = 0(会有轻微性能损失),或先使用SELECT检查再插入(会有性能损失和额外代码)。
2. REPLACE INTO有什么副作用?
REPLACE INTO会先删除原记录再插入新记录,可能会触发删除触发器,导致其他约束对象被删除。并且,根据MySQL手册,只有当表有主键或唯一索引时,REPLACE才有意义,否则它等同于INSERT。
3. INSERT … ON DUPLICATE KEY UPDATE会影响自增列吗?
当插入失败(因重复键)而执行UPDATE操作时,INSERT … ON DUPLICATE KEY UPDATE会使自增列递增,因为它并非真正的插入失败,而是进行了更新操作。
相关推荐
- 30天学会Python编程:16. Python常用标准库使用教程
-
16.1collections模块16.1.1高级数据结构16.1.2示例...
- 强烈推荐!Python 这个宝藏库 re 正则匹配
-
Python的re模块(RegularExpression正则表达式)提供各种正则表达式的匹配操作。...
- Python爬虫中正则表达式的用法,只讲如何应用,不讲原理
-
Python爬虫:正则的用法(非原理)。大家好,这节课给大家讲正则的实际用法,不讲原理,通俗易懂的讲如何用正则抓取内容。·导入re库,这里是需要从html这段字符串中提取出中间的那几个文字。实例一个对...
- Python数据分析实战-正则提取文本的URL网址和邮箱(源码和效果)
-
实现功能:Python数据分析实战-利用正则表达式提取文本中的URL网址和邮箱...
- python爬虫教程之爬取当当网 Top 500 本五星好评书籍
-
我们使用requests和re来写一个爬虫作为一个爱看书的你(说的跟真的似的)怎么能发现好书呢?所以我们爬取当当网的前500本好五星评书籍怎么样?ok接下来就是学习python的正确姿...
- 深入理解re模块:Python中的正则表达式神器解析
-
在Python中,"re"是一个强大的模块,用于处理正则表达式(regularexpressions)。正则表达式是一种强大的文本模式匹配工具,用于在字符串中查找、替换或提取特定模式...
- 如何使用正则表达式和 Python 匹配不以模式开头的字符串
-
需要在Python中使用正则表达式来匹配不以给定模式开头的字符串吗?如果是这样,你可以使用下面的语法来查找所有的字符串,除了那些不以https开始的字符串。r"^(?!https).*&...
- 先Mark后用!8分钟读懂 Python 性能优化
-
从本文总结了Python开发时,遇到的性能优化问题的定位和解决。概述:性能优化的原则——优化需要优化的部分。性能优化的一般步骤:首先,让你的程序跑起来结果一切正常。然后,运行这个结果正常的代码,看看它...
- Python“三步”即可爬取,毋庸置疑
-
声明:本实例仅供学习,切忌遵守robots协议,请不要使用多线程等方式频繁访问网站。#第一步导入模块importreimportrequests#第二步获取你想爬取的网页地址,发送请求,获取网页内...
- 简单学Python——re库(正则表达式)2(split、findall、和sub)
-
1、split():分割字符串,返回列表语法:re.split('分隔符','目标字符串')例如:importrere.split(',','...
- Lavazza拉瓦萨再度牵手上海大师赛
-
阅读此文前,麻烦您点击一下“关注”,方便您进行讨论和分享。Lavazza拉瓦萨再度牵手上海大师赛标题:2024上海大师赛:网球与咖啡的浪漫邂逅在2024年的上海劳力士大师赛上,拉瓦萨咖啡再次成为官...
- ArkUI-X构建Android平台AAR及使用
-
本教程主要讲述如何利用ArkUI-XSDK完成AndroidAAR开发,实现基于ArkTS的声明式开发范式在android平台显示。包括:1.跨平台Library工程开发介绍...
- Deepseek写歌详细教程(怎样用deepseek写歌功能)
-
以下为结合DeepSeek及相关工具实现AI写歌的详细教程,涵盖作词、作曲、演唱全流程:一、核心流程三步法1.AI生成歌词-打开DeepSeek(网页/APP/API),使用结构化提示词生成歌词:...
- “AI说唱解说影视”走红,“零基础入行”靠谱吗?本报记者实测
-
“手里翻找冻鱼,精心的布局;老漠却不言语,脸上带笑意……”《狂飙》剧情被写成歌词,再配上“科目三”背景音乐的演唱,这段1分钟30秒的视频受到了无数网友的点赞。最近一段时间随着AI技术的发展,说唱解说影...
- AI音乐制作神器揭秘!3款工具让你秒变高手
-
在音乐创作的领域里,每个人都有一颗想要成为大师的心。但是面对复杂的乐理知识和繁复的制作过程,许多人的热情被一点点消磨。...
你 发表评论:
欢迎- 一周热门
- 最近发表
-
- 30天学会Python编程:16. Python常用标准库使用教程
- 强烈推荐!Python 这个宝藏库 re 正则匹配
- Python爬虫中正则表达式的用法,只讲如何应用,不讲原理
- Python数据分析实战-正则提取文本的URL网址和邮箱(源码和效果)
- python爬虫教程之爬取当当网 Top 500 本五星好评书籍
- 深入理解re模块:Python中的正则表达式神器解析
- 如何使用正则表达式和 Python 匹配不以模式开头的字符串
- 先Mark后用!8分钟读懂 Python 性能优化
- Python“三步”即可爬取,毋庸置疑
- 简单学Python——re库(正则表达式)2(split、findall、和sub)
- 标签列表
-
- idea eval reset (50)
- vue dispatch (70)
- update canceled (42)
- order by asc (53)
- spring gateway (67)
- 简单代码编程 贪吃蛇 (40)
- transforms.resize (33)
- redisson trylock (35)
- 卸载node (35)
- np.reshape (33)
- torch.arange (34)
- npm 源 (35)
- vue3 deep (35)
- win10 ssh (35)
- vue foreach (34)
- idea设置编码为utf8 (35)
- vue 数组添加元素 (34)
- std find (34)
- tablefield注解用途 (35)
- python str转json (34)
- java websocket客户端 (34)
- tensor.view (34)
- java jackson (34)
- vmware17pro最新密钥 (34)
- mysql单表最大数据量 (35)