在 MySQL 中处理批量插入 / 更新时的冲突,可结合 INSERT ... ON DUPLICATE KEY UPDATE 语法与 VALUES() 函数实现高效操作。以下是几种常见场景的解决方案:
1. 批量插入,冲突时更新(最常用)
可以使用 INSERT ... ON DUPLICATE KEY UPDATE
语句来实现批量插入并在发生冲突时更新的操作。这种语法特别适用于需要"存在则更新,不存在则插入"的场景。
基本语法
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value1, value2, ...), ... ON DUPLICATE KEY UPDATE column1 = VALUES(column1), column2 = VALUES(column2), ...;
示例
假设有一个用户表 users
,主键或唯一键是 id
:
INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (1, 'Charlie', 35) -- 假设 id=1 冲突 ON DUPLICATE KEY UPDATE name = VALUES(name), -- 更新为新值 age = VALUES(age); -- 更新为新值
关键点:
VALUES(col)
函数引用插入时的值(如VALUES(name)
指('Charlie', 35)
中的Charlie
)。- 若插入的记录不存在冲突,则正常插入;否则执行 UPDATE。
2. 批量插入,冲突时忽略
使用 INSERT IGNORE
忽略冲突记录:
INSERT IGNORE INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (1, 'Charlie'); -- 忽略 id=1 的重复记录
3. 批量替换(删除原记录 + 插入新记录)
使用 REPLACE INTO
替换冲突的整条记录:
REPLACE INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (1, 'Charlie', 35); -- 会删除原 id=1 的记录
4. 结合条件更新(高级用法)
可在 UPDATE
子句中添加条件逻辑,例如只在新值不为 NULL
时更新:
INSERT INTO users (id, name, age) VALUES (1, 'Alice', NULL), (2, 'Bob', 25) ON DUPLICATE KEY UPDATE name = IF(VALUES(name) IS NOT NULL, VALUES(name), name), -- 仅更新非 NULL 值 age = IF(VALUES(age) IS NOT NULL, VALUES(age), age);
注意事项
- 唯一键约束:必须在表中定义唯一键(PRIMARY KEY 或 UNIQUE),否则冲突检测无效。
- 性能优化:批量插入时建议分组执行(如每 1000 条一批),避免 SQL 语句过长。
- 版本兼容性:MySQL 5.7+ 完全支持上述语法。
- 值引用:使用 VALUES(column_name) 函数可以引用原本要插入的值
根据你的具体需求,选择合适的语句即可高效处理批量插入 / 更新冲突。
MyBatis项目示例:
(1) 数据表 base_setting
CREATE TABLE `base_setting` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID', `name` varchar(60) NOT NULL DEFAULT '' COMMENT '名称', `value` varchar(600) NOT NULL DEFAULT '' COMMENT '值', `create_user_id` int NOT NULL DEFAULT '0' COMMENT '创建人', `update_user_id` int NOT NULL DEFAULT '0' COMMENT '更新人', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统设置表';
(2) BaseSettingMapper.java
package com.fuyo.application.baseadmin.mapper; import com.fuyo.application.baseadmin.entity.BaseSetting; import com.mybatisflex.core.BaseMapper; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.Map; /** * 系统设置表 映射层。 * * @author AlbertWen * @since 2025-05-13 */ @Mapper public interface BaseSettingMapper extends BaseMapper<BaseSetting> { int batchInsert(@Param("settingMap") Map<String, Object> settingMap); }
(3) BaseSettingMapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.fuyo.application.baseadmin.mapper.BaseSettingMapper"> <insert id="batchInsert"> <foreach collection="settingMap" index="index" item="value" separator=";"> INSERT INTO base_setting(name, value) VALUES (#{index}, #{value}) ON DUPLICATE KEY UPDATE value = VALUES(value) </foreach> </insert> </mapper>