导入csv表时MySQL无效的UTF8字符串
我想通过以下方式将 .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.)
相关文章