EF Core 9向带复合唯一约束的MySQL表高效插入最佳实践

你提到的场景是带有column1+column2复合唯一约束的MySQL表，需要批量插入大量数据并跳过重复项，你梳理的A、B、C方案都存在交互次数多、效率低或逻辑不合理的问题，最优方案是利用MySQL原生的INSERT ... ON DUPLICATE KEY语法，结合EF Core 9的特性实现单次批量交互，具体有两种实现方式：

方式一：使用EF Core扩展方法实现UPSERT（推荐） #

如果使用Pomelo.EntityFrameworkCore.MySql（EF Core官方推荐的MySQL provider），可以直接通过InsertRange配合OnDuplicateKeyUpdate扩展方法生成批量UPSERT语句，遇到唯一键冲突时执行空更新（即跳过重复行）：

// 假设你的实体类为ExampleTable，dbContext为你的DbContext实例
var batchData = GetBatchData(); // 替换为你的批量数据集合

await dbContext.ExampleTable
    .InsertRange(batchData)
    .OnDuplicateKeyUpdate(entry => entry
        // 冲突时设置字段为自身值，相当于不做任何修改，仅跳过重复行
        .SetProperty(e => e.Column3, e => e.Column3)
    )
    .ExecuteAsync();

这种方式的优势是无需手写SQL，通过EF Core的LINQ语法完成，同时只需要一次数据库交互，性能远高于逐条插入或先查后插的方案。

方式二：执行原生SQL实现ON DUPLICATE KEY DO NOTHING #

如果使用的MySQL版本在8.0.19及以上，可以直接使用INSERT ... ON DUPLICATE KEY DO NOTHING语法，这比空更新更直观，适合仅需插入新数据的场景：

var batchData = GetBatchData();
var sqlTemplate = @"INSERT INTO example_table (column1, column2, column3)
                    VALUES {0}
                    ON DUPLICATE KEY DO NOTHING;";

// 构建参数化的VALUES子句，避免SQL注入
var valueClauses = new List<string>();
var parameters = new List<object>();
int paramCounter = 0;

foreach (var item in batchData)
{
    valueClauses.Add($"(@p{paramCounter++}, @p{paramCounter++}, @p{paramCounter++})");
    parameters.Add(item.Column1);
    parameters.Add(item.Column2);
    parameters.Add(item.Column3);
}

var finalSql = string.Format(sqlTemplate, string.Join(", ", valueClauses));
await dbContext.Database.ExecuteSqlRawAsync(finalSql, parameters.ToArray());

注意事项 #

• 确保使用的EF Core MySQL provider版本与EF Core 9兼容（比如Pomelo.EntityFrameworkCore.MySql 9.x）
• 当批量数据量极大时（比如上万条），建议分批次插入（每1000条为一批），避免单条SQL语句过长导致性能下降
• 必须保证unique_column1_column2复合唯一约束已正确创建，否则无法检测到重复行

内容的提问来源于stack exchange，提问作者Saggy