java中sum=a+aa+aaa_关于MySQL中insert ignore,insert on duplicate和replace into，你可能没想过区别...

最新推荐文章于 2023-06-20 08:29:17 发布
最新推荐文章于 2023-06-20 08:29:17 发布
阅读量73 收藏
点赞数
文章标签： java中sum=a+aa+aaa
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39607798/article/details/111668711
版权

读完需要

10分钟

速读仅需5分钟

在数据流转中或者日常的数据操作中，势必会有数据写入的过程，如果把一些数据写入一张数据库表中，如果写入量有100万，而重复的数据有90万，那么如何让这10%的数据能够更高更高效的写入。

在MySQL方向提供了Insert ignore into,insert into on duplicate,replace into这几种写入的方式，看起来好像都差不多，但是实际上在一些场景下的差异还比较大，如果使用不当，恰恰是性能的瓶颈。

整体上我分为两个大的部分，会分别测试这三种数据写入场景。

第一部分基于id,name的数据列，其中id为主键，自增

第二部分基于id,xid,name的数据列，其中id为主键，自增，xid为唯一性索引

至于为什么要这么分，我们可以先看结果再做讨论。

1

   

基于id,name的数据列，其中id为主键，自增

  为了三种测试场景的基准对等，数据初始化会按照如下的三种方式来进行。

数据初始化

create table test_data(id int primary key auto_increment,name varchar(30)) engine=innodb;
insert into test_data values(1,'aa'),(2,'bb'),(3,'cc');
show create table test_data\G
       Table: test_data
Create Table: CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

insert ignore

insert ignore into test_data values(1,'aa');
Query OK, 0 rows affected, 1 warning (0.00 sec)
>>show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)
show create table test_data\G
       Table: test_data
Create Table: CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
insert ignore into test_data values(1,'aaa');
Query OK, 0 rows affected, 1 warning (0.01 sec)
>>show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)
insert ignore into test_data values(4,'cc');
Query OK, 1 row affected (0.01 sec)
select * from test_data;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | cc   |
+----+------+
4 rows in set (0.00 sec)

replace into场景

>>replace into test_data values(1,'aa');
Query OK, 1 row affected (0.01 sec)
show create table test_data\G
       Table: test_data
Create Table: CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
replace into test_data values(1,'aaa');
Query OK, 2 rows affected (0.00 sec)
replace into test_data values(4,'cc');
Query OK, 1 row affected (0.00 sec)
select *from test_data;
+----+------+
| id | name |
+----+------+|  1 | aaa  |
|  2 | bb   |
|  3 | cc   |
|  4 | cc   |
+----+------+
4 rows in set (0.00 sec)

insert into on duplicate场景

insert into test_data values(1,'aa') on duplicate key update id=id;
Query OK, 0 rows affected (0.00 sec)
insert into test_data values(1,'aa') on duplicate key update id=id, name=name;
Query OK, 0 rows affected (0.00 sec)
show create table test_data\G
       Table: test_data
Create Table: CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
insert into test_data values(1,'aaa') on duplicate key update id=id;
Query OK, 0 rows affected (0.00 sec)
insert into test_data values(1,'aaa') on duplicate key update id=id,name=name;
Query OK, 0 rows affected (0.00 sec)
insert into test_data values(4,'cc') on duplicate key update id=id;
Query OK, 1 row affected (0.01 sec)
insert into test_data values(4,'ccc') on duplicate key update id=id, name=name;
Query OK, 0 rows affected (0.00 sec)
select * from test_data;
+----+------+
| id | name |
+----+------+|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | cc   |
+----+------+
4 rows in set (0.00 sec)

小结：这三种场景的结果从自增列的处理方式来看是完全对等的，但是对于重复数据的处理方式还是存在差异。 

相比而言，replace into和insert into on duplicate存在本质的区别，replace into是覆盖写，即删除原来的，写入新的。不光是主键列，其他列也会保持一致

insert into on duplicate则可以根据自己的需求来定制重复数据的处理策略，不会主动改变数据。

insert ignore into 在这种场景下最为通用，而且对于数据的侵入性最小。

所以如果要保证源端的数据基于主键完全一致，不管非主键列的数据是否一致，都需要完全覆盖，选择replace into是一种好的方法。

否则采用insert into on duplcate或者insert ignore into

2

   

基于id,xid,name的数据列，其中id为主键，自增,xid为唯一性索引

  为了三种测试场景的基准对等，数据初始化会按照如下的三种方式来进行。

数据初始化

