前言
假如有几万、几十万条数据需要写入到数据库Mysql中,那么一条一条循环写入会很耗时,如果一两百条批量写入也会比一条条的快一点,那么有没有一次性插入一万或者更多的方法?当然有使用MySqlBulkLoader可以解决这个耗时的问题。
一、MySqlBulkLoader
MySqlBulkLoader主要的实现方式:将需要插入的数据转成DataTable,DataTable转成一个CSV文件,将CSV文件使用批量导入的形式导入到数据库里面去。
注意:
1)数据库连接地址需要添加配置AllowLoadLocalInfile=true,允许本地文件导入;
Data Source = 数据库地址; Port = 端口; Initial Catalog = 数据库名; User Id = 用户名; Password = 密码;AllowLoadLocalInfile=true;
2)插入的时候会返回插入行数,但是检查所有的数据都正确,也没有报异常,却返回了插入数量为0,可以检查表是否有唯一索引,插入的数据是否违反了唯一索引。
二、代码实现
需要引用sing MySql.Data.MySqlClient,其中的table的名字要使用数据库的名字,比如数据库表名字叫做user_tb,那么table.TableName=“user_tb”。
/// <summary>
///大批量数据插入,返回成功插入行数
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="table">数据表</param>
/// <returns>返回成功插入行数</returns>
public static int BulkInsert(DataTable table)
{
if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");
if (table.Rows.Count == 0) return 0;
int insertCount = 0;
string tmpPath = AppDomain.CurrentDomain.BaseDirectory + "\\" +Guid.NewGuid().ToString().Replace("-","")+".csv" ;// Path.GetTempFileName();
string csv = DataTableToCsv(table);
File.WriteAllText(tmpPath, csv);
try
{
string connectionStr = "数据库连接串";
MySqlConnection objMySqlConnection = new MySqlConnection(connectionStr);
MySqlBulkLoader bulk = new MySqlBulkLoader(objMySqlConnection)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tmpPath,
NumberOfLinesToSkip = 0,
TableName = table.TableName,
ConflictOption= MySqlBulkLoaderConflictOption.Replace,
Local=true
};
bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
insertCount = bulk.Load();
Console.WriteLine("insertCount:" + insertCount);
}
catch
{
throw;
}
finally
{
File.Delete(tmpPath);
}
return insertCount;
}