MySqlBulkLoader 中文乱码

亦凉 2021-11-05 10:54 476阅读 0赞

[MySqlBulkLoader 中文乱码][MySqlBulkLoader]

原文: [MySqlBulkLoader 中文乱码][MySqlBulkLoader 1]

MySQL驱动：[MySqlConnector][]  
GitHub地址：[https://github.com/mysql-net/MySqlConnector.git][https_github.com_mysql-net_MySqlConnector.git]  
文档地址：[https://mysql-net.github.io/MySqlConnector/][https_mysql-net.github.io_MySqlConnector]

代码插入之后发现出现中文乱码现象。

## 原来的代码逻辑简略版 ##

实现逻辑梳理，将数组写入csv，通过MySqlBulkLoader批量插入

public async Task BulkLoadLocalCsvFile<T>(IEnumerable<T> dataSource, string tableName)
            {
                using (var connection = new MySqlConnection(GetLocalConnectionString()))
                {
                    await connection.OpenAsync();
                    var path = Path.GetTempFileName();
                    MySqlBulkLoader bl = new MySqlBulkLoader(connection);
                    bl.FileName = path;
                    bl.TableName = tableName;
                    bl.FieldTerminator = ",";
                    bl.FieldQuotationCharacter = '"';
                    bl.FieldQuotationOptional = true;
                    bl.Local = true;
                    using (var writer = new StreamWriter(path))
                    {
                        var configuration = new Configuration
                        {
                            HasHeaderRecord = false,
                        };
                        using (var csv = new CsvWriter(writer, configuration))
                        {
                            csv.WriteRecords(dataSource);
                            var writerConfiguration = csv.Context.WriterConfiguration;
                            var map = writerConfiguration.Maps.Find<T>();
                            foreach (var memberMap in map.MemberMaps)
                            {
                                var memberName = memberMap.Data.Member.Name;
                                loader.Columns.Add(memberName);
                            }
                        }
                    }
                    int rowCount = await bl.LoadAsync();
                    File.Delete(path);
                }
            }

## 分析乱码原因 ##

*  需要指定csv文件的字符集
 *  需要指定StreamWriter字符集
 *  需要指定MySqlBulkLoader字符集

## 指定csv文件的字符集 ##

写CSV文件借助的是开源包CsvHelper。  
CsvHelper

*  GitHub地址：[https://github.com/JoshClose/CsvHelper][https_github.com_JoshClose_CsvHelper]
 *  nuget地址：[https://www.nuget.org/packages/CsvHelper/12.1.2][https_www.nuget.org_packages_CsvHelper_12.1.2]

指定 `CsvHelper.Configuration.Configuration`字符集

var configuration = new Configuration
    {
         Encoding = Encoding.UTF8
     };

没能解决。

## 指定StreamWriter字符集 ##

var writer = new StreamWriter(path, true, Encoding.UTF8)

不行。

## 指定MySqlBulkLoader字符集 ##

bl.CharacterSet = "UTF8";

乱码解决。

posted on 2019-06-29 11:26 [NET未来之路][NET] 阅读( ...) 评论( ...) [编辑][Link 1] 收藏

转载于:https://www.cnblogs.com/lonelyxmas/p/11106073.html

[MySqlBulkLoader]: https://www.cnblogs.com/lonelyxmas/p/11106073.html
[MySqlBulkLoader 1]: https://www.cnblogs.com/AlienXu/p/11011394.html
[MySqlConnector]: https://www.nuget.org/packages/MySqlConnector/0.56.0/
[https_github.com_mysql-net_MySqlConnector.git]: https://github.com/mysql-net/MySqlConnector.git
[https_mysql-net.github.io_MySqlConnector]: https://mysql-net.github.io/MySqlConnector/
[https_github.com_JoshClose_CsvHelper]: https://github.com/JoshClose/CsvHelper
[https_www.nuget.org_packages_CsvHelper_12.1.2]: https://www.nuget.org/packages/CsvHelper/12.1.2
[NET]: https://www.cnblogs.com/lonelyxmas/
[Link 1]: https://i.cnblogs.com/EditPosts.aspx?postid=11106073