批量复制一个数据表到MySQL(类似于System.Data.SqlClient.SqlBulkCopy)
本文关键字:类似于 MySQL System Data SqlBulkCopy SqlClient 一个 复制 数据表 | 更新日期: 2023-09-27 17:53:15
我正在将我的程序从Microsoft SQL Server迁移到MySQL。一切都很好,除了大容量拷贝有一个问题。
在解决方案与MS SQL代码看起来像这样:
connection.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
bulkCopy.DestinationTableName = "testTable";
bulkCopy.WriteToServer(rawData);
现在我尝试对MySQL做类似的事情。因为我认为会有不好的性能,我不想写数据表到CSV文件,并从那里做插入与MySqlBulkLoader
类。
因为我认为会有不好的性能,我不想写数据表到CSV文件,并从那里做插入与MySqlBulkLoader类。
不要基于毫无根据的假设排除可能的解决方案。我刚刚测试了使用Transaction
内的标准MySqlDataAdapter#Update()
将100,000行从System.Data.DataTable
插入到MySQL表中的情况。运行时间一直在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);
…包括将DataTable中的100,000行转储到临时CSV文件(使用与此类似的代码)、从该文件批量加载以及随后删除该文件的时间。
类似于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行被插入了"之类的信息。
这个类在下面的库中可用
使用任何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);
只需要5秒就可以将整个DataTable复制到MySQL中,而无需首先将DataTable中的100,000行转储到一个临时CSV文件中。