MySQL batch insert, how not to insert duplicate data?

PHP open source community 2021-04-08 11:29:32
mysql batch insert insert duplicate

Consider the past you shall know the future

Knowledge is a thing , It seems that we really need to review the old and learn the new , Never , Forget about it .

The business is simple : Need to batch insert some data , The data source may be tables from other databases , It could also be an external excel Import of .

So here comes the question , Is it necessary to check before inserting each time , See if it's repeated , Filter the data in the code , The repeated ones will be filtered out ?

When inserting values into big data databases , Also judge whether the insertion is repeated , Then insert . How to improve efficiency ?

It seems that I am not alone in this problem .

There are many solutions , Different scenarios have different solutions , When the amount of data is small , Whatever you do , But when there's a lot of data , This is not a simple problem

Millions of data , It's impossible to find out, come and go, reprocess !

Talk about me Google The solution to the problem .

1、insert ignore into

When inserting data , If there is an error , Like duplicate data , No errors will be returned , Only return... In the form of a warning . So use ignore Make sure there is no problem with the statement itself , Otherwise, it will be ignored . for example :
INSERT IGNORE INTO user (nameVALUES ('telami')
This method is very simple , But there's a possibility , The insertion is not due to duplicate data , But for other reasons , Also ignored ~

2、on duplicate key update

When primary perhaps unique When repeated , execute update sentence , Such as update After that is the useless statement , Such as id=id, Same with 1 Function the same , But mistakes don't go unnoticed .

for example , In order to achieve name Repeated data insertion does not report error , You can use the following statement :

INSERT INTO user (nameVALUES ('telami'ON duplicate KEY UPDATE id = id

There is a prerequisite for this approach , Namely , Constraints that need to be inserted , It needs to be a primary key or a unique constraint ( In your business, if you want to be the only judge, set that field as the only constraint, that is unique key).

3、insert … select … where not exist

according to select Whether to insert , It's not just through primary and unique To judge , Other conditions can also be adopted . for example :

INSERT INTO user (nameSELECT 'telami' FROM dual WHERE NOT EXISTS (SELECT id FROM user WHERE id = 1)

This method actually uses mysql The way to create a temporary table , But it uses subqueries , Efficiency also has a little impact , If you can use the above, don't use this .

4、replace into

If there is primary or unique The same record , Delete first . Insert a new record .

REPLACE INTO user SELECT 1'telami' FROM books

This method is whether there are the same records or not , Will be deleted first and then inserted .


The choice is the second way

<insert id="batchSaveUser" parameterType="list">
    insert into user (id,username,mobile_number)
    <foreach collection="list" item="item" index="index" separator=",">
    ON duplicate KEY UPDATE id = id

It's used here Mybatis, An operation of batch insertion ,mobile_number A unique constraint has been imposed . In this way, during batch insertion , If the mobile phone number is the same , It won't be inserted any more .


本文为[PHP open source community]所创,转载请带上原文链接,感谢

  1. 【TTS】传输表空间AIX asm -&gt; linux asm
  2. 【TTS】传输表空间Linux asm -&gt; AIX asm
  3. 【DB宝40】MySQL高可用管理工具Orchestrator简介及测试
  4. 【TTS】传输表空间Linux -&gt;AIX 基于rman
  5. 一本关于HTTP的恋爱日记
  6. 【RocketMQ源码分析】深入消息存储(3)
  7. SpringCloud+Nacos实现服务配置中心(Hoxton版本)
  8. SICP:构造过程抽象--面向对象的解释
  9. 3w 字长文爆肝 Java 基础面试题!太顶了!!!
  10. Spring Cloud 升级之路 - 2020.0.x - 3. Undertow 的 accesslog 配置
  11. win10卸载mysql5.7
  12. MySQL 批量插入,如何不插入重复数据?
  13. k8s cronjob应用示例
  14. 非常规方法,轻松应对Oracle数据库危急异常
  15. Oracle hang 之sqlplus -prelim使用方法
  16. 如何全文搜索oracle官方文档
  17. Java student achievement management system course design, with source code!
  18. win10安装mysql8.0
  19. 手把手教你写一个spring IOC容器
  20. JAVA 中的异常(1)- 基本概念
  21. A love diary about http
  22. navicat连接win10 mysql8.0 报错2059
  23. [rocketmq source code analysis] in depth message storage (3)
  24. Implementation of service configuration center with spring cloud + Nacos (Hoxton version)
  25. SCIP: constructing data abstraction -- Explanation of queue and tree in data structure
  26. SCIP: abstraction of construction process -- object oriented explanation
  27. Using docker to build elasticsearch + kibana cluster
  28. What are the spring IOC features? I can't understand the source code!
  29. Spring cloud upgrade road - 2020.0. X - 3. Accesslog configuration of undertow
  30. 导致Oracle性能抖动的参数提醒
  31. 风险提醒之Oracle RAC高可用失效
  32. 小机上运行Oracle需要注意的进程调度bug
  33. Oracle内存过度消耗风险提醒
  34. Oracle SQL monitor
  35. 使用Bifrost实现Mysql的数据同步
  36. 揭秘Oracle数据库truncate原理
  37. 看了此文,Oracle SQL优化文章不必再看!
  38. Mybatis (3) map and fuzzy query expansion
  39. Kafka性能篇:为何这么“快”?
  40. 两个高频设计类面试题:如何设计HashMap和线程池
  41. [TTS] AIX - & gt; Linux -- Based on RMAN (real environment)
  42. 为什么学编程大部分人选Java编程语言?
  43. Redis 高可用篇:你管这叫 Sentinel 哨兵集群原理
  44. redis 为什么把简单的字符串设计成 SDS?
  45. [TTS] transfer table space AIX - & gt; Linux based on RMAN
  46. Linux 网卡数据收发过程分析
  47. Redis 高可用篇:你管这叫 Sentinel 哨兵集群原
  48. Redis 6.X Cluster 集群搭建
  49. [TTS] transfer table space AIX ASM - & gt; Linux ASM
  50. [TTS] transfer table space Linux ASM - & gt; AIX ASM
  51. 高性能通讯框架——Netty
  52. Brief introduction and test of orchestrator, a high availability management tool for MySQL
  53. [TTS] transfer table space Linux - & gt; AIX based on RMAN
  54. A love diary about http
  55. [rocketmq source code analysis] in depth message storage (3)
  56. Implementation of service configuration center with spring cloud + Nacos (Hoxton version)
  57. SiCp: abstraction of construction process -- object oriented explanation
  58. springboot网上点餐系统
  59. 【SPM】oracle如何固定执行计划
  60. 用好HugePage,告别Linux性能故障