使用RMySQL查询MySQL数据库时字符编码异常问题求助

问题描述 #

从表t1获取字符串数据时，phpMyAdmin显示正常，但通过R语言查询得到的数据格式异常。例如原字符串Régie Réserve查询后变为Régie Réserve，执行Encoding(data)返回结果全为"unknown"。

环境信息 #

• 服务器连接排序规则：utf8mb4_unicode_ci
• 原数据库排序规则为latin1_swedish_ci，修改为utf8_general_ci后数据出现?字符

用到的R代码 #

library(RMySQL)
con <- dbConnect(MySQL(),
                 host = "host",
                 user = "user",
                 password = "password",
                 dbname = "database",
                 encoding = 'UTF-8')
query <- "SELECT * FROM t1"
data <- dbGetQuery(con, query)
dbDisconnect(con)
Encoding(data)
# [1] "unknown" "unknown" "unknown" ...

解决步骤 #

1. 统一数据库、表、字段的字符集 #

直接修改数据库默认排序规则没用，得先确认表和字段的实际字符集：

• 在phpMyAdmin执行SQL查看表结构：

  SHOW CREATE TABLE t1;
  

• 若字符串字段仍为latin1，单独修改字段字符集（操作前务必备份数据）：
  如果原数据是用latin1存储的UTF-8内容，直接转utf8mb4会乱码，要先转成二进制再转编码：

  ALTER TABLE t1 MODIFY COLUMN 目标字段名 VARBINARY(字段长度);
  ALTER TABLE t1 MODIFY COLUMN 目标字段名 VARCHAR(字段长度) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

  若数据本身是latin1编码的非UTF-8内容，直接执行第二行修改即可。

2. 修正R连接的编码设置 #

RMySQL的encoding参数控制客户端编码，要和服务器匹配：

• 尝试将连接编码改为utf8mb4：

  con <- dbConnect(MySQL(),
                   host = "host",
                   user = "user",
                   password = "password",
                   dbname = "database",
                   encoding = 'utf8mb4')
  

• 若无效，查询前强制指定连接字符集：

  dbSendQuery(con, "SET NAMES utf8mb4")
  data <- dbGetQuery(con, "SELECT * FROM t1")
  

3. 手动修复已读取数据的编码 #

如果数据已经被读成unknown编码，可强制转换为UTF-8：

data[] <- lapply(data, function(x) {
  if(is.character(x)) {
    Encoding(x) <- "UTF-8"
    x
  } else {
    x
  }
})

4. 检查MySQL全局配置 #

修改MySQL配置文件（my.cnf或my.ini），确保全局字符集统一：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

[client]
default-character-set=utf8mb4

修改后重启MySQL服务，确保新连接都用正确编码。

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