R 语言写入 RMySQL 中文乱码

R语言在windows 平台写入 MySQL 可以使用下面的库：

library(RMySQL)

con <- RMySQL::dbConnect(MySQL(),host='localhost', port=3306, user='username', password='passwd',dbname='testDB')

dbSendQuery(con,'SET NAMES gbk')  ==> 用于处理中文乱码问题，详情见下文

常用的命令：

dbListTables(con)

dbRemoveTable(con,"testtable")

fruits <-data.frame(id=1:5,name=c("苹果","香蕉","梨子","玉米","西瓜"),price=c(8.8,4.98,7.8,6,2.1),status=c("无","打折","无","售罄","批发"))

dbWriteTable(con,"fruits",fruits,append=TRUE)

dbReadTable(con,"fruits")
res=dbSendQuery(con,"select * from fruits order by price") 
dat=fetch(res)

由于MySQL 默认不支持中文，需要在 MySQL 里面增加对中文的支持

在Linux 平台，做如下修改：

sudo vim ./mysql.conf.d/mysqld.cnf

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
# For Chinese characters
character-set-server=gbk

[mysqld]
#
# * Basic Settings
#
user            = mysql
# For Chinese characters
character-set-server=gbk

修改完成之后，重启 mysql 服务

sudo /etc/init.d/mysql restart

登入 mysql：

mysql -u root -p
xxxxxx
mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | gbk                        |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | gbk                        |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

把原来的数据库删掉，重新创建数据库和表，就可以解决中文问题

> dbReadTable(con,"fruits")
   row_names id name price status
1          1  1 苹果  8.80     无
2          2  2 香蕉  4.98   打折
3          3  3 梨子  7.80     无
4          4  4 玉米  6.00   售罄
5          5  5 西瓜  2.10   批发