不灭的火

在 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);

注意事项

1. 唯一键约束：必须在表中定义唯一键（PRIMARY KEY 或 UNIQUE），否则冲突检测无效。
2. 性能优化：批量插入时建议分组执行（如每 1000 条一批），避免 SQL 语句过长。
3. 版本兼容性：MySQL 5.7+ 完全支持上述语法。
4. 值引用：使用 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>