Geek Answers Handbook

How to change the encoding of a dplyr :: tbl connection to utf8?

In the MySQL database, the table is encoded in utf8, but for some reason the connection is in latin1.

res <- RMySQL::dbSendQuery(con,"show variables like 'character_set_%'") dbFetch(res) Variable_name Value 1 character_set_client latin1 2 character_set_connection latin1 3 character_set_database utf8mb4 4 character_set_filesystem binary 5 character_set_results latin1 6 character_set_server latin1 7 character_set_system utf8 8 character_sets_dir /usr/share/mysql/charsets/

This page explains how to set utf8 connection character set using RMySQL .

 RMySQL::dbGetQuery(con,"show variables like 'character_set_%'") RMySQL::dbGetQuery(con,"set names utf8")

But I really prefer to use dplyr::tbl to query the database. Since the connection created by dplyr::src_mysql has the ability to send SQL commands that create tables. What is the dplyr way to set the connection setting to use utf8 encoding?

+5
source share
2 answers

I ran into the same problem that I solved as follows:

 foo_db <- src_mysql(host='0.0.0.0',user='dbuser',password='a_password', dbname='FlightTimes',port=3336) dbGetQuery(foo_db$con,'SET NAMES utf8')

I found that this was possible by looking at the foo_db structure via str(foo_db) , seeing that there is a con attribute of the MySQLConnection class, and then apply the dbGetQuery mantra.

+2
source

Edit the server settings file (located in /etc/mysql/my.cnf on the Debian system) and add the following parameters:

 collation-server = utf8_unicode_ci character-set-server = utf8 skip-character-set-client-handshake

The mysql server configuration file can also be edited using mysql-workbench.

After this change, dplyr::tbl selects the character vector encoded in utf-8.

+1
source

More articles:


All Articles