insert into,insert into select,replace into,replace into select,insert ignore into,on duplicate key update

1、replace into

REPLACE的运行与INSERT很相似。只有一点例外：假如表中的一个旧记录与一个用于PRIMARY

KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。
注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。该

语句会与INSERT相同，因为没有索引被用于确定是否新行复制了其它的行。

MySQL 对 SQL 有很多扩展，有些用起来很方便，但有一些被误用之后会有性能问题，还会有一些意料之外的副作用，比如 REPLACE INTO。

所有列的值均取自在REPLACE语句中被指定的值。所有缺失的列被设置为各自的默认值，这

和INSERT一样。您不能从当前行中引用值，也不能在新行中使用值。如果您使用一个例如“

SET col_name = col_name + 1”的赋值，则对位于右侧的列名称的引用会被作为DEFAULT

(col_name)处理。因此，该赋值相当于SET col_name = DEFAULT(col_name) + 1。

为了能够使用REPLACE，您必须同时拥有表的INSERT和DELETE权限。

REPLACE语句会返回一个数，来指示受影响的行的数目。该数是被删除和被插入的行数的和

。如果对于一个单行REPLACE该数为1，则一行被插入，同时没有行被删除。如果该数大于1

，则在新行被插入前，有一个或多个旧行被删除。如果表包含多个唯一索引，并且新行复制

了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。

受影响的行数可以容易地确定是否REPLACE只添加了一行，或者是否REPLACE也替换了其它行

：检查该数是否为1（添加）或更大（替换）。

1. 尝试把新行插入到表中

2. 当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时：

a. 从表中删除含有重复关键字值的冲突行

b. 再次尝试把新行插入到表中

比如有这样一张表：

CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoD

auto 表有一个自增的 id 字段作为主键，字段 k 有 UNIQUE KEY 做唯一性约束。写入几条记录之后会是这样：

