MySQL LOAD DATA INFILE数据太长而导致列异常 [英] MySQL LOAD DATA INFILE Data too long for column exception

问题描述

我正在使用MySQL LOAD DATA INFILE Data命令将数据批量插入表中.这是我的操作方式:

I'm using MySQL LOAD DATA INFILE Data command to bulk insert data to a table. Here's how I am doing it :

LOAD DATA INFILE 'MyFile.csv' INTO TABLE `dbname`.`tablename` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ; 

当我从我们的C#项目中运行它时，我得到了char(50)列的Data too long for column xxx异常，该列为其提供的数据小于50(但在波斯语中)，但是当我使用MySql时像SQLyog这样的客户端可以正常工作.

When I run it from our C# project I'm getting a Data too long for column xxx exception for a char(50) column which the provided data for it is less than 50 (but it is in Persian)but when I use a MySql client such as SQLyog it is working fine.

这是我运行此命令的方式:

Here's how I am running this command :

private static void RunCommand(string command,params object[] args)
        {
            if (args != null)
                command = string.Format(command, args);
            using (var conn = MySqlClientFactory.Instance.CreateConnection())
            {
                if (conn == null) return;
                conn.ConnectionString =
                    "Server=localhost;Uid=root;Pwd=123456; AutoEnlist=false;Charset=utf8;";
                conn.Open();
                using (var comm = conn.CreateCommand())
                {
                    comm.CommandText = command;
                    comm.ExecuteNonQuery();
                }
            }
        }

我想这可能是转换Unicode字符的问题，但我不知道如何使它正确运行.

I guess that it could be an issue of converting Unicode characters but I can not figure out how can I make it run correctly.

推荐答案

在LOAD DATA INFILE...语句中添加CHARACTER SET utf8参数:

LOAD DATA INFILE 'MyFile.csv'
 INTO TABLE `dbname`.`tablename`
 CHARACTER SET utf8
 FIELDS TERMINATED BY '\t' ENCLOSED BY '"'
 LINES TERMINATED BY '\r\n';

如文档中所述，它指定了哪个字符文件中使用了set:

As stated in documentation it specifies which character set is used in the file:

character_set_database系统指示的字符集 变量用于解释文件中的信息.设置名称 和character_set_client的设置不影响解释 输入.如果输入文件的内容使用字符集，则 与默认设置不同，通常最好指定 使用CHARACTER SET子句的文件字符集，即 从MySQL 5.1.17开始可用.

The character set indicated by the character_set_database system variable is used to interpret the information in the file. SET NAMES and the setting of character_set_client do not affect interpretation of input. If the contents of the input file use a character set that differs from the default, it is usually preferable to specify the character set of the file by using the CHARACTER SET clause, which is available as of MySQL 5.1.17.

如果未指定，则获取默认值，例如latin1，并且每个utf-8字节均被解释为字符.因为某些utf-8编码字符具有多个字节，所以您得到了更长的字符串.

If it is not specified then gets default value, for example latin1 and each utf-8 byte is interpreted as character. Because some utf-8 encoded characters has more than one byte you got longer strings.

这篇关于MySQL LOAD DATA INFILE数据太长而导致列异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！