C#中MySqlConnector批量插入CSV时数据读取错误问题咨询

使用MySqlConnector实现C#批量插入大量数据时，遭遇数据读取错误：数据包含UTF特殊字符、换行符\r\n，直接生成分号分隔的文本文件再通过MySqlBulkLoader导入时，出现字段错位、换行被识别为新记录、特殊字符乱码等问题。

现有代码 #

using (MySqlConnection mySqlConnection2 = new MySqlConnection(sqlconnect))
{
    using (StreamWriter streamWriter = new StreamWriter(path, true, Encoding.GetEncoding("windows-1251")))
    {                        
        foreach (Mail_Data data in List)
        {
            streamWriter.WriteLine($"{data.threadID};{data.mailID};{data.UserID};{data.UserName};{data.mailTime};{data.body}");
        }
    }

    var bl = new MySqlBulkLoader(mySqlConnection2);
    bl.TableName = "Mail";
    bl.FieldTerminator = ";";
    bl.LineTerminator = "\r\n";
    bl.FileName = path;
    bl.NumberOfLinesToSkip = 0;
    var inserted = bl.Load();
}

数据示例 #

207;207;66;Kondommm;3/15/2022 3:02:26 PM;selam :D
207;254;82;OkanOzcelik;3/15/2022 3:03:19 PM;asssss 
207;4200;82;OkanOzcelik;3/15/2022 7:53:03 PM;discord
207;7039;66;Kondommm;3/16/2022 9:14:18 AM;sirket bilgisayar?ndan oynuyorum discord acam?yorum burda . bisey dersen mail at 
207;7366;82;OkanOzcelik;3/16/2022 10:40:52 AM;Tamamd?r abi
207;8937;82;OkanOzcelik;3/16/2022 4:22:54 PM;Koy ad?n? MAD ile ilgili bir sey yapsana abi
207;38036;66;Kondommm;3/22/2022 2:57:34 PM;discord sifremi unuttum sacma bi sekilde hangi maille kaydoldugumuda unuttum. D: 
207;39142;82;OkanOzcelik;3/22/2022 5:59:41 PM;Abi nas?l basard?n bunu hahah
207;39767;82;OkanOzcelik;3/22/2022 7:16:36 PM;ne zaman Page be abi
207;42204;82;OkanOzcelik;3/23/2022 10:35:02 AM;:D
207;43615;66;Kondommm;3/23/2022 3:37:30 PM;2 bar var :D
207;68426;82;OkanOzcelik;3/28/2022 9:30:26 PM;Nas?l gidiyor abi
207;71877;66;Kondommm;3/29/2022 7:21:22 PM;isler yogun bi t?k 2 gundur bakamad?m bile :(
207;72166;82;OkanOzcelik;3/29/2022 8:30:36 PM;2 gun bakmad?n hala gecememisim seni :D
473;477;96;reCONNECT-;3/15/2022 3:08:00 PM;hallo nachbar :)
473;563;157;wolkrus;3/15/2022 3:10:42 PM;hey wie gehts
473;577;96;reCONNECT-;3/15/2022 3:11:16 PM;alles gut
hoffe dir gehts auch gut :)
473;583;157;wolkrus;3/15/2022 3:11:27 PM;immer

473;8964;96;reCONNECT-;3/16/2022 4:28:40 PM;?
473;8980;157;wolkrus;3/16/2022 4:31:56 PM;Mision
473;8985;96;reCONNECT-;3/16/2022 4:32:27 PM;ach so;

sag bei so was aber bitte vorher Bescheid in Zukunft ;)
523;528;165;Silenced_13d;3/15/2022 3:09:32 PM;hey
523;535;99;Draft911;3/15/2022 3:09:50 PM;hi man
523;656;99;Draft911;3/15/2022 3:14:07 PM;where you landed
523;668;165;Silenced_13d;3/15/2022 3:14:37 PM;105156 just south of porto seat

523;1523;99;Draft911;3/15/2022 3:59:24 PM;I don't know how fast are you have rank.7 ^^

错误表现 #

数据库中出现字段错位：邮件正文内的换行被识别为新行，导致后续字段被当作新记录的字段；正文内的分号被当作字段分隔符，破坏字段结构；部分UTF特殊字符显示乱码。

1. 改用标准带引号的CSV格式 #

直接拼接分号文本无法处理字段内的分隔符和换行，必须使用标准CSV规则：用双引号包裹每个字段，字段内的双引号替换为两个双引号""，同时统一编码为UTF-8。

修改写入文件代码：

using (StreamWriter streamWriter = new StreamWriter(path, true, Encoding.UTF8))
{                        
    foreach (Mail_Data data in List)
    {
        // 字段转义函数：包裹引号并转义内部双引号
        string EscapeField(string value) => $"\"{value?.Replace("\"", "\"\"")}\"";
        
        var fields = new[]
        {
            EscapeField(data.threadID.ToString()),
            EscapeField(data.mailID.ToString()),
            EscapeField(data.UserID.ToString()),
            EscapeField(data.UserName),
            EscapeField(data.mailTime.ToString()),
            EscapeField(data.body)
        };
        
        streamWriter.WriteLine(string.Join(";", fields));
    }
}

同步配置MySqlBulkLoader识别引号：

var bl = new MySqlBulkLoader(mySqlConnection2);
bl.TableName = "Mail";
bl.FieldTerminator = ";";
bl.LineTerminator = "\r\n";
bl.FileName = path;
bl.NumberOfLinesToSkip = 0;
bl.FieldQuotationCharacter = '"'; // 启用字段引号解析
bl.CharacterSet = "utf8mb4"; // 指定支持全UTF-8的字符集
var inserted = bl.Load();

2. 跳过中间文件，直接内存批量插入 #

如果不需要保留CSV文件，直接使用MySqlBulkCopy从内存数据批量插入，彻底避免文件读写的编码和格式问题，效率更高：

using (MySqlConnection mySqlConnection2 = new MySqlConnection(sqlconnect))
{
    await mySqlConnection2.OpenAsync();
    
    using (var bulkCopy = new MySqlBulkCopy(mySqlConnection2))
    {
        bulkCopy.DestinationTableName = "Mail";
        
        // 映射实体属性与数据库列名（属性名和列名一致可省略）
        bulkCopy.ColumnMappings.Add(nameof(Mail_Data.threadID), "threadID");
        bulkCopy.ColumnMappings.Add(nameof(Mail_Data.mailID), "mailID");
        bulkCopy.ColumnMappings.Add(nameof(Mail_Data.UserID), "UserID");
        bulkCopy.ColumnMappings.Add(nameof(Mail_Data.UserName), "UserName");
        bulkCopy.ColumnMappings.Add(nameof(Mail_Data.mailTime), "mailTime");
        bulkCopy.ColumnMappings.Add(nameof(Mail_Data.body), "body");
        
        // 直接从List批量写入数据库
        await bulkCopy.WriteToServerAsync(List);
    }
}

3. 统一字符集配置 #

• 数据库表字符集设置为utf8mb4，支持所有UTF-8字符（包括emoji、特殊符号）
• 代码中文件读写统一使用Encoding.UTF8，避免windows-1251的编码限制
• MySqlBulkLoader指定CharacterSet = "utf8mb4"，确保读取文件时编码匹配

内容的提问来源于stack exchange，提问作者Gaben