实现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` = '张飞' );
推荐使用场景
- 使用
INSERT ... ON DUPLICATE KEY UPDATE- 当表有主键或唯一索引，并且只需要更新部分字段时，这是最好的选择。
- 使用
REPLACE INTO- 当你需要替换整行数据，并且能接受删除旧数据触发的影响时。
- 使用
INSERT IGNORE- 当你需要灵活控制插入时，且不需要更新数据，数据存在则忽略。
- 使用
INSERT IF NOT EXISTS- 当你需要灵活控制插入时，且不需要依赖任何唯一约束的索引。
结束语：根据实际业务场景选择合适的方案，以平衡性能和数据操作的复杂性。
——如果有更好的方法，希望大家能留言告诉我一下，一起学习进步。