Mastering MySQL Duplicate Key Update with Custom MyBatis‑Plus Injector

Background Introduction

The author frequently encounters business scenarios that require a unique index constraint. When a unique key exists, the record should be updated; otherwise, it should be inserted. MySQL’s DUPLICATE KEY UPDATE natively supports this pattern, prompting the creation of a shared component to improve development efficiency.

Common Approach

To ensure data safety and smooth writes, many teams use a distributed lock. The typical steps are:

Query by the unique index; if the record exists, update it directly.

If the record does not exist, acquire a distributed lock.

Re‑query; if the record now exists, update it, otherwise insert a new record.

Release the lock

A simplified implementation looks like this:

public void duplicateKeyUpdate(NeedInsertModel model) {
    NeedInsertModel dbModel = findByUnique(model);
    /* This does not handle the case where another thread deletes the data at this moment */
    if (Objects.nonNull(dbModel)) {
        updateByUnique(model);
        return;
    }

    String lockKey = lockKey();
    RLock lock = redisClient.getLock(lockKey);
    lock.lock();
    try {
        dbModel = findByUnique(model);
        if (Objects.nonNull(dbModel)) {
            updateByUnique(model);
        } else {
            insert(model);
        }
    } finally {
        lock.unlock();
    }
}

While this achieves the desired “update‑if‑exists‑else‑insert” behavior, the boilerplate code proliferates in multi‑threaded or multi‑process environments.

New Idea: Custom SqlInjector in MyBatis‑Plus

MyBatis‑Plus generates CRUD methods via com.baomidou.mybatisplus.core.injector.AbstractMethod . Each concrete implementation corresponds to a method in BaseMapper. By creating a custom AbstractMethod, we can encapsulate the upsert logic once and reuse it.

Below is a minimal custom injector that builds an INSERT … ON DUPLICATE KEY UPDATE statement, excluding primary‑key and create_time columns:

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator;
import org.apache.ibatis.executor.keygen.KeyGenerator;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import java.util.Objects;
import java.util.function.Function;
import java.util.function.Predicate;
import java.util.stream.Collectors;

/**
 * @author Raphael
 * @since 2025/7/15 19:49
 */
public class DuplicateInserter extends AbstractMethod {
    private static final String CREATE_TIME = "create_time";
    private static final String METHOD_NAME = "duplicateUpdate";
    private static final String SEGMENT = " = VALUES(";
    private static final String FORMAT = "<script>
INSERT INTO %s %s VALUES %s ON DUPLICATE KEY UPDATE %s
</script>";

    public DuplicateInserter() {
        super(METHOD_NAME);
    }

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String insertColumns = tableInfo.getAllInsertSqlColumnMaybeIf(EMPTY);
        String insertValues = tableInfo.getAllInsertSqlPropertyMaybeIf(EMPTY);
        String insertColumnsTrim = SqlScriptUtils.convertTrim(insertColumns, LEFT_BRACKET, RIGHT_BRACKET, null, COMMA);
        String insertValuesTrim = SqlScriptUtils.convertTrim(insertValues, LEFT_BRACKET, RIGHT_BRACKET, null, COMMA);
        String keyProperty = tableInfo.getKeyProperty(), keyColumn = tableInfo.getKeyColumn();
        Predicate<TableFieldInfo> needConcat = field ->
                !Objects.equals(field.getColumn(), keyColumn) &&
                !Objects.equals(field.getColumn(), CREATE_TIME);
        Function<TableFieldInfo, String> stringFunc = field -> field.getColumn() + SEGMENT + field.getColumn() + RIGHT_BRACKET;
        String updateSet = tableInfo.getFieldList().stream()
                .filter(needConcat)
                .map(stringFunc)
                .collect(Collectors.joining(COMMA));
        KeyGenerator keyGenerator = NoKeyGenerator.INSTANCE;
        if (StringUtils.isNotBlank(tableInfo.getKeyProperty())) {
            if (tableInfo.getIdType() == IdType.AUTO) {
                keyGenerator = Jdbc3KeyGenerator.INSTANCE;
            } else if (null != tableInfo.getKeySequence()) {
                keyGenerator = TableInfoHelper.genKeyGenerator(this.methodName, tableInfo, builderAssistant);
            }
        }
        String sql = String.format(FORMAT, tableInfo.getTableName(), insertColumnsTrim, insertValuesTrim, updateSet);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, METHOD_NAME, sqlSource, keyGenerator, keyProperty, keyColumn);
    }
}

To make MyBatis‑Plus recognize this method, we extend DefaultSqlInjector and add our custom DuplicateInserter to the method list:

public class StrengthenSqlInjector extends DefaultSqlInjector {
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
        // Register our custom implementation
        methodList.add(new DuplicateInserter());
        return methodList;
    }
}

Finally, we replace the default injector via a MybatisPlusPropertiesCustomizer configuration:

@Configuration
@EnableTransactionManagement
public class MybatisPlusAutoConfiguration implements MybatisPlusPropertiesCustomizer {
    @Override
    public void customize(MybatisPlusProperties properties) {
        properties.getGlobalConfig().setSqlInjector(new StrengthenSqlInjector());
    }
}

Usage is straightforward: declare the new method in any mapper that extends BaseMapper:

@Mapper
public interface BusinessMapper extends BaseMapper<BusinessModel> {
    void duplicateUpdate(BusinessModel model);
}

Warm Tip

Because this replaces the built‑in DefaultSqlInjector, only the last MybatisPlusPropertiesCustomizer bean will take effect. If multiple customizers exist, consolidate them or ensure only one StrengthenSqlInjector is registered.