MySQL插入insert与更新replace命令的辨析

读书笔记

新建一个test表，三个字段，id，name，remarks,  id是主键，name是唯一索引；
CREATE TABLE `test` (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL,
  `remarks` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`name`)
) ;

insert into test(id,name,remarks) values (1,'唐僧','师傅'),(2,'孙悟空','齐天大圣'),(3,'猪八戒','天蓬元帅'); #向表里插入三条记录值。如果是一条记录，则values后一个括号即可。
结果如下：
+----+--------+----------+
| id | name   | remarks  |
+----+--------+----------+
|  1 | 唐僧   | 师傅     |
|  2 | 孙悟空 | 齐天大圣 |
|  3 | 猪八戒 | 天蓬元帅 |
+----+--------+----------+

replace into test(id,name,remarks) values (1,'沙僧','卷帘大将'); #插入一条记录，但是主键重复
结果如下：
+----+--------+----------+
| id | name   | remarks  |
+----+--------+----------+
|  1 | 沙僧   | 卷帘大将 |
|  2 | 孙悟空 | 齐天大圣 |
|  3 | 猪八戒 | 天蓬元帅 |
+----+--------+----------+
结果是主键重复的行，原有数据被更新

replace into test(id,name,remarks) values (4,'猪八戒','龙太子'); #插入一条记录，但是唯一索引重复
结果如下：
+----+--------+----------+
| id | name   | remarks  |
+----+--------+----------+
|  1 | 沙僧   | 卷帘大将 |
|  2 | 孙悟空 | 齐天大圣 |
|  4 | 猪八戒 | 龙太子   |
+----+--------+----------+
结果是唯一索引重复的行，原有数据被更新，包括主键

replace into 和 insert into 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。
前提是插入数据的表必须有主键或者是唯一索引！否则的话将导致表中出现重复的数据。

update test set name='白龙马' where id=4;#修改id=4的name为'白龙马'
结果如下：
+----+--------+----------+
| id | name   | remarks  |
+----+--------+----------+
|  1 | 沙僧   | 卷帘大将 |
|  2 | 孙悟空 | 齐天大圣 |
|  4 | 白龙马 | 龙太子   |
+----+--------+----------+

insert into test(id,name,remarks) values (5,'唐僧','龙太子') on duplicate key update remarks=values(remarks);#插入一条记录，正常情况下执行insert语句，如果发现主键或者唯一键重复了，那么则修改remarks的内容，但是不修改key，即主键或者唯一键
结果如下：
+----+--------+----------+
| id | name   | remarks  |
+----+--------+----------+
|  1 | 沙僧   | 卷帘大将 |
|  2 | 孙悟空 | 齐天大圣 |
|  4 | 白龙马 | 龙太子   |
|  5 | 唐僧   | 龙太子   |
+----+--------+----------+
结果是正常输入了一条记录

insert into test(id,name,remarks) values (5,'唐僧','师傅') on duplicate key update remarks=values(remarks);#输入一条记录，其中主键和唯一键都是重复的，其他是新的
结果如下：
+----+--------+----------+
| id | name   | remarks  |
+----+--------+----------+
|  1 | 沙僧   | 卷帘大将 |
|  2 | 孙悟空 | 齐天大圣 |
|  4 | 白龙马 | 龙太子   |
|  5 | 唐僧   | 师傅     |
+----+--------+----------+
结果是重复行remarks得到更新

insert into test(id,name,remarks) values (6,'唐僧','天蓬元帅') on duplicate key update remarks=values(remarks);#输入一条记录，其中主键和其他是新的，唯一键是重复的
结果如下：
+----+--------+----------+
| id | name   | remarks  |
+----+--------+----------+
|  1 | 沙僧   | 卷帘大将 |
|  2 | 孙悟空 | 齐天大圣 |
|  4 | 白龙马 | 龙太子   |
|  5 | 唐僧   | 天蓬元帅 |
+----+--------+----------+
结果是remarks得到更新,主键没有变
如果想让主键也更新，则在values后面加入id即可insert into test(id,name,remarks) values (6,'唐僧','天蓬元帅') on duplicate key update id=values(id),remarks=values(remarks);
结果如下：
+----+--------+----------+
| id | name   | remarks  |
+----+--------+----------+
|  1 | 沙僧   | 卷帘大将 |
|  2 | 孙悟空 | 齐天大圣 |
|  4 | 白龙马 | 龙太子   |
|  6 | 唐僧   | 天蓬元帅 |
+----+--------+----------+
结果是remarks和主键得到更新
修改唯一键同理。

replace into和on duplcate key update都是只有在主键primary key或者唯一键unique key冲突的时候才会执行。如果数据存在，replace into则会将原有数据删除，再进行插入操作，而on duplicate key update则是执行update后面的语句。

DROP TABLE `test`;#实验完毕，可以删除此表