使用R的DBI连接MariaDB时如何修正字符编码显示错误？

数据库某表字段存储值为「CIÓN」（字母Ó带重音），但通过R的DBI连接获取数据时，字符显示异常为「CI\xe0N」。尝试设置latin1、windows-1252、UTF-8等编码均未解决问题。

尝试过的代码 #

library(DBI)
con <- dbConnect(odbc::odbc(), Driver = "{MariaDB ODBC 3.1 Driver}",
                 Server = "{server}", database = "database", UID = "user",
                 PWD = rstudioapi::askForPassword("password"),
                 Port = "port"
                 , encoding = "windows-1252")
                  # , encoding = "latin1") # 也尝试过latin1                          
               # , encoding = "UTF-8") # 也尝试过UTF-8   

sql_r <- "select field from table"
res <- dbGetQuery(con, sql_r)

不同编码设置下的显示结果 #

• LATIN1：显示为 IàN
• LATIN2：显示为 IŕN
• WINDOWS-1252：显示为 IàN
• UTF-8：显示为 I\xe0N（默认显示）

补充信息 #

R会话locale信息 #

sessionInfo()
locale:
[1] LC_COLLATE=Spanish_Mexico.utf8  LC_CTYPE=Spanish_Mexico.utf8    LC_MONETARY=Spanish_Mexico.utf8
[4] LC_NUMERIC=C                    LC_TIME=Spanish_Mexico.utf8 

数据库表排序规则 #

数据库表的排序规则为 utf8mb3_general_ci

解决建议 #

1. 在ODBC连接中指定字符集
   由于数据库使用utf8mb3字符集（与UTF-8兼容），在连接字符串中添加CharacterSet=utf8参数，强制驱动以UTF-8编码传输数据：

   con <- dbConnect(odbc::odbc(), Driver = "{MariaDB ODBC 3.1 Driver}",
                    Server = "{server}", database = "database", UID = "user",
                    PWD = rstudioapi::askForPassword("password"),
                    Port = "port",
                    CharacterSet = "utf8",
                    encoding = "UTF-8")
   

2. 手动转换编码修复显示
   观察到Ó被解析为à（十六进制0xE0），尝试用iconv函数手动纠正编码：

   res$field <- iconv(res$field, from = "ISO-8859-1", to = "UTF-8", sub = "byte")
   

   若无效，可替换from参数为"Windows-1252"再次测试。

3. 确认数据库字段字符集
   检查对应字段的字符集是否确实为utf8mb3，避免存储阶段就出现编码错误，可通过SQL查询确认：

   SHOW FULL COLUMNS FROM `table` LIKE 'field';
   

内容的提问来源于stack exchange，提问作者hfa