UTF8 encoding using RMySQL

Question

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.

Have you tried to change the default encoding of R ? If you are using R studio go : Tool -> Global Options -> Code -> Saving and put UTF-8 — Christophe D.
– Christophe D., Commented Jul 13, 2016 at 9:17
Ok. I have tried to use some iconv function to set the WÃ¼nsche to the proper format but didn't find any solution... i will continue — Christophe D.
– Christophe D., Commented Jul 13, 2016 at 11:40

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

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()

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 13, 2016 at 12:10

Christophe D.

1,08911 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

stephan mc Over a year ago

Thanks, that helps. But I would like to know, how this happens, that the encoding is set to "unkown".

Christophe D. Over a year ago

may be the SQL query dont apply the format to the R object

stephan mc Over a year ago

The character set approach did not work for me. I have to keep your encoding workaround. Thanks, again.

baitmbarek · Accepted Answer · 2020-01-19 17:53:17Z

2

I took this answer from: https://stat.ethz.ch/pipermail/r-sig-db/2012q1/001141.html Before dbSendQuery you have to place dbGetQuery(mydb, "SET NAMES 'utf8'")

mydb <-  dbConnect(MySQL(), user = db_user, password = db_password,
               dbname = db_name, host = db_host, port = db_port)

s=dbGetQuery(mydb, "SET NAMES 'utf8'") 
s=paste0("select * from ", db_table) 
rs=dbSendQuery(mydb, s)
df=fetch(rs, n = -1)

edited Jan 19, 2020 at 17:53

baitmbarek

2,5264 gold badges20 silver badges27 bronze badges

answered Jan 19, 2020 at 17:31

Joao Fonseca

512 silver badges4 bronze badges

Comments

Rick James · Accepted Answer · 2016-07-13 17:42:47Z

0

When trying to use utf8/utf8mb4, if you see Mojibake, check the following. This discussion also applies to Double Encoding, which is not necessarily visible.

The bytes to be stored need to be utf8-encoded.
The connection when INSERTing and SELECTing text needs to specify utf8 or utf8mb4.
The column needs to be declared CHARACTER SET utf8 (or utf8mb4).
HTML should start with <meta charset=UTF-8>.

answered Jul 13, 2016 at 17:42

Rick James

144k15 gold badges144 silver badges254 bronze badges

Collectives™ on Stack Overflow

UTF8 encoding using RMySQL

3 Answers 3

EDIT :

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

EDIT :

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related