导入csv表时，MySQL无效的UTF8字符串 [英] MySQL Invalid UTF8 character string when importing csv table

问题描述

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

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

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;

.csv文件如下所示:

The .csv file looks like:

但是我遇到以下错误，我无法解释原因:

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

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

有什么建议吗?

推荐答案

查看 export 的设置是什么.寻找"UTF-8".

See what the settings for the export were. Look for "UTF-8".

此 建议截断的文本"是由数据未编码为utf8mb4引起的.在MySQL外部，寻找"UTF-8".(在内部，MySQL，utf8和utf8mb4对于所有欧洲字符集都同样有效，因此ü应该不是问题.

如果将其导出为"cp1252"(或多种编码中的任何一种)，则ü的字节对于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.

如果此分析正确，则有两种解决方案:

If this analysis is correct, there are two solutions:

计划A:导出为UTF-8.

计划B:导入为latin1. (您无需更改列/表定义，只需更改LOAD DATA.)

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

这篇关于导入csv表时，MySQL无效的UTF8字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！