MySQL如果数据存在则更新，不存在则插入

如果数据存在则更新，不存在则插入，MySQL有duplicate、replace into、replace三种方式如何更新数据？
insert ignore into 又是如何插入数据的呢？

准备表和基础数据

测试MySQL版本:8.0.35

use testdb;
#drop table tb_student;
CREATE TABLE `tb_student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增',
  `num` int not null default 0 COMMENT '',
  `name` varchar(30) DEFAULT '' COMMENT 'appid',
  `address` varchar(100) default '' COMMENT '家庭地址',
  `mobile` varchar(30) DEFAULT '' COMMENT '手机号',
   primary key (id),
   unique key idx_num(num)
   ) ENGINE=InnoDB comment ='学生信息';
   
select * from tb_student;

测试 INSERT INTO ... ON DUPLICATE KEY UPDATE

功能： MySQL语法支持如果数据存在则更新，不存在则插入，首先判断数据存在还是不存在的那个字段要设置成unique索引。
语法： INSERT INTO 表名(唯一索引列, 列2, 列3) VALUE(值1, 值2, 值3) ON DUPLICATE KEY UPDATE 列=值, 列=值

初始化第一条数据

insert into tb_student (num,name,address,mobile)values(1,'张三','上海','18111111111');

不存在则插入

insert into tb_student(num,name,mobile) values(10,'李四','13100000000') on duplicate key update name='李四',mobile='1310000000';

存在则更新

从下面的结果可以看出，第二条记录主键id=2并没有变化，on duyplicate key update 直接在原记录上更新字段值。

insert into tb_student(num,name,mobile) values(10,'李四','13188888888') on duplicate key update name='李四',mobile='13188888888';

INSERT部分，未指明唯一索引列有两种情况：

  1. 如果表中不存在索引列默认值（这里是int,默认值指定的是0）的记录，则直接插入，索引列为默认值；
  2. 如果表中存在索引列默认值记录时，则更新索引字段为默认值记录的其他数据。

insert into tb_student(id,name,mobile) values(3,'王五','15611111111') on duplicate key update name='王五',mobile='15611111111';

第二次未指明唯一索引列插入数据，从下面的结果看出，王五的数据被刘麻子的数据更新掉了。

insert into tb_student(id,name,mobile) values(4,'刘麻子','15899999999') on duplicate key update name='刘麻子',mobile='15899999999';

on duplicate key update特点
  • DUPLICATE不会删除原有的记录。即：不会破坏索引。
  • 不指定唯一索引列时，直接将数据插入表中，索引列是默认值，如果表中已经存在索引列默认值时，就将索引列为默认值的数据更新掉。

测试 REPLACE INTO

REPLACE INTO 主要作用类似 INSERT 插入操作。主要的区别是 REPLACE INTO 会根据主键或者唯一索引检查数据是否存在，如果存在就先删除再更新。

  • 语法：REPLACE INTO 表名称(列1, 列2, 列3) VALUES(值1, 值2, 值3)
不存在则插入

replace into tb_student(num, name, mobile) VALUES(30, '刘备', '13122222222');

注意：此时的主键id=4

存在则先删除后插入

注意：从下面的结果上看，此时的主键id=4的一条记录被删除，重新插入了一条主键id=5的新记录。

replace into tb_student(num, name, mobile) VALUES(30, '刘备', '13122222222');

REPLACE INTO 特点

REPLACE INTO底层是先删除后插入数据，会破坏索引、重新维护索引。
必须要有主键或唯一索引才能有效，否则replace into就只新增了。
测试REPLACE
replace是mysql 里面处理字符串比较常用的函数，可以替换字符串中的内容。类似的处理字符串的还有trim截取操作。

  • 语法：replace(object,search,replace)
查询结果替换

select replace('aaabbbcccc','b','d');

更新数据

update tb_student set address=replace(address,'上','青') where id=1;

测试insert ignore into

如果插入的数据在表中已经存在（主键或者唯一键已存在），使用insert ignore 语法可以忽略插入重复的数据。

  • 语法：insert ignore into table_name values…
主键不冲突，直接插入

insert ignore into tb_student(id, num,name, mobile) VALUES(6, 40,'张飞', '13144444444');

主键冲突，忽略插入

insert ignore into tb_student(id, num,name, mobile) VALUES(6, 40,'赵云', '13155555555');

去掉ignore，SQL报异常

insert into tb_student(id, num,name, mobile) VALUES(6, 40,'赵云', '13155555555');

插入语句不指定索引列时的两种情况
  1. 如果表中不存在索引列默认值（这里是int,默认值指定的是0）的记录，则直接插入，索引列为默认值；
  2. 如果表中存在索引列默认值记录时，则插入不成功，因为和默认值的唯一索引冲突了。

总结：

on duplicate key update特点
  • DUPLICATE不会删除原有的记录。即：不会破坏索引。
  • 不指定唯一索引列时，直接将数据插入表中，索引列是默认值，如果表中已经存在索引列默认值时，就将索引列为默认值的数据更新掉。
replace into特点
  • REPLACE INTO底层是先删除后插入数据，会破坏索引、重新维护索引。
  • 必须要有主键或唯一索引才能有效，否则replace into就只新增了。
replace特点
  • replace是MySQL里面处理字符串比较常用的函数，可以替换字符串中的内容。
insert ignore into特点
  • 主键冲突时，忽略本次插入的数据，否则正常插入。
posted @ 2024-03-11 17:19  熊仔其人  阅读(5392)  评论(0)  编辑  收藏  举报