批量将 DataTable 复制到 MySQL 中（类似于 System.Data.SqlClient.SqlBulkCopy）

Question

我正在将我的程序从Microsoft SQL Server迁移到MySQL。除了批量复制的一个问题外，一切都很顺利。

在使用MS SQL解决方案时，代码如下：

connection.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
bulkCopy.DestinationTableName = "testTable";
bulkCopy.WriteToServer(rawData);

现在我尝试为MySQL做类似的事情。因为我认为性能会很差，所以我不想将DataTable写入CSV文件，然后使用MySqlBulkLoader类进行插入。

非常感谢任何帮助。

- Michael Schegg

---

可能重复的问题：Can a C# program read a text file into memory and then pass that object to a method that requires a filename? - Gord Thompson

还有另一个答案在这里（https://dev59.com/nlTTa4cB1Zd3GeqPsXPw#23537155）。关于性能，您是否进行了任何测试以查看将数据转储到磁盘，然后使用`MySqlBulkLoader`是否真的会成为问题，或者实际上比不使用临时文件的其他“自定义”解决方案更糟糕？ - Gord Thompson

https://github.com/borisdj/EFCore.BulkExtensions 已经具备了这个功能。 - borisdj

Answer 1

因为我认为表现不佳，所以我不想将DataTable写入CSV文件中，然后使用MySqlBulkLoader类进行插入。请不要基于没有根据的假设排除可能的解决方案。 我刚刚测试了从System.Data.DataTable插入100,000行到MySQL表中，并在Transaction内使用标准的MySqlDataAdapter#Update()，它一直需要大约30秒才能运行：

using (MySqlTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = conn;
        cmd.Transaction = tran;
        cmd.CommandText = "SELECT * FROM testtable";
        using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
        {
            da.UpdateBatchSize = 1000;
            using (MySqlCommandBuilder cb = new MySqlCommandBuilder(da))
            {
                da.Update(rawData);
                tran.Commit();
            }
        }
    }
}

(我尝试了几个不同的UpdateBatchSize值，但它们似乎对经过的时间没有显著影响。)

相比之下，使用MySqlBulkLoader的以下代码只需要5或6秒钟运行...

string tempCsvFileSpec = @"C:\Users\Gord\Desktop\dump.csv";
using (StreamWriter writer = new StreamWriter(tempCsvFileSpec))
{
    Rfc4180Writer.WriteDataTable(rawData, writer, false);
}
var msbl = new MySqlBulkLoader(conn);
msbl.TableName = "testtable";
msbl.FileName = tempCsvFileSpec;
msbl.FieldTerminator = ",";
msbl.FieldQuotationCharacter = '"';
msbl.Load();
System.IO.File.Delete(tempCsvFileSpec);

... 包括将100,000行数据从DataTable导出到临时CSV文件的时间（使用类似这个的代码），从该文件进行批量加载，然后删除该文件。

- Gord Thompson

---

非常感谢。在你的第二条评论之后，我自己找到了答案 :-) - Michael Schegg

使用MySqlBulkLoader的代码对我来说不起作用，除非我添加msbl.LineTerminator = "\r\n"。 - Newton Zou

Answer 2

类似于 SqlBulkCopy，我们有 Mysql 的 MySqlBulkCopy。以下是如何使用它的示例。

public async Task<bool> MySqlBulCopyAsync(DataTable dataTable)
    {
        try
        {
            bool result = true;
            using (var connection = new MySqlConnector.MySqlConnection(_connString + ";AllowLoadLocalInfile=True"))
            {
                await connection.OpenAsync();
                var bulkCopy = new MySqlBulkCopy(connection);
                bulkCopy.DestinationTableName = "yourtable";
                // the column mapping is required if you have a identity column in the table
                bulkCopy.ColumnMappings.AddRange(GetMySqlColumnMapping(dataTable));
                await bulkCopy.WriteToServerAsync(dataTable);
                return result;
            }
        }
        catch (Exception ex)
        {
            throw;
        }
    }
    private List<MySqlBulkCopyColumnMapping> GetMySqlColumnMapping(DataTable dataTable)
    {
        List<MySqlBulkCopyColumnMapping> colMappings = new List<MySqlBulkCopyColumnMapping>();
        int i = 0;
        foreach (DataColumn col in dataTable.Columns)
        {
            colMappings.Add(new MySqlBulkCopyColumnMapping(i, col.ColumnName));
            i++;
        }
        return colMappings;
    }

如果您的表中没有身份列，则可以忽略列映射。 如果有身份列，则必须使用列映射，否则它不会插入任何记录到表中。 它只会显示消息“复制了x行，但插入了0行”。

此类可在以下库中使用 Assembly MySqlConnector，Version=1.0.0.0

- sushil suthar

Answer 3

通过使用任何一个BulkOperation NuGet包，您可以轻松地完成此操作。

以下是使用https://www.nuget.org/packages/Z.BulkOperations/2.14.3/包的示例。

MySqlConnection conn = DbConnection.OpenConnection();
DataTable dt = new DataTable("testtable");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM testtable", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.Fill(dt);

使用

代替

......
da.UpdateBatchSize = 1000;
......
da.Update(dt)

只是以下两行

var bulk = new BulkOperation(conn);
bulk.BulkInsert(dt);

将整个DataTable复制到MySQL只需要5秒钟，无需先将100,000行从DataTable转储到临时CSV文件中。

- alex

---

请注意，所提到的库不是免费的。 - Randy Burden