R语言连接MySQL查询UTF-8数据异常问题求助

已尝试以下操作仍无法正确获取带特殊字符的MySQL数据：

• 在工具中切换为UTF-8编码
• 修改系统区域设置
• 在SQL语句中使用CONVERT函数转换字段编码

目前仅能通过parse_character临时处理数据，但希望通过设置默认编码彻底解决问题。

R环境信息 #

R version 4.0.3 (2020-10-10)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19045)

Matrix products: default

locale:
[1] LC_COLLATE=Portuguese_Brazil.1252  LC_CTYPE=Portuguese_Brazil.1252   
[3] LC_MONETARY=Portuguese_Brazil.1252 LC_NUMERIC=C                       
[5] LC_TIME=Portuguese_Brazil.1252    
system code page: 65001

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

相关代码 #

con <- function(database){
      lapply(dbListConnections( dbDriver( drv = "MySQL")), dbDisconnect)
  cn <- dbConnect(MySQL(), host= "mysqlavisodados.servers", user = Sys.getenv("user"), password = Sys.getenv("password"),dbname = database )
    if(Sys.getlocale("LC_COLLATE")!="Portuguese_Brazil.1252"){dbSendQuery(cn, "SET CHARSET 'utf8';")}
  return(cn)
}

queryEstagio <- paste0("
SELECT 
v.vag_id as vag_id,
v.titulo,
empresa_cont,
tipo,
CONVERT(especializacao USING utf8) as Escolaridade,
periodo,
CONVERT( horario USING utf8) as horario,
qtde,
v.data_entrada
FROM conline.vag as v
left join conlina.vag_cidade as c on c.vag_id = v.vag_id
    left join conlina.cidade as cd on cd.cidade_id = c.cidade_id
        left join conlina.cidade as cidade on cidade.cidade_id = cd.cidade_id
        left join conlina.estado as e on  e.estado_id = cd.estado_id
    left join conlina.emp as emp on v.emp_id = emp.emp_id
left join conlina.faixasal_vag as sal on v.faixasal_id = sal.faixasal_id 
    where v.vag_id in (",vaga_id,") and
    (v.titulo like '%auxiliar%' or 
    v.titulo like '%motorista%' or
    v.titulo like '%cozinheiro%' or
    v.titulo like '%estagio%' or
    v.titulo like '%estagiario%'or
    v.titulo like '%ajudante%'   
    
")

vag_inicio <- dbGetQuery(conn = con("conlina"), queryEstagio)

1. 调整R的区域设置为UTF-8 #

在R启动时执行以下命令，覆盖当前的1252编码：

# Windows环境下设置UTF-8 locale
Sys.setlocale(category = "LC_ALL", locale = "Portuguese_Brazil.UTF-8")

若该locale不可用，可尝试"en_US.UTF-8"替代，设置完成后再连接数据库。

2. 连接MySQL时直接指定编码参数 #

修改dbConnect调用，添加encoding = "UTF-8"参数，确保连接层面默认使用UTF-8：

cn <- dbConnect(MySQL(), 
                host= "mysqlavisodados.servers", 
                user = Sys.getenv("user"), 
                password = Sys.getenv("password"),
                dbname = database,
                encoding = "UTF-8") # 新增编码参数

3. 优化数据库连接后的编码配置 #

替换原有的SET CHARSET语句，使用更全面的会话级编码设置：

# 连接成功后执行以下两条语句
dbSendQuery(cn, "SET NAMES 'utf8mb4';")
dbSendQuery(cn, "SET CHARACTER SET utf8mb4;")

utf8mb4支持完整Unicode字符（如emoji），若数据库支持优先使用。

4. 检查并修正数据库表字符集 #

确认MySQL中目标表和字段的字符集为utf8或utf8mb4，可通过以下SQL查询：

SHOW CREATE TABLE conline.vag;

若表字符集非UTF-8，备份数据后执行修改：

ALTER TABLE conline.vag CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

内容的提问来源于stack exchange，提问作者Vinicius Jose Caldeira