mysql bulk_MySqlBulkLoader的使用

mysql数据库：最近要写一个服务，跨库数据同步，目前数据量大约一万，以后会越来越多，考虑到扩展性，数据的插入操作就采用了MySqlBulkLoader。本文分两部分来写，第一部分写一下MySqlBulkLoader的使用，第二部分记录使用过程中出现的问题。

一、MySqlBulkLoader的使用

我们先来定义个数据表student，表结构如下：

创建一个core控制台项目，相关代码如下：

入口代码：

usingSystem;usingSystem.Collections.Generic;namespaceMySqlBulkLoaderDemo

{classProgram

{static void Main(string[] args)

{//装载30个数据

List stuList = new List();for (int i = 0; i < 30; i++)

{

stuList.Add(newStudent

{

Guid=Guid.NewGuid().ToString(),

Name= "QXH",

Age= new Random().Next(1, 30)

});

}//调用MySqlBulkLoader，往student表中插入stuList

int insertCount = MySqlBulkLoaderHelper.BulkInsert(stuList, "student");

Console.WriteLine($"成功插入{insertCount}条数据");

Console.ReadKey();

}

}

}

View Code

定义一个Student映射类：

usingSystem;usingSystem.Collections.Generic;usingSystem.Text;namespaceMySqlBulkLoaderDemo

{public classStudent

{public string Guid { get; set; }public string Name { get; set; }public int Age { get; set; }

}

}

定义一个MySqlBulkLoaderHelper类，用于存放相关方法：

usingMySql.Data.MySqlClient;usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel.DataAnnotations.Schema;usingSystem.Data;usingSystem.IO;usingSystem.Linq;usingSystem.Text;namespaceMySqlBulkLoaderDemo

{public classMySqlBulkLoaderHelper

{const string ConnectionString = "server=localhost;port=3306;user=root;password=123456;database=mysql;SslMode = none;AllowLoadLocalInfile=true";public static int BulkInsert(List entities, stringtableName)

{

DataTable dt=entities.ToDataTable();using (MySqlConnection conn = newMySqlConnection())

{

conn.ConnectionString=ConnectionString;if (conn.State !=ConnectionState.Open)

{

conn.Open();

}if(tableName.IsNullOrEmpty())

{var tableAttribute = typeof(T).GetCustomAttributes(typeof(TableAttribute), true).FirstOrDefault();if (tableAttribute != null)

tableName=((TableAttribute)tableAttribute).Name;elsetableName= typeof(T).Name;

}int insertCount = 0;string tmpPath = Path.Combine(Path.GetTempPath(), DateTime.Now.Ticks.ToString() + "_" + Guid.NewGuid().ToString() + ".tmp");string csv =dt.ToCsvStr();

File.WriteAllText(tmpPath, csv, Encoding.UTF8);using (MySqlTransaction tran =conn.BeginTransaction())

{

MySqlBulkLoader bulk= newMySqlBulkLoader(conn)

{

FieldTerminator= ",",

FieldQuotationCharacter= '"',

EscapeCharacter= '"',

LineTerminator= "\r\n",

FileName=tmpPath,

Local= true,

NumberOfLinesToSkip= 0,

TableName=tableName,

CharacterSet= "utf8"};try{

bulk.Columns.AddRange(dt.Columns.Cast().Select(colum =>colum.ColumnName).ToList());

insertCount=bulk.Load();

tran.Commit();

}catch(MySqlException ex)

{if (tran != null)

tran.Rollback();throwex;

}

}

File.Delete(tmpPath);returninsertCount;

}

}

}

}

View Code

定义一个帮助类ExtentionHelper，主要是扩展方法：

usingNewtonsoft.Json;usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Text;namespaceMySqlBulkLoaderDemo

