replace into 解析

一 介绍

在支持业务过程中,经常遇到开发咨询replace into 的使用场景以及注意事项,这里做个总结,从功能原理注意事项上做个说明。

二 原理

2.1 存在主键但是不存在唯一键

表结构

CREATE TABLE `yy` ( `id` bigint(20) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; root@test 02:43:58>insert into yy values(1,'abc'),(2,'bbb'); Query OK, 2 row affected (0.00 sec) root@test 02:55:42>select * from yy; +----+------+ | id | name | +----+------+ | 1 | abc | | 2 | bbb | +----+------+ 2 rows in set (0.00 sec)

如果本来已经存在的主键值,那么MySQL做update操作。

root@test 02:55:56>replace into yy values(1,'ccc'); Query OK, 2 rows affected (0.00 sec) ### UPDATE test.yy ### WHERE ### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2='abc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */ ### SET ### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2='ccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

如果本来相应的主键值没有,那么做insert 操作

replace into yy values(2,'bbb'); ### INSERT INTO test.yy ### SET ### @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2='bbb' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

2.2 当表中主键和唯一键同时存在时

CREATE TABLE `yy` ( `id` int(11) NOT NULL DEFAULT \'0\', `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL PRIMARY KEY (`id`), UNIQUE KEY `uk_bc` (`b`,`c`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

情形1 主键冲突

root@test 04:37:18>replace into yy values(1,2,3); Query OK, 1 row affected (0.00 sec) root@test 04:37:37>replace into yy values(2,2,4); Query OK, 1 row affected (0.00 sec) root@test 04:38:05>select * from yy; +----+------+------+ | id | b | c | +----+------+------+ | 1 | 2 | 3 | | 2 | 2 | 4 | +----+------+------+ 2 rows in set (0.00 sec) root@test 04:38:50>replace into yy values(1,2,5); Query OK, 2 rows affected (0.00 sec) root@test 04:38:58>select * from yy; +----+------+------+ | id | b | c | +----+------+------+ | 2 | 2 | 4 | | 1 | 2 | 5 | +----+------+------+ 2 rows in set (0.00 sec)

主键冲突时,数据库对表做先删除然后插入的操作,也即先删除id=1的记录,然后插入记录(1,2,5).

### DELETE FROM test.yy ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=1 is_null=0 */ ### @3=3 /* INT meta=0 nullable=1 is_null=0 */ ### INSERT INTO test.yy ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=1 is_null=0 */ ### @3=5 /* INT meta=0 nullable=1 is_null=0 */

情形2 唯一建冲突

root@test 04:48:30>select * from yy; +----+------+------+ | id | b | c | +----+------+------+ | 1 | 2 | 4 | | 2 | 2 | 5 | | 3 | 3 | 5 | | 4 | 3 | 6 | +----+------+------+ 4 rows in set (0.00 sec) root@test 04:53:21>replace into yy values(5,3,6); Query OK, 2 rows affected (0.00 sec) root@test 04:53:40>select * from yy; +----+------+------+ | id | b | c | +----+------+------+ | 1 | 2 | 4 | | 2 | 2 | 5 | | 3 | 3 | 5 | | 5 | 3 | 6 | +----+------+------+ 4 rows in set (0.00 sec)

主键不冲突,唯一键冲突时,数据库对表 唯一键为(3,6)的行做update操作,将主键修改为要插入的值,id=4 改为id=5。

### UPDATE test.yy ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2=3 /* INT meta=0 nullable=1 is_null=0 */ ### @3=6 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @1=5 /* INT meta=0 nullable=0 is_null=0 */ ### @2=3 /* INT meta=0 nullable=1 is_null=0 */ ### @3=6 /* INT meta=0 nullable=1 is_null=0 */

情形3 主键和唯一键同时冲突,如果需要插入的值的主键 和唯一和表中已经存在的存在冲突。

root@test 04:53:52>replace into yy values(1,3,6); Query OK, 3 rows affected (0.00 sec) ---注意此处影响的行数是3 root@test 04:55:35>select * from yy; +----+------+------+ | id | b | c | +----+------+------+ | 2 | 2 | 5 | | 3 | 3 | 5 | | 1 | 3 | 6 | +----+------+------+ 3 rows in set (0.00 sec)

要插入的值(1,3,6) 主键于 表里面的id=1的值冲突,唯一键(3,6)和表中id=5的记录冲突,MySQL 处理的时候 ,先删除id=1的行,然后更新了id=5的行。

### DELETE FROM test.yy ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=1 is_null=0 */ ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ ### UPDATE test.yy ### WHERE ### @1=5 /* INT meta=0 nullable=0 is_null=0 */ ### @2=3 /* INT meta=0 nullable=1 is_null=0 */ ### @3=6 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=3 /* INT meta=0 nullable=1 is_null=0 */ ### @3=6 /* INT meta=0 nullable=1 is_null=0 */

三 结论 对表进行replace into操作的时候: 当不存在冲突时,replace into 相当于insert普通的操作。 当存在pk冲突的时候是先delete再insert。如果主键自增,则AUTO_INCREMENT属性不变。

master test 05:39:25> show create table yy \G *************************** 1. row *************************** Table: yy Create Table: CREATE TABLE `yy` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) master test 05:39:35> replace into yy(id,name) values(1,'ccc'); Query OK, 2 rows affected (0.00 sec) master test 05:39:40> show create table yy \G *************************** 1. row *************************** Table: yy Create Table: CREATE TABLE `yy` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) master test 05:40:41> insert into yy(id) values(3) on duplicate key update name='youzan'; Query OK, 2 rows affected (0.00 sec) master test 05:41:20> show create table yy \G *************************** 1. row *************************** Table: yy Create Table: CREATE TABLE `yy` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

