在Windows上的RMySQL编码问题 - 西班牙字符ñ

Question

当我在Windows上使用RMySQL::dbWriteTable函数将表写入MySQL时，会出现涉及字符[ñ]的错误信息。

简化示例如下：

    table <- data.frame(a=seq(1:3), b=c("És", "España", "Compañía"))
    table

 a        b
1 1       És
2 2   España
3 3 Compañía


db <- dbConnect(MySQL(), user = "####", password = "####", dbname ="test", host= "localhost")

RMySQL::dbWriteTable(db, name="test1", table, overwrite=T, append=F )

Error in .local(conn, statement, ...) : 
  could not run statement: Invalid utf8 character string: 'Espa'

如您所见，重音符号("És")没有问题，但是ñ字符("España")存在问题。

另一方面，MySQL没有问题，因为这个查询可以正常工作:

INSERT INTO test.test1 (a,b)
values (1, "España");

在写表格之前，我已经尝试过以下方法：

1. 对于所有表格都使用“UTF-8”编码。

2. 对于所有表格都使用“iconv(x, “UTF-8”, “UTF-8”)”。

3. 发送预查询：“dbSendQuery(db, “SET NAMES UTF8;”)”

4. 将MySQL表格字符集更改为：“utf-8-general, latin-1, latin-1-spanish...”

*尝试使用“Latin-1”编码，但结果仍然失败。

我已经寻找这个问题的答案很长时间了，但没有得到帮助。
请帮忙！

版本信息：

MySQL 5.7.17

R版本3.3.0

Sys.getlocale()

[1] "LC_COLLATE=English_United States.1252;LC_CTYPE=English_United States.1252;LC_MONETARY=English_United States.1252;LC_NUMERIC=C;LC_TIME=C"

PS：在Linux环境下运行正常，但目前的项目中却被Windows困扰着:(

- AlexSB

---

在说“这是Windows的问题”之前，请想象一下必须处理来自多个语言环境的数据。当你必须处理多个代码页时，无法在LC_COLLATE中硬编码只有一个语言环境。 - Panagiotis Kanavos

我成功地将问题隔离到了GUI上。RStudio 1.0.136和Rgui会默默地转换Unicode文字。无论你输入什么都被视为非Unicode字符串。在我的机器上（希腊语环境），ñ被转换为n。另一方面，Visual Studio的R工具没有这个问题，字符得以保留。 - Panagiotis Kanavos

我找到了一个中间解决方案。由于不仅西班牙字符妨碍了上传过程（其他特殊字符，如长破折号[- -]和从Excel文件导入的奇怪空格），我对所有列应用了make.names函数，并清除了所有这些特殊字符。我知道这不是最好的解决方案，但它允许我交叉表格，因为它们都是以相同的方式导入的（使用make.names）。 - AlexSB

为什么要这样做，而不是将脚本保存和加载为Unicode？Windows没有任何问题。这只是General中的一个设置。长破折号或Excel文件也没有问题，前提是它们确实是Excel文件，而不仅仅是带有虚假扩展名的CSV文件。实际上，当您加载文件时，可以指定要使用的编码。如果您在Linux上工作，并且必须同时加载德语、俄语和乌克兰ASCII文件（真实故事，乌克兰啤酒灌装厂），那么您会怎么做？您不会为每个文件指定编码吗？ - Panagiotis Kanavos

请勿使用希腊语本地化；它不包括波浪线-n。 - Rick James

Answer 1

最终看起来似乎是连接的编码设置问题。默认情况下，我的连接设置为utf-8，但我的本地编码设置为latin1。因此，我的最终解决方案是：

con <- dbConnect(MySQL(), user=user, password=password,dbname=dbname, host=host, port=port)
# With the next line I try to get the right encoding (it works for Spanish keyboards)
encoding <- if(grepl(pattern = 'utf8|utf-8',x = Sys.getlocale(),ignore.case = T)) 'utf8' else 'latin1'
dbGetQuery(con,paste("SET names",encoding))
dbGetQuery(con,paste0("SET SESSION character_set_server=",encoding))
dbGetQuery(con,paste0("SET SESSION character_set_database=",encoding))
dbWriteTable( con, value = dfr, name = table, append = TRUE, row.names = FALSE )
dbDisconnect(con)

- Kratos

Answer 2

在Windows中，这对我有效：

write.csv(table, file = "tmp.csv", fileEncoding = "utf8", quote = FALSE, row.names = FALSE)

db <- dbConnect(MySQL(), user = "####", password = "####", dbname ="test", host= "localhost")

dbWriteTable( db, value = "tmp.csv", name = "test1", append = TRUE, row.names = FALSE, sep = ",", quote='\"', eol="\r\n")

- Kratos

Answer 3

函数dbConnect()有一个名为encoding的参数，可以帮助您轻松设置连接编码方式。

dbConnect(MySQL(), user=user, password=password,dbname=dbname, host=host, port=port, encoding="latin1")

这使我能够在我的表中插入“ñ”字符，并且还可以向名称中包含“ñ”的列中插入数据。例如，我可以向名为“año”的列中插入数据。

- aicesanchez

Answer 4

我在处理一个大约有60列和150万行的数据表时遇到了这个问题；其中有许多计算值和协调和更正的日期和时间，因此我不想重新格式化任何我不必重新格式化的内容。由于utf-8问题只出现在字符字段中，所以我使用了一个笨拙但快速的方法：

1）将dbWriteTable语句中的字段列表复制到文字处理器或文本编辑器中

2）在您的副本上，仅保留具有VARCHAR和TEXT描述的字段

3）将这些字段简化为字段名称

4）使用paste0编写一个字符向量的语句，以确保所有字段都是字符字段：

dt$x <- as.character(dt$x)

5) 然后再次使用 paste0，编写一个字符向量的语句，将编码设置为UTF-8

Encoding(dt$x) <- "UTF-8"

在Encoding组之前运行as.character组。

这绝对是一个临时解决方案，还有更优雅的方法，但如果你只需要偶尔这样做（就像我一样），那么它有三个优点：

1）它只改变需要改变的内容（当数据表中已经有大量工作，您不想在重新格式化时冒险时，这一点非常重要），

2）它不需要大量空间和中间阶段的读/写操作，

3）它编写快速，并以至少我正在处理的数据表大小的可接受速度运行。

不够优雅，但它可以让您迅速解决此特定问题。

- JohnBarnes