通信人家园

标题: MySQL插入insert与更新replace命令的辨析  [查看完整版帖子] [打印本页]

时间:  2022-9-8 11:26
作者: 读书笔记     标题: 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`;#实验完毕,可以删除此表








通信人家园 (https://www.txrjy.com/) Powered by C114