create table test_data(id int primary key auto_increment,xid int unique key,name varchar(30)) engine=innodb;
insert into test_data(xid,name) values(1,'aa'),(2,'bb'),(3,'cc');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
select *from test_data;
+----+------+------+
| id | xid  | name |
+----+------+------+
|  1 |    1 | aa   |
|  2 |    2 | bb   |
|  3 |    3 | cc   |
+----+------+------+
3 rows in set (0.00 sec)

insert ignore into

insert ignore into test_data(xid,name) values(1,'aa');
Query OK, 0 rows affected, 1 warning 
CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `xid` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `xid` (`xid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
insert ignore into test_data(xid,name) values(1,'aaa');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql--root@localhost:test 18:58:13>>show warnings;
+---------+------+-----------------------------------+
| Level   | Code | Message                           |
+---------+------+-----------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'xid' |
+---------+------+-----------------------------------+
insert ignore into test_data(xid,name) values(4,'dd');
Query OK, 1 row affected (0.00 sec)
Create Table: CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `xid` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `xid` (`xid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
>select * from test_data;
+----+------+------+
| id | xid  | name |
+----+------+------+
|  1 |    1 | aa   |
|  2 |    2 | bb   |
|  3 |    3 | cc   ||  6 |    4 | dd   |
+----+------+------+
4 rows in set (0.00 sec)

replace into

replace into test_data(xid,name) values(1,'aa');
Query OK, 2 rows affected (0.00 sec)
+----+------+------+
| id | xid  | name |
+----+------+------+
|  2 |    2 | bb   |
|  3 |    3 | cc   ||  4 |    1 | aa   |
+----+------+------+
3 rows in set (0.00 sec)
replace into test_data(xid,name) values(1,'aaa');
Query OK, 2 rows affected (0.01 sec)
select *from test_data;
+----+------+------+
| id | xid  | name |
+----+------+------+
|  2 |    2 | bb   |
|  3 |    3 | cc   ||  5 |    1 | aaa  |
+----+------+------+
replace into test_data(xid,name) values(4,'cc');
Query OK, 1 row affected (0.00 sec)
select *from test_data;
+----+------+------+
| id | xid  | name |
+----+------+------+
|  2 |    2 | bb   |
|  3 |    3 | cc   |
|  5 |    1 | aaa  ||  6 |    4 | dd   |
+----+------+------+
4 rows in set (0.00 sec)

insert into on duplicate

insert into test_data(xid,name) values(1,'aa') on duplicate key update xid=xid;
Query OK, 0 rows affected (0.00 sec)
insert into test_data(xid,name) values(1,'aa') on duplicate key update xid=xid, name=name;
Query OK, 0 rows affected (0.01 sec)
+----+------+------+
| id | xid  | name |
+----+------+------+
|  1 |    1 | aa   |
|  2 |    2 | bb   |
|  3 |    3 | cc   |
+----+------+------+
3 rows in set (0.00 sec)
insert into test_data(xid,name) values(1,'aaa') on duplicate key update xid=xid;
Query OK, 0 rows affected (0.01 sec)
insert into test_data(xid,name) values(1,'aaa') on duplicate key update xid=xid,name=name;
Query OK, 0 rows affected (0.00 sec)
insert into test_data(xid,name) values(4,'cc') on duplicate key update xid=xid;
Query OK, 1 row affected (0.01 sec)
insert into test_data(xid,name) values(4,'ccc') on duplicate key update xid=xid, name=name;
Query OK, 0 rows affected (0.00 sec)
select * from test_data;
+----+------+------+
| id | xid  | name |
+----+------+------+
|  1 |    1 | aa   |
|  2 |    2 | bb   |
|  3 |    3 | cc   |
|  8 |    4 | cc   |
+----+------+------+
4 rows in set (0.00 sec)

小结：在这个场景里面，可以看到三种场景的变化真是很大，而且区别也很明显。 

insert ignore into如果不指定自增列，尽管没有写入数据，但是自增列依然会自增

replace into如果不指定自增列，会看到数据重新写入的效果已经非常明显，而且自增列始终会自动维护。

insert into on duplicate对于重复数据依然会消耗自增列值，实现相对更加灵活。

转文至此。

21f6df531d32bda1848ab1f9967d8d36.gif

以下为个人公众号“一森咖记”，欢迎关注。 9f7351e8e639198d0c9d6642f9c1ca36.png                        

   

近期热文

你可能也会对以下话题感兴趣。点击链接便可查看。

  • MySQL：主从同步延迟Seconds_Behind_Master越来越大，什么鬼？

  • MySQL安全性解决方案，是个啥？

  • 如果要升级到MySQL 8.0，可以分几个阶段走

  • Oracle和MySQA

  • MySQL中left join的几个SQL对比X

  • MySQL静态数据加密和企业版TDE

  • MySQL8功能详解——数据字典

  • MySQL中如何查看正在执行的SQL语句？

  • MySQL的主键命名挺任性，就这么定了

  • 浅谈MySQL三种锁：全局锁、表锁和行锁

  • LINUX环境：MySQL和Oracle开机自启动，咋搞？

  • 生产环境：mysqlbackup逻辑备份的一种shell脚本实现

  • 生产环境：mysqlbackup物理备份的一种shell脚本实现

  • MySql 8.0.16 客户端连接失败

  • Oracle如何访问MySql：透明网关

  • 一款好的数据库监控工具：天兔数据库监控系统V3.8搭建

  • MySQL主从架构搭建+GTID同步方式部署

  • 用户：单台服务器部署多MySQL实例，咋弄？

  • MySQL服务器一次异常掉电的恢复

  • MySQL8.0.18新特性之bash join探究

weixin_39607798
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
求一个数的叠加和sum=a+aa+aaa+......
weixin_30502965的博客
04-15 653
#include<stdio.h> int main() { int a,n; scanf("%d%d",&a,&n); int sum=0; int t=0; for(int i=0;i<n;i++) { t=t*10+a; sum+=t; }...
java中的mysql insert 避免直接用insert语句
bitterliquor的专栏
11-08 1187
Inserting a Row into a Database Table Using a Prepared Statement If you have a SQL statement that needs to be executed many times but with different values, a prepared statement can be used
MySQL：插入数据insert ignore，重复数据自动忽略
会写Bug的攻城狮
12-11 1万+
MySQL：插入数据insert ignore，重复数据自动忽略
mysql避免重复插入记录insert ignore 、on duplicate key update、replace into
最新发布
Java学习之道
06-20 1358
星标▲Java学习之道▲一起成长，一起学习~哈喽，大家好，我是阿淼。今天梳理一下mysql中避免重复插入记录的集中操作。1序回顾以前写的项目，发现在规范的前提下，还是可以做点骚操作的。假如项目使用的MySQL，首先创建测试数据表（建表语句中默认使用utf8mb4以及utf8mb4_unicode_ci，感兴趣的读者可以自行搜索这两个配置）：CREATETABLE`student`( `i...
mysql insert的几点操作(DELAYED,IGNORE,ON DUPLICATE KEY UPDATE )
01-19
INSERT语法 INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,…)] VALUES ({expr | DEFAULT},…),(…),… [ ON DUPLICATE KEY UPDATE col_name=expr, … ] 或： INSERT [LOW_...
Mysql中replace与replace into的用法讲解
09-09
而 `REPLACE` 的效率相比 `INSERT IGNORE` 或 `INSERT ON DUPLICATE KEY UPDATE` 在某些情况下可能会更高，因为它不需要检查每一行是否存在冲突，而是直接进行替换。 总结来说，`REPLACE` 和 `REPLACE INTO` 是...
正确使用MySQL INSERT INTO语句
01-19
以下的文章主要介绍的是MySQL INSERT INTO语句的实际用法以及MySQL INSERT INTO语句中的相关语句的介绍，MySQL INSERT INTO语句在实际应用中是经常使用到的语句，所以对其相关的内容还是多多掌握为好。 INSERT [LOW...
逐步分析MySQL从库com_insert无变化的原因
09-10
在MySQL的主从复制过程中，确保主库和从库的数据一致性至关重要。`com_insert`变量用于记录执行的`INSERT`语句数量，理论上在主从复制正常的情况下，主库和从库的这个值应当保持一致。 MySQL提供了两种主要的二进制...
INSERT INTO… ON DUPLICATE KEY UPDATE用法
热门推荐
码农崛起
07-05 3万+
https://yq.aliyun.com/ziliao/59813MySql中4种批量更新的方法   最近在完成MySql项目集成的情况下，需要增加批量更新的功能，根据网上的资料整理了一下，很好用，都测试过，可以直接使用。   mysql 批量更新共有以下四种办法1、.replace into 批量更新   replace into test_tbl (id,dr) values (1,'2')...
MySQL INSERT IGNORE语句
Jaylon Wang的专栏
11-01 6015
MySQL INSERT IGNORE语句 摘要：在本教程中，您将学习如何使用MySQLINSERT IGNORE语句将数据插入表中。 MySQLINSERT IGNORE语句介绍 当您使用该INSERT语句向一个表中添加多行时，如果在处理过程中发生错误，则MySQL终止该语句并返回错误。结果，没有行插入到表中。 但是，如果使用该INSERT IGNORE语句，则会忽略包含导致错误的无...
MySql避免重复插入记录方法(ignore,Replace,ON DUPLICATE KEY UPDATE)
wbj16116的博客
02-19 412
2015年07月10日 11:54:2563871本文章来给大家提供三种在mysql中避免重复插入记录方法，主要是讲到了ignore,Replace,ON DUPLICATE KEY UPDATE三种方法，各位同学可尝试参考。案一：使用ignore关键字如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用： 代码如下复制代码1 INSERT IGNORE ...
insert into 的加强版replace into和 insert ignore into
u013677636的博客
08-23 3304
insert into 是插入表最常用的语句，一般我们往表里插入数据要首先判断是否存在，再插入。举个栗子~~~~ if not exists (select 1 from t where id = 1) then insert into t(id, update_time) values(1, getdate()) else update t set update_time = get
mysql 排除重复记录 insert 使用INSERT IGNORE into
创业的程序员
10-22 1万+
INSERT IGNORE 与INSERT INTO的区别 INSERT IGNORE 与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。 eg: insert ignore into table(name) 
MySQL插入语句insert into,insert ignore into,insert into ... on duplicate key update,replace into-解决唯一键约束
ThinkWon的博客
06-08 1万+
文章目录常用插入语句insert intoinsert ignore intoinsert into ... on duplicate key updatereplace into使用场景总结 当MySQL表字段设置unique key或者primary key时，被约束的字段就必须是唯一的。新插入数据直接使用insert into，如果出现唯一性冲突，就会抛出异常。我们可以根据需求选择合适的插入语句。 为了演示，我们先新建一张user表，SQL语句如下，其中user_id为主键，username为唯一索引
插入MySQL数据库前去除重复数据的几种方法
杨春建的博客
01-29 2611
原文地址：http://blog.csdn.net/u011389474/article/details/52822143 在数据存储过程中，可能会遇到数据主键重复的情况，我们可以通过下面几个方法进行处理：  1. 若数据不存在插入，存在更新  2. 使用duplicate key关键字，如插入数据时发生主键冲突就更新数据  3. 使用Ingore关键字  4. 使用repl
C语言 - sum = a + aa + aaa + aaaa + ....
W_Jp - 编程爱好者
11-11 3205
题目：求 a + aa + aaa + aaaa + ... 的和的算法 #include #include int main(void) { int val, temp, n, sum = 0, aa = 0; int i; printf("请分别输入数据，执行次数："); scanf("%d %d", &val, &n); temp = val; if(val=1
mysql插入函数和删除函数的区别
写bug的小哥哥
04-09 441
1.insert ignore into : 当插入数据时，数据库不会检查主键（primary key）或者唯一主键（unique key），如出现重复数据，也不返回错误，可继续往下执行。 2.insert into : 当插入数据时，数据库会检查主键（primary key）或者唯一主键（unique key），如果出现重复会报错，无法插入。 3.replace into ： 如果存在主键（pr...
mysql inser into on_数据库避免插入重复数据需求解决：MySQL之insert、insert ignore、replace和insert into on duplicate key ...
05-23
在 MySQL 中，我们可以使用以下几种方法来避免插入重复数据： 1. INSERT IGNORE INTO：如果插入的数据与现有数据冲突，则忽略该插入操作。 例如： ``` INSERT IGNORE INTO table1 (id, name) VALUES (1, 'John'); ``` 如果表 table1 中已经存在 id 为 1 的数据，则该插入操作会被忽略。 2. REPLACE INTO：如果插入的数据与现有数据冲突，则删除已有数据并插入新数据。 例如： ``` REPLACE INTO table1 (id, name) VALUES (1, 'John'); ``` 如果表 table1 中已经存在 id 为 1 的数据，则先删除该数据，再插入新数据。 3. INSERT INTO ON DUPLICATE KEY UPDATE：如果插入的数据与现有数据冲突，则更新已有数据。 例如： ``` INSERT INTO table1 (id, name) VALUES (1, 'John') ON DUPLICATE KEY UPDATE name = 'Tom'; ``` 如果表 table1 中已经存在 id 为 1 的数据，则更新该数据的 name 属性为 Tom。 这三种方法均可以避免插入重复数据，但具体使用哪种方法还需要根据实际情况来确定。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值