R连接远程MySQL

发表于 | 分类于

数据分析师在工作中会经常做这一件事，R从远程数据库中取数，然后进行数据处理、分析和绘图。本文我们介绍R连接远程MySQL。

本地和远程连接

只要本地和远程能ping通，即可访问远程数据库。

  1. 本地和远程在同一网段。如192.168.1.101和192.168.1.102，前三位地址相同则在同一网段～
  2. 关闭防火墙
  3. 如ping 远程ip地址能返回数据则说明本地和远程可以连接

R连接远程MySQL

SendQuery

1
2
3
4
5
6
7
8
9
library(RMySQL)
con1 = dbConnect(RMySQL::MySQL(),
dbname = 'wind_macro_economic',
username = 'your_user_name',
password = 'your_password',
host = 'your_remote_ip_address')# 设置连接
res = dbSendQuery(con1,"select * from your_database;")# 查询
mydata = dbFetch(res)# 读入数据框
head(mydata)

GetQuery

感谢王丽华同学发现GetQuery效率更高～

1
2
3
4
5
6
7
8
9
library(RMySQL)
con1 = dbConnect(RMySQL::MySQL(),
dbname = 'wind_macro_economic',
username = 'your_user_name',
password = 'your_password',
host = 'your_remote_ip_address')# 设置连接
res = dbGetQuery(con1,"set names gbk")# 查询
mydata = dbGetQuery(con1,"select * from your_database;")# 读入数据框
head(mydata)

解决数据乱码

MySQL设置

服务器和客户端编码最好改成utf8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show variables like "%character%";
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | utf8                                                   |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | utf8                                                   |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.5.48-osx10.8-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.00 sec)

R语言设置

windows下R读取远程MySQL的数据，出现了乱码，时间紧张，没有仔细找资料解决，暂时设置编码gbk

1
2
3
4
5
6
7
dbSendQuery(con1,"set names gbk")# 设置gbk编码解决R乱码
query = "select * from 整体宏观经济;"
res1 = dbSendQuery(con1,query)
mydata = dbFetch(res1)
head(mydata)
dbClearResult(res)# 清除查询
dbDisconnect(con1)# 关闭连接

解决select语句每次只返回500条

返回所有行，需添加n = -1

1
mydata = dbFetch(res1,n = -1)

R数据框写入数据库

1
2
3
4
dbWriteTable(con1,"sql_tablename",dataframe,
             row.names = FALSE,
             replace = TRUE, # 同数据库replace
             append = TRUE)
喂他一颗糖