c# - MySQL LOAD DATA INFILE 列异常的数据太长

Question

我正在使用 MySQL LOAD DATA INFILE Data 命令将数据批量插入表中。这是我的做法:

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

当我从我们的 C# 项目运行它时，我得到一个 Data too long for column xxx char(50) 列的异常，它提供的数据小于 50(但它是波斯语)但是当我使用 MySql 客户端(例如 SQLyog)时，它工作正常。

以下是我运行此命令的方式:

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 字符的问题，但我不知道如何才能使其正确运行。

Answer 1

将CHARACTER SET utf8参数添加到LOAD DATA INFILE...语句:

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

如 documentation 中所述它指定文件中使用的字符集:

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 编码的字符有一个以上的字节，所以你得到更长的字符串。