C#操作MySQL批量插入后获取每条记录自增ID的最优方案咨询

我在C#应用中需要向MySQL表批量插入数据，并获取每条插入记录的自增ID。目前有两种处理方案，但不确定哪种效率更高，或是存在更优实现方式。

方案一：使用MySqlDataReader结合RETURNING子句 #

string insertQuery = @"
    INSERT INTO users (Name, Age) 
    VALUES (@name1, @age1), (@name2, @age2), (@name3, @age3)
    RETURNING Id;";  // 返回所有插入记录的ID

using (MySqlCommand cmd = new MySqlCommand(insertQuery, conn))
{
    cmd.Parameters.AddWithValue("@name1", "Alice");
    cmd.Parameters.AddWithValue("@age1", 25);
    cmd.Parameters.AddWithValue("@name2", "Bob");
    cmd.Parameters.AddWithValue("@age2", 30);
    cmd.Parameters.AddWithValue("@name3", "Charlie");
    cmd.Parameters.AddWithValue("@age3", 35);

    using (MySqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            int insertedId = reader.GetInt32(0);
            Console.WriteLine("Inserted ID: " + insertedId);
        }
    }
}

方案二：插入后通过MySqlCommand.LastInsertedId获取ID #

// 先批量插入数据，再获取最后插入的ID
using (MySqlCommand cmd = new MySqlCommand(StrQuery, conn, transaction))
{
    cmd.CommandType = CommandType.Text;
    if (mysqlParameters != null)
    {
        cmd.Parameters.AddRange(mysqlParameters);
    }
    cmd.ExecuteNonQuery();
    lastInsertedId = Convert.ToInt32(cmd.LastInsertedId);
}

我的疑问 #

使用MySQL 8.0.33，批量插入记录数通常在10-100条，有时更多。请问哪种方案能保证最优性能且避免数据不一致？是否存在更优的方法来获取每条插入记录的自增ID？

原本预期两种方案都能准确获取每条记录的自增ID：方案一可在插入后直接返回ID，方案二能在不影响数据一致性的前提下获取ID。实际测试中两种方案都能运行，但不确定哪种在更大数据集下更高效、更不易出问题，也想了解是否有更好的实现方式。

方案对比与最优实践 #

1. 两种方案的核心差异 #

• 方案一（RETURNING子句）：

  • MySQL 8.0.20+正式支持RETURNING子句，批量插入时可一次性返回所有插入记录的自增ID，无需额外查询。
  • 性能优势：单轮数据库交互完成插入+ID获取，减少网络往返次数，批量越大优势越明显。
  • 数据一致性：插入和ID获取在同一个原子操作中，不会出现中间状态，完全避免数据不一致问题。
  • 注意：原代码遗漏了RETURNING Id子句，必须加上才能返回ID，否则MySqlDataReader不会有结果。

• 方案二（LastInsertedId）：

  • LastInsertedId仅返回批量插入中最后一条记录的自增ID，无法直接获取所有插入记录的ID。如果想获取所有ID，依赖自增ID连续的假设在并发场景下完全不可靠（其他会话插入数据会导致ID断档），额外查询又会增加数据库交互开销。
  • 性能劣势：即使只获取最后一个ID，单轮插入看似简单，但要获取所有ID的后续操作会大幅降低性能。
  • 数据一致性风险：依赖自增ID连续性的逻辑，在高并发场景下必然出现ID错误，导致数据不一致。

2. 最优方案选择 #

对于你的场景（批量插入10-100条记录，MySQL 8.0.33），方案一（结合RETURNING子句）是最优选择：

• 性能：单请求完成插入+所有ID返回，网络开销最小，批量越大优势越明显。
• 可靠性：原子操作保证插入和ID获取的一致性，不会出现并发导致的ID错误。
• 代码简洁性：直接通过MySqlDataReader读取所有ID，无需额外逻辑。

3. 额外优化建议 #

• 动态参数化批量插入：当插入记录数动态变化时，不要手动拼接参数名，而是通过代码生成参数化的VALUES子句，避免SQL注入同时适配任意批量大小：

public class User { public string Name { get; set; } public int Age { get; set; } }

List<User> users = new List<User> 
{ 
    new User { Name = "Alice", Age = 25 }, 
    new User { Name = "Bob", Age = 30 },
    new User { Name = "Charlie", Age = 35 }
};

var valueClauses = users.Select((u, i) => $"(@name{i}, @age{i})");
string insertQuery = $"INSERT INTO users (Name, Age) VALUES {string.Join(", ", valueClauses)} RETURNING Id;";

using (MySqlCommand cmd = new MySqlCommand(insertQuery, conn))
{
    for (int i = 0; i < users.Count; i++)
    {
        cmd.Parameters.Add($"@name{i}", MySqlDbType.VarChar).Value = users[i].Name;
        cmd.Parameters.Add($"@age{i}", MySqlDbType.Int32).Value = users[i].Age;
    }

    using (MySqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            int insertedId = reader.GetInt32(0);
            Console.WriteLine("Inserted ID: " + insertedId);
        }
    }
}

• 使用事务：如果批量插入需要原子性（全成功或全失败），在外层添加事务进一步提升数据一致性。
• 避免AddWithValue：AddWithValue可能导致MySQL参数类型推断不准确，建议使用Add方法明确指定参数类型，避免潜在性能或类型问题。

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