MySQL replace into

 

CREATE TABLE `test` (
  `id` int NOT NULL,
  `age` int DEFAULT NULL,
  `name` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

---

 

insert into重复插入时 会报主键冲突:

insert into test (id,age,name) value (1,1,'t1');
insert into test (id,age,name) value (1,1,'t1');
[SQL]insert into test (id,age,name) value (1,1,'t1');
[Err] 1062 - Duplicate entry '1' for key 'PRIMARY'

 

---

 

replace into主键冲突时会修改:

replace into test (id,age,name) value (2,2,'t2');
[SQL]replace into test (id,age,name) value (2,2,'t2');
受影响的行: 1
时间: 0.005s

 

replace into test (id,age,name) value (1,1,'t1'),(2,2,'t2');
[SQL]replace into test (id,age,name) value (1,1,'t1'),(2,2,'t2');
受影响的行: 2
时间: 0.019s

 

一、不存在冲突 执行语句为insert into

二、只存在主键,主键冲突 执行语句为update 注意：就算主键冲突，如果数据一致，是不会产生binlog

三、只存在唯一索引,唯一索引冲突 执行语句为update

四、同时存在主键和唯一索引

1. 只有主键冲突 执行语句为先delete再insert
2. 只有唯一索引冲突 执行语句为update
3. 同时存在主键和唯一索引冲突，同一行数据 执行语句为先delete再insert
4. 同时存在主键和唯一索引冲突，不同一行数据 执行语句为先delete再update

微信.MySQL replace into行为解析

---

 

INSERT INTO ON DUPLICATE KEY UPDATE 主键冲突时会执行update后面的语句

INSERT INTO test (id,age,name) VALUES (1,1,'t1'),(2,2,'t2')   ON DUPLICATE KEY UPDATE age=1;
[SQL]INSERT INTO test (id,age,name) VALUES (1,1,'t1'),(2,2,'t2')   ON DUPLICATE KEY UPDATE age=1;
受影响的行: 2
时间: 0.042s

 

---