导入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 '
'
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'

有什么建议吗?

推荐答案

查看导出的设置.寻找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屋！