mysql 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

现在我们可以下结论了:

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大数据学习笔记

MySQL/MariaDB SQL操作笔记

1、修改表字段类型 alter table nodes modify column info varchar(200) 示例操作入下: MariaDB [dbm...

2025
来自专栏抠抠空间

MySQL之表操作

一、创建表   1、创建新表 #语法: create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名...

3377
来自专栏Aox Lei

Mysql 分区介绍(二) —— RANGE分区

通过范围的方式进行分区, 为每个分区给出一定的范围, 范围必须是连续的并且不能重复, 使用VALUES LESS THAN操作符

861
来自专栏听雨堂

一次数据库的整理的sql语句

//查询以井结束的记录 SELECT f_wellnumber, SUBSTRING(f_wellnumber, 1, LEN(f_wellnumber) - ...

1859
来自专栏腾讯云TStack专栏

Zabbix 分区优化

目前 zabbix 的数据主要存储在 history 和 trends 的2个表中,随着时间的推移,这两个表变得非常大,性能会非常差,影响监控的使用。对 MyS...

1.2K0
来自专栏程序员同行者

salt-api return mysql返回的使用,记录操作日志

1323
来自专栏ml

mysql 查询数据库表结构

1.  1 mysql> describe tmp_log; 2 +----------+------------------+------+-----+---...

3578
来自专栏Netkiller

数据库安全·内容版本控制,撰改留痕

以下节选择《Netkiller Architect 手札》 地址 http://www.netkiller.cn/architect/ 接下来几周的话题是数据库...

2994
来自专栏王翔的专栏

如何处理 MySQL错误码 1215:无法添加外键约束?

创建外键失败常见错误,以及处理办法。

2.2K0
来自专栏北京马哥教育

zabbix表分区(适用于zabbix2.0.x,zabbix2.2.x和zabbix2.4.x)[推荐]

本文主要介绍了zabbix进行数据库表分区的方法: 在系统监控中,zabbix已经代替了nagios+cacti,zabbix以其良好的图形展示和高度自定义赢得...

3566

扫码关注云+社区