C# Mysql 批量插入数据，用到MySqlConnector，并且要在连接字符串中加入 AllowLoadLocalInfile=true，还在先执行下Sql： SET GLOBAL local_infile = 1。
不然会报错：
1、{"Loading local data is disabled; this must be enabled on both the client and server sides"}
2、{"To use MySqlBulkLoader.Local=true, set AllowLoadLocalInfile=true in the connection string."}
private void btnChinese_Click(object sender, EventArgs e)
{
string path = @"D:\test\EnglishWords";
string[] files = Directory.GetFiles(path, "*.txt");
HashSet chineseWords = new HashSet();
foreach (string fn in files)
{
string content = File.ReadAllText(fn, Encoding.UTF8);
ParseChineseWords(content, "\"tran\":\"(?[^\"]+?)\"", chineseWords);
ParseChineseWords(content, "\"pCn\":\"(?[^\"]+?)\"", chineseWords);
ParseChineseWords(content, "\"tranCn\":\"(?[^\"]+?)\"", chineseWords);
//"tran":"[计]取消，撤销",
//"pCn":"取消按钮"
//"tranCn":" 取消， 撤销， 删去"
//[\u4e00-\u9fa5]+
}
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn() { ColumnName = "Name", DataType = typeof(string) });
foreach (var word in chineseWords)
{
DataRow dr = dt.NewRow();
dr["Name"] = word;
dt.Rows.Add(dr);
}
// 数据库连接字符串
using (MySqlConnection connection =
new MySqlConnection(
"server=127.0.0.1;" +
"database=englishdb;" +
"user=username;" +
"password=pwd;" +
"port=3306;" +
"charset=utf8mb4;" +
"AllowLoadLocalInfile=true"))
{
//{"Loading local data is disabled; this must be enabled on both the client and server sides"}
//{"To use MySqlBulkLoader.Local=true, set AllowLoadLocalInfile=true in the connection string."}
connection.Open();
// 2. 临时在服务器端启用本地数据加载
using (var command = new MySqlCommand("SET GLOBAL local_infile = 1;", connection))
{
command.ExecuteNonQuery();
}
// 使用MySqlBulkCopy进行批量插入
var bulkCopy = new MySqlBulkCopy(connection);
// 设置目标表名
bulkCopy.DestinationTableName = "chinese";
try
{
// 映射DataTable列到数据库表列（如果名称一致可省略）
foreach (DataColumn column in dt.Columns)
{
MySqlBulkCopyColumnMapping mapping = new MySqlBulkCopyColumnMapping(0, "Name");
bulkCopy.ColumnMappings.Add(mapping);
}
// 执行批量插入
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
Console.WriteLine($"插入失败: {ex.Message}");
}
}
}
private static void ParseChineseWords(string content, string pattern, HashSet set)
{
Regex regex = new Regex(pattern);
MatchCollection mc = regex.Matches(content);
foreach (Match match in mc)
{
string[] arr = match.Groups["value"].Value.Split(new char[] { ',', '；' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string str in arr)
{
if (Regex.IsMatch(str, "^[\u4e00-\u9fa5]+$"))
{
set.Add(str);
}
}
}
}