xupeng@diggle7:3600(dba_m) [dba] mysql> INSERT INTO auto (k, v, extra) VALUES (1, '1', 'extra 1'), (2, '2', 'extra 2'), (3, '3', 'extra 3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

xupeng@diggle7:3600(dba_m) [dba] mysql> SELECT * FROM auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  1 | 1 | 1    | extra 1 |
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
+----+---+------+---------+
3 rows in set (0.00 sec)

在 slave 节点上是和 master 一致的：

xupeng@diggle8:3600(dba_s) [dba] mysql> SELECT * FROM auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  1 | 1 | 1    | extra 1 |
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
+----+---+------+---------+
3 rows in set (0.00 sec)

xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

可以看到，写入三条记录之后，auto 表的 AUTO_INCREMENT 增长为 4，也就是说下一条不手工为 id 指定值的记录，id 字段的值会是 4。

接下来使用 REPLACE INTO 来写入一条记录：

xupeng@diggle7:3600(dba_m) [dba] mysql> REPLACE INTO auto (k, v) VALUES (1, '1-1');
Query OK, 2 rows affected (0.01 sec)

xupeng@diggle7:3600(dba_m) [dba] mysql> SELECT * FROM auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 1 | 1-1  | NULL    |
+----+---+------+---------+
3 rows in set (0.00 sec)

xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

可以看到 MySQL 说 “2 rows affected”，可是明明是只写一条记录，为什么呢？这是因为 MySQL 在执行 REPLACE INTO auto (k) VALUES (1) 时首先尝试 INSERT INTO auto (k) VALUES (1)，但由于已经存在一条 k=1 的记录，发生了 duplicate key error，于是 MySQL 会先删除已有的那条 k=1 即 id=1 的记录，然后重新写入一条新的记录。

这时候 slave 上出现了诡异的问题：

xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

可以知道，当前表内数据 id 字段的最大值是 4，AUTO_INCREMENT 应该为 5，但在 slave 上 AUTO_INCREMENT 却并未更新，这会有什么问题呢？把这个 slave 提升为 master 之后，由于 AUTO_INCREMENT 比实际的 next id 还要小，写入新记录时就会发生 duplicate key error，每次冲突之后 AUTO_INCREMENT += 1，直到增长为 max(id) + 1 之后才能恢复正常：

xupeng@diggle8:3600(dba_s) [dba] mysql> REPLACE INTO auto (k, v) VALUES (4, '4');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
xupeng@diggle8:3600(dba_s) [dba] mysql> REPLACE INTO auto (k, v) VALUES (5, '5');
Query OK, 1 row affected (0.00 sec)

xupeng@diggle8:3600(dba_s) [dba] mysql> SELECT * FROM auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 1 | 1-1  | NULL    |
|  5 | 5 | 5    | NULL    |
+----+---+------+---------+
4 rows in set (0.00 sec)

没有预料到 MySQL 在数据冲突时实际上是删掉了旧记录，再写入新记录，这是使用 REPLACE INTO 时最大的一个误区，拿之前的例子来说，执行完 REPLACE INTO auto (k, v) VALUES (1, ‘1-1’) 之后，由于新写入记录时并未给 extra 字段指定值，原记录 extra 字段的值就「丢失」了，而通常这并非是业务上所预期的，更常见的需求实际上是，当存在 k=1 的记录时，就把 v 字段的值更新为 ‘1-1’，其他未指定的字段则保持原状，而满足这一需求的 MySQL 方言是 INSERT INTO auto (k, v) VALUES (1, ‘1-1’) ON DUPLICATE KEY UPDATE v=VALUES(v);

鉴于此，很多使用 REPLACE INTO 的场景，实际上需要的是 INSERT INTO … ON DUPLICATE KEY UPDATE，在正确理解 REPLACE INTO 行为和副作用的前提下，谨慎使用 REPLACE INTO。

######################################################################################

replace into的存在的几种情况

当表存在主键并且存在唯一键的时候
- 如果只是主键冲突

mysql> select * from auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 1 | 1-1  | NULL    |
+----+---+------+---------+
3 rows in set (0.00 sec)

mysql> 
mysql> show create table auto\G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

这里我们插入一条主键已经存在的4的数据

mysql> replace into auto(id,k)values(4,5);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 5 | NULL | NULL    |
+----+---+------+---------+
3 rows in set (0.00 sec)

mysql> show create table auto \G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

发现，auto_increment并没有+1，而是针对原来的那一条id=4的记录进行了update，因为没有指定其他列(v,extra)的值，所以，update的时候都使用了默认值.

如果主键跟唯一键都冲突并且在同一行里

mysql> select * from auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 5 | NULL | NULL    |
|  5 | 6 | 6    | NULL    |
+----+---+------+---------+
4 rows in set (0.00 sec)

mysql> show create table auto \G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> replace into auto(id,k,extra)values(5,6,77);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 5 | NULL | NULL    |
|  5 | 6 | NULL | 77      |
+----+---+------+---------+
4 rows in set (0.00 sec)

mysql> show create table auto \G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

我们发现，auto_increment也并没有+1，而是针对原来的那一条id=6的记录进行了update，因为没有指定其他列(v)的值，所以，update的时候都v使用了默认值变成了null

如果主键跟唯一键都冲突不在同一行，对应2条记录呢

我们来看下：

mysql> show create table auto \G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 5 | NULL | NULL    |
|  6 | 6 | 66   | NULL    |
+----+---+------+---------+
4 rows in set (0.00 sec)

mysql> replace into auto(id,k,v)values(6,2,88);
Query OK, 3 rows affected (0.03 sec)

像上面的，主键id=6对应一条记录，唯一索引k=2对应id=2的另外一条记录，所以我们当前插入的记录就会跟2行数据有冲突，我们replace into 看看会有什么结果

mysql> replace into auto(id,k,v)values(6,2,88);
Query OK, 3 rows affected (0.03 sec)

mysql> select * from auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  3 | 3 | 3    | extra 3 |
|  4 | 5 | NULL | NULL    |
|  6 | 2 | 88   | NULL    |
+----+---+------+---------+
3 rows in set (0.00 sec)

mysql> show create table auto \G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

我们发现auto_increment并没有+1，MySQL把原来的id=6的这条记录上进行uppdate，但是发现唯一索引k出现了冲突，所以就把对应冲突的那条数据删除，再进行更新，由于没有指定更新字段extra的数据，所以就把extra更新为默认数据

如果仅仅是唯一键冲突呢？

mysql> select * from auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 5 | NULL | NULL    |
|  5 | 6 | NULL | 77      |
+----+---+------+---------+
4 rows in set (0.00 sec)

mysql> show create table auto \G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> 
mysql> replace into auto(k,v)values(6,66);
Query OK, 2 rows affected (0.04 sec)

mysql> select * from auto;
+----+---+------+---------+
| id | k | v    | extra   |
+----+---+------+---------+
|  2 | 2 | 2    | extra 2 |
|  3 | 3 | 3    | extra 3 |
|  4 | 5 | NULL | NULL    |
|  6 | 6 | 66   | NULL    |
+----+---+------+---------+
4 rows in set (0.00 sec)

mysql> show create table auto \G
*************************** 1. row ***************************
       Table: auto
Create Table: CREATE TABLE `auto` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL,
  `v` varchar(100) DEFAULT NULL,
  `extra` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

这时候，我们发现，，auto_increment已经+1了。MySQL这时候的执行步骤是，首先往表里面插入一条数据，这时候auto_increment+1,但是在插入的时候发现唯一索引的k冲突了，然后把冲突的这条数据删除，然后重新插入，对于没有指定其他列(extra)的值，如extra都使用了默认值变成了null

现在我们可以下结论了：

当replace into 记录只与主键冲突的时候，auto_increment不会增加，它会对与主键冲突的那一条记录进行更新，没有指定的列将会被更新为默认值
当replace into 记录与主键跟唯一索引同时冲突的时候，auto_increment不会增加
1. 如果冲突的主键和索引在同一行记录，则replace into只做更新，对于没有指定值的其他列，将会被更新为默认值，
2. 如果冲突的主键和索引分别对应2行数据，则MySQL将会删除唯一索引的那一行记录，更新对应主键的那一行记录。
当replace into 记录只与唯一索引进行冲突的时候，auto_increment + 1，再对数据进行更新。

最后我们可以对总结分析下，MySQL对replace into的操作是首先是insert操作，如果insert失败，则对insert失败的这条记录进行update，如果update还是失败，则会进行delete操作之后再update。
具体流程是这样的：insert记录，发现主键冲突，则update这一行，update的时候发现存在唯一键冲突，则delete对应的唯一键的行后再进行update。如果insert成功，auto_increment自然+1了，然后对这条记录进行update，update的时候发现存在唯一键冲突，则delete对应的唯一键的行后再进行update。

2、insert ignore into

on duplicate key update问题：

语法：insert into table_name(columns)values(values1,values2) on duplicate key update column1=values(column1),column2=values(column2)
条件：表要求必须有主键或唯一索引才能起效果，否则insert或update无效；注意：主键是一个唯一索引，是一个特殊的唯一索引，所以有这三种情况都可以：表只有主键、表只有唯一索引，表既有主键也有唯一索引。
注意语法：on duplicate key update后面应为需要更新字段，不需要更新的字段不用罗列；
相较于replace into（insert加强版，不存在时insert，存在时先delete后insert）虽然也能达到批量更新目的，但因为删除和添加需要重复维护索引，所以大批量比on duplicate key update性能要差，小量可忽略，自选为主。

########################################

posted @ 2020-08-19 16:24 igoodful 阅读(257) 评论(3) 编辑收藏举报

刷新页面返回顶部

igoodful

一枚数据库管理员（DBA），从事的数据库有：MySQL，PostgreSQL，Redis，MongoDB

insert into,insert into select,replace into,replace into select,insert ignore into,on duplicate key update

1、replace into

replace into的存在的几种情况

2、insert ignore into

on duplicate key update问题：

公告