您的位置 首页 php

关于MySQL中insert ignore,insert on duplicate和replace into，你可能没想过区别

智云科技发布于 2022年8月1日评论关闭阅读(292)

这是学习笔记的第 2187 篇文章

读完需要

分钟

速读仅需5分钟

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

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

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

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

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

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

基于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 AUTO_INCREMENT,

`name` varchar( 30 ) DEFAULT ,

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 AUTO_INCREMENT,

`name` varchar( 30 ) DEFAULT ,

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 AUTO_INCREMENT,

`name` varchar( 30 ) DEFAULT ,

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 AUTO_INCREMENT,

`name` varchar( 30 ) DEFAULT ,

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

基于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 AUTO_INCREMENT,

`xid` int(11) DEFAULT ,

`name` varchar( 30 ) DEFAULT ,

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@loca lh ost: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 AUTO_INCREMENT,

`xid` int(11) DEFAULT ,

`name` varchar( 30 ) DEFAULT ,

PRIMARY KEY (`id`),

UNIQUE KEY `xid` (`xid`)

) ENGINE=InnoDB AUTO_INCREMENT=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对于重复数据依然会消耗自增列值，实现相对更加灵活。

标签:
打赏 点赞(1)

文章来源：智云一二三科技

文章标题：关于MySQL中insert ignore,insert on duplicate和replace into，你可能没想过区别

文章地址：https://www.zhihuclub.com/79010.shtml

关于作者: 智云科技

相关文章

热门文章

1分享新浪图床上传接口源码

点赞(973)阅读(61,637)

2PHP简单实现路由Route功能

点赞(1.19K)阅读(35,992)

3Tideways、xhprof 和 xhgui 打造 PHP 非侵入式监控平台

点赞(413)阅读(4,324)

4centos系统如何查看是否安装了mysql

点赞(577)阅读(3,813)

5curl 工具简述

点赞(567)阅读(3,002)
网站地图