Fetching UTF-8 text from MySQL in R returns "????"
Thanks to @chooban I found out the connection session was using latin1 instead of utf8. Here are two solutions I found:
- For RMySQL, after connecting run the query
SET NAMES utf8to change the connection character set. - For RODBC, connect using
CharSet=utf8in the DSN string. I was not able to runSET NAMESvia ODBC.
This question pointed me in the right direction.
Here's something to try at least. After you've connected, run "SHOW VARIABLES LIKE 'character_set_%'" and print out the results. If nothing else it's a useful check to see if the character set options you've specified have taken.
This worked for me. Here is a full example:
con = dbConnect(drv = MySQL(), user = user, password = password, dbname = dbname, host=host)dbSendQuery(con, "SET NAMES utf8mb4;")dbSendQuery(con, "SET CHARACTER SET utf8mb4;")dbSendQuery(con, "SET character_set_connection=utf8mb4;")dbGetQuery(con, "SELECT * FROM WHATEVER")