Search code examples
mysqlrutf-8rmysql

UTF8 encoding using RMySQL

I am trying to query data from a mysql database, which contains some strings, of course. For the connection and data retrieval I am using RMySQL in R, which works fine. Apart from one thing: the strings I am retrieving seem not to be in utf8. But I need this, because I have some german "Umlaute" in these strings. When I ask teh databse, which are its encoding by 

dbGetQuery(db, "SHOW VARIABLES LIKE 'character_set_%';")

I get the desired answer:

             Variable_name           Value
1   character_set_client             utf8
2   character_set_connection         utf8
3   character_set_database           utf8
4   character_set_filesystem         binary
5    character_set_results           utf8
6     character_set_server           utf8
7     character_set_system           utf8
8       character_sets_dir C:\\Program Files\\MySQL\\MySQL Server 5.7\\share\\charsets\\

But e.g. I receive 

Andreas Wünsche

instead of 

Andreas Wünsche

Hope that somebody knows how to deal with it. If additonal information is needed, just ask. I can provide it.

Solution

  • I find something a bit tricky but works for me :

    you have to manually define the col of your data frame to utf-8 like this :

    x <- "Wünsche"
Encoding(x) <- "UTF-8"
x
[1] "Wünsche"

    Think you have to do this to all your strings vector

    EDIT :

    Take a look here
    seems to fix the same problem by adding 'set character set "utf8"'inside the dbSendQuery()