当存在uk冲突的时候是直接update。如果主键自增的,则AUTO_INCREMENT属性会做 +1 操作。

master test 05:46:31> show create table xx \G *************************** 1. row *************************** Table: xx Create Table: CREATE TABLE `xx` ( `id` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) NOT NULL DEFAULT '0', `c` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `uk_b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) master test 06:14:07> replace into xx(b,c) values(2,5); Query OK, 1 row affected (0.00 sec) master test 06:14:28> show create table xx \G *************************** 1. row *************************** Table: xx Create Table: CREATE TABLE `xx` ( `id` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) NOT NULL DEFAULT '0', `c` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `uk_b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 master test 06:17:10> insert into xx(b,c) values(4,10) on duplicate key update c=10; Query OK, 2 rows affected (0.00 sec) master test 06:17:13> show create table xx \G *************************** 1. row *************************** Table: xx Create Table: CREATE TABLE `xx` ( `id` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) NOT NULL DEFAULT '0', `c` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `uk_b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

原文发表时间:2018-01-08

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Netkiller

重新整理AUTO_INCREMENT字段

节选择《Netkiller MySQL 手札》 13.9. 重新整理AUTO_INCREMENT字段 AUTO_INCREMENT 并非按照我们意愿,顺序排列...

3455
来自专栏康怀帅的专栏

MySQL 修改数据表 ALTER

修改列定义 ALTER table 表名 MODIFY 字段名 列定义 位置[FIRST | AFTER 字段名]; 修改列名称 ALTER table 表名 ...

3516
来自专栏Netkiller

重新整理AUTO_INCREMENT字段

节选择《Netkiller MySQL 手札》 13.9. 重新整理AUTO_INCREMENT字段 AUTO_INCREMENT 并非按照我们意愿,顺序排列,...

2896
来自专栏程序员同行者

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

1323
来自专栏python3

mysql-数据的增删改

1042
来自专栏抠抠空间

MySQL之表操作

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

3377
来自专栏Netkiller

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

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

2994
来自专栏Aox Lei

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

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

861
来自专栏王翔的专栏

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

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

2.2K0
来自专栏听雨堂

使用子查询时应当注意的

在一个查询中: UPDATE a SET a.scts = b.v1, a.YCYL = b.v2, a.YCSL = b.v3 FROM kfdbsyy a,...

21110

扫码关注云+社区