Mysql的批量导入类 MySqlBulkLoader

最新推荐文章于 2024-09-01 23:12:24 发布

转载最新推荐文章于 2024-09-01 23:12:24 发布 · 1k 阅读

文章标签：

#数据库

本文介绍了在使用MySQL的MySqlBulkLoader工具进行大批量数据导入时遇到的问题及解决办法，特别是如何正确处理NULL值的插入。文章指出，在使用特定转义字符N时并未成功插入NULL值，而正确的做法应该是直接使用NULL关键字。

在mssqlserver 中对应的SqlBuckCopy类，进行批量数据插入。

在mysql 中，官方提供了MySqlBulkLoader 平行的工具；

不过里面有坑，具体坑是插入空值列 NULL的。

For input, if the FIELDS ESCAPED BY character is not empty, occurrences of that character are stripped and the following character is taken literally as part of a field value. Some two-character sequences that are exceptions, where the first character is the escape character. These sequences are shown in the following table (using \ for the escape character). The rules for NULLhandling are described later in this section.

Character	Escape Sequence
`\0`	An ASCII NUL (`X'00'`) character
`\b`	A backspace character
`\n`	A newline (linefeed) character
`\r`	A carriage return character
`\t`	A tab character.
`\Z`	ASCII 26 (Control+Z)
`\N`	NULL

在 MySql.Data.dll 提供的驱动中，使用 \N 字符并未将NULL列插入。应该使用关键词 NULL 进行空值的代表。

 //tran = conn.BeginTransaction();  
                    MySqlBulkLoader bulk = new MySqlBulkLoader(conn)  
                    {  
                        FieldTerminator = ",",  
                        FieldQuotationCharacter = '"',  
                        EscapeCharacter = '"',  
                        LineTerminator = "\r\n",  
                        FileName = tmpPath,  
                        NumberOfLinesToSkip = 0,  
                        TableName = table.TableName,  
                    };  
                    //bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToArray());  
                    insertCount = bulk.Load();

具体资料：

https://dev.mysql.com/doc/connector-net/en/connector-net-programming-bulk-loader.html

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

http://blog.youkuaiyun.com/zhou2s_101216/article/details/50875211

https://dev.mysql.com/doc/refman/5.7/en/load-data.html