MySQL 导入csv表时MySQL无效的UTF8字符串

MySQL Invalid UTF8 character string when importing csv table

提问by user7335295

I want to import an .csv file into MySQL Database by:

我想通过以下方式将 .csv 文件导入 MySQL 数据库：

load data local infile 'C:\Users\t_lichtenberger\Desktop\tblEnvironmentLog.csv'
into table tblenvironmentlog
character set utf8
fields terminated by ';'
lines terminated by '\n'
ignore 1 lines;

The .csv file looks like:

.csv 文件如下所示：

But I am getting the following error and I cannot explain why:

但我收到以下错误，我无法解释原因：

Error Code: 1300. Invalid utf8 character string: 'M'

Any suggestions?

有什么建议？

采纳答案by Rick James

See what the settings for the exportwere. Look for "UTF-8".

查看导出的设置。寻找“UTF-8”。

Thissuggests that "Truncated text" is caused by the data not being encoded as utf8mb4. Outside MySQL, "look for "UTF-8". (Inside, MySQL, utf8 and utf8mb4 work equally well for all European character sets, so the üshould not be a problem.

这表明“截断文本”是由未编码为 utf8mb4 的数据引起的。在 MySQL 之外，“寻找“UTF-8”。（在 MySQL 内部，utf8 和 utf8mb4 对所有欧洲字符集都同样有效，所以这ü应该不是问题。

If it was exported as "cp1252" (or any of a number of encodings), the byte for üwould not be valid for utf8mb4, leading to truncation.

如果将其导出为“cp1252”（或多种编码中的任何一种），则 for 字节ü对于 utf8mb4 将无效，从而导致截断。

If this analysis is correct, there are two solutions:

如果这个分析是正确的，有两种解决方案：

Plan A: Export as UTF-8.

计划 A：导出为UTF-8.

Plan B: Import as latin1. (You do not need to change the column/table definition, just the LOAD DATA.)

计划 B：导入为latin1. （您不需要更改列/表定义，只需更改LOAD DATA.）

回答by Ryan

Nothing else I tried worked for me, including ensuring that my .csv was saved with UTF-8 encoding.

我尝试过的任何其他方法都不适合我，包括确保我的 .csv 使用 UTF-8 编码保存。

This worked:

这有效：

When using LOAD DATA LOCAL INFILE, set CHARACTER SET latin1instead of CHARACTER SET utf8mb4as shown in https://dzone.com/articles/mysql-57-utf8mb4-and-the-load-data-infile

使用时LOAD DATA LOCAL INFILE，设置CHARACTER SET latin1而不是CHARACTER SET utf8mb4如https://dzone.com/articles/mysql-57-utf8mb4-and-the-load-data-infile 中所示

Here is a full example that worked for me:

这是一个对我有用的完整示例：

TRUNCATE homestead_daily.answers;
SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE homestead_daily.questions;
SET FOREIGN_KEY_CHECKS = 1; 
LOAD DATA LOCAL INFILE 'C:/Users/me/Desktop/questions.csv' INTO TABLE homestead_daily.questions
CHARACTER SET latin1
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(type, question, created_at, updated_at);
SELECT * FROM homestead_daily.questions;

回答by Ammar Bozorgvar

Just open the csv file in your text editor (like Nodepad++)

只需在文本编辑器中打开 csv 文件（如 Nodepad++）

and change the file Encoding to UTF-8

并将文件编码更改为 UTF-8

then import your csv file

然后导入您的 csv 文件

回答by davmos

It's complaining about 'M'but I think it's in Münchenand the actual problematic character is next one, the umlaut 'ü'.

它在抱怨，'M'但我认为它在München，而实际有问题的角色是下一个，元音变音'ü'。

One simple way to test would be to try loading a file with just the first 2 rows & see if that works. Then add the 3rd row, try again & see if that fails.

一种简单的测试方法是尝试加载一个只有前 2 行的文件，看看是否有效。然后添加第三行，再试一次，看看是否失败。

If you can't or don't want to replace these special characters in your data, then you'll need to start investigating the character sets configured in your CSV file, database, table, columns, tools etc...

如果您不能或不想替换数据中的这些特殊字符，那么您需要开始调查 CSV 文件、数据库、表、列、工具等中配置的字符集...

Are you using MySQL 5.7 or above? Then something simple to try would be to change to character set utf8mb4in your load datacommand.

您使用的是 MySQL 5.7 或更高版本吗？然后尝试一些简单的方法是character set utf8mb4在您的load data命令中更改为。

See How MySQL 5.7 Handles 'utf8mb4' and the Load Data Infilefor a similar issue.

有关类似问题，请参阅MySQL 5.7 如何处理 'utf8mb4' 和 Load Data Infile。

Also see:

另见：

import geonames allCountries.txt into MySQL 5.7 using LOAD INFILE - ERROR 1300 (HY000)

使用 LOAD INFILE - ERROR 1300 (HY000) 将 geonames allCountries.txt 导入 MySQL 5.7

Trouble with utf8 characters; what I see is not what I stored

utf8字符问题；我看到的不是我存储的

“Incorrect string value” when trying to insert UTF-8 into MySQL via JDBC?

尝试通过 JDBC 将 UTF-8 插入 MySQL 时出现“不正确的字符串值”？