MySQL插入数据重复时，数据已经存在，则更新或者忽略，不存在，则插入数据

四七伵

已于 2025-01-11 17:29:01 修改

阅读量4.3k

点赞数 93

分类专栏： MySQL 开发者宝典文章标签： mysql 后端数据库 sql 学习方法经验分享 mybatis

于 2024-12-18 09:49:51 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_46029085/article/details/144550402

版权

MySQL 开发者宝典专栏收录该内容

14 篇文章

订阅专栏

前言

实现MySQL插入数据重复时更新，数据不存在时插入，只使用一条SQL语句的需求，可以通过以下几种方法来实现。

首页先创建一张表，开始测试

-- 创建一张users表，并把name设置为唯一索引。
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_key` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

# 插入一条数据，做测试数据
INSERT INTO `users` (`name`, `age`) VALUES ('刘备', 50);

1. `INSERT ... ON DUPLICATE KEY UPDATE`

使用条件：

表中必须存在主键或者唯一索引，用于判断数据是否重复。

执行逻辑：

先执行插入，如果不存在，则插入成功；如果唯一索引已存在，则删除刚刚插入的数据，再去更新之前存在的那条记录。

-- 再次插入这个唯一索引存在的数据，如果存在则修改
INSERT INTO `users` (`name`, `age`)
VALUES
	( '刘备', 60 ) ON DUPLICATE KEY UPDATE `name` = '张飞', `age` = 30;

你会发现影响了两条数据（删除了SQL执行时新增的数据，并把原来的数据修改了）

再次查看数据表，数据被修改了

再去查看自增id

优点：

灵活，可以只更新部分字段，而不是全行替换。
插入和更新在一条 SQL 语句中完成，效率较高。

缺点：

仅适用于存在主键或唯一索引的表。
如果多个字段导致唯一索引冲突，需要提前设计索引结构。
执行更新时，会有额外的开销。并且使用自增id时，会丢失一个id。

2. `REPLACE INTO`

使用条件：

表中必须存在主键或唯一索引，用于判断数据是否重复。

执行逻辑：

以唯一键判断数据是否存在。如果不存在，那么新增；如果存在，先删除原来的数据，再新增。

REPLACE INTO `users` (`name`, `age`)
VALUES ('张飞', '55');

你会发现影响了两条数据（删除了原来的数据，并插入了新数据）

再次查看数据表，原来的数据没有了，新的数据id值不一样

优点：

逻辑简单，直接替换整行数据。
适合替换所有列的场景。

缺点：

删除操作会触发外键约束、触发器等，可能导致额外开销。
删除和重新插入会导致主键的id值变化。
对于大表来说，性能不如 ON DUPLICATE KEY UPDATE 高效。

3. `INSERT IGNORE`

使用条件：

不需要依赖主键或唯一索引来触发冲突行为（但是需要唯一索引来作为是否重复的判断条件）。

执行逻辑：

会先执行插入，如果数据不存在，则插入成功；如果存在，则不插入。由于使用了ignore，所以会忽略索引存在错误。

INSERT IGNORE INTO `users` (`name`, `age`)
VALUES ('张飞', 50);

你会发现没有修改任何数据（因为当前唯一索引的数据已存在）

再次查看数据表，没有任何变化

优点：

控制更灵活，可以通过条件进行精确的更新操作。
不会触发删除操作，不会影响外键。

缺点：

不能更新数据，要想更新数据，需要额外的处理。

4. `INSERT IF NOT EXISTS`

使用条件：

不需要依赖主键或唯一索引来触发冲突行为。

执行逻辑：

执行insert前，会先判断条件是否满足。通过not exists判断，如果不存在，则插入；如果存在，则不插入。

注意：新增记录时，select 字段 from，表名称是dual，并不是当前表。如果是当前表，那么在新增记录时（表中没有该记录）会报错。因为第2行的select从当前表查询，已经有了该值。所以必须是dual，或者是其他表也可以。

INSERT INTO `users` ( `name`, `age` )
SELECT
    '张飞',
    50
FROM
    DUAL 
WHERE
    NOT EXISTS ( SELECT * FROM `users` WHERE `name` = '张飞' );

你会发现没有修改任何数据

再次查看数据表，没有任何变化

优点：

避免重复数据：通过 NOT EXISTS 过滤掉已存在的数据，保证数据的唯一性。不需要依赖唯一索引和主键
简洁：将检查与插入操作合并在一条 SQL 语句中，避免写多条查询。

缺点：

性能开销：对于大表，NOT EXISTS 的子查询可能性能较差，尤其是在没有索引的情况下。
不适合并发高的场景：在高并发插入时，可能仍然出现重复数据（需要借助唯一索引等约束）。
不能修改数据：修改数据时，依然需要额外处理。

最后：附上所有测试SQL语句

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_key` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `users` (`name`, `age`) VALUES ('刘备', 50);

INSERT INTO `users` (`name`, `age`)
VALUES
	( '刘备', 60 ) ON DUPLICATE KEY UPDATE `name` = '张飞', `age` = 30;
	
SHOW TABLE STATUS LIKE 'users';

REPLACE INTO  `users` (`name`, `age`)
VALUES ('张飞', 55);

INSERT IGNORE INTO `users` (`name`, `age`)
VALUES ('张飞', 50);

INSERT INTO `users` ( `name`, `age` ) SELECT
'张飞',
50 
FROM
DUAL 
WHERE
	NOT EXISTS ( SELECT * FROM `users` WHERE `name` = '张飞' );