mysqlrencodingutf-8rmysql

dbWriteTable ignores UTF-8 encoding

I am trying to write my dataframe into mysql table using UTF-8 encoding. The encoding seems to not working properly. When I open the table in a Workbench environment the UTF-8 encoding is not set as it should. Strange characters appears.

Here is the workbench view

mysql connection:

conn = dbConnect(MySQL(), user='user', password='pw', dbname='dbname', host='127.0.0.1')

setting UTF-8:

dbSendQuery(conn, 'SET NAMES utf8')

the dataframe need to be inserted into mysql table:

id = c(1, 2, 3); vnt = c("Individualioji įmonė", "Akcinė Bendrovė", "Mažoji bendrija")
df = data.frame(id, vnt)

Writing df to mysql table

dbWriteTable(conn,  value = df, name = "mysql_df", row.names=F, append = TRUE)

My question, is it possible to set charset to UTF-8 using dbWriteTable function?

Solution

  • Solved. The solution is so simple. Right before writing data to mysql you need to overwrite dbConnect object:

    conn = dbConnect(MySQL(), user='', password='', dbname='', host='127.0.0.1')