{public static classExtentionHelper

{///

///将对象序列化成Json字符串///

/// 需要序列化的对象

///

public static string ToJson(this objectobj)

{returnJsonConvert.SerializeObject(obj);

}///

///将Json字符串转为DataTable///

/// Json字符串

///

public static DataTable ToDataTable(this stringjsonStr)

{return jsonStr == null ? null : JsonConvert.DeserializeObject(jsonStr);

}///

///将IEnumerable'T'转为对应的DataTable///

/// 数据模型

/// 数据源

/// DataTable

public static DataTable ToDataTable(this IEnumerableiEnumberable)

{returniEnumberable.ToJson().ToDataTable();

}///

///判断是否为Null或者空///

/// 对象

///

public static bool IsNullOrEmpty(this objectobj)

{if (obj == null)return true;else{string objStr =obj.ToString();return string.IsNullOrEmpty(objStr);

}

}///

///将DataTable转换为标准的CSV字符串///

/// 数据表

/// 返回标准的CSV

public static string ToCsvStr(thisDataTable dt)

{//以半角逗号(即,)作分隔符，列为空也要表达其存在。//列内容如存在半角逗号(即,)则用半角引号(即"")将该字段值包含起来。//列内容如存在半角引号(即")则应替换成半角双引号("")转义，并用半角引号(即"")将该字段值包含起来。

StringBuilder sb = newStringBuilder();

DataColumn colum;foreach (DataRow row indt.Rows)

{for (int i = 0; i < dt.Columns.Count; i++)

{

colum=dt.Columns[i];if (i != 0) sb.Append(",");if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))

{

sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");

}elsesb.Append(row[colum].ToString());

}

sb.AppendLine();

}returnsb.ToString();

}

}

}

View Code

运行结果如下：

二、MySqlBulkLoader使用过程中出现的问题

上边已经完整了介绍了MySqlBulkLoader的使用，但是在使用过程中出现了很多问题，主要集中在两方面，第一个方面是Mysql数据库不支持加载本地文件数据；第二个方面是我的数据库在阿里云服务器上，而代码在本地，换句话说数据库和项目是分别放在不同服务器上的。

1、Mysql数据库不支持加载本地文件数据

MySQLBulkLoader原理？

我们结合SQLBulkCopy来说，用过SqlServer数据库的都熟悉SQLBulkCopy，很方便，可以直接将datatable中的数据批量导入到数据库。与SQLBulkCopy不同，MySQLBulkLoader也称为LOAD DATA INFILE，他要从文件读取数据，所以我们需要将我们的数据集(如上边的List)保存到文件，然后再从文件里面读取。

而对于Mysql来说，为了数据库的安全，本地导入文件的配置没有开启，所以使用MySQLBulkLoader批量导入数据库，就需要mysql数据库支持本地导入文件。否则会出现以下错误：

The used command is not allowed with this MySQL version

解决方案：

要分两步来处理：

1、mysql数据库开启允许本地导入数据的配置，命令如下：

SET GLOBAL local_infile=1；//1表示开启，0表示关闭

查看该配置的状态命令如下：

SHOW VARIABLES LIKE '%local%';

效果如下：

2、第二步就是在项目里面的数据库连接字符串做设置

数据库连接字符串要加上”AllowLoadLocalInfile=true“

如下：

const string ConnectionString = "server=localhost;port=3306;user=root;password=123456;database=mysql;SslMode = none;AllowLoadLocalInfile=true";

总结：

经过以上两步操作即可解决The used command is not allowed with this MySQL version的错误。

2、数据库和项目是分别放在不同服务器上

数据库和项目是分别放在不同服务器上，会造成以下问题：

System.NotSupportedException

HResult=0x80131515Message=To use MySqlBulkLoader.Local=true, set AllowLoadLocalInfile=true in the connection string. See https://fl.vu/mysql-load-data

为什么呢？

因为项目中将数据集生成的文件保存在了项目所在的服务器，另一个服务器上的数据库在插入数据操作时，找不到数据集文件，导致的错误

解决方法

方法很简单，因为数据库并不在项目所在的服务器，所以MySqlBulkLoader中要设置Local = true 读取本地文件，进行导入。

具体代码如下：

总结

如果你的项目和数据库在一台服务器上，那么就不会出现该问题。