R连接MySQL数据库方法备忘

R语言连接数据库可以利用数据库的存贮能力和R的计算能力，起到取长补短的效果。之前我们也说过了如何在R中使用SQL，很多教材上也提到了连接MySQL的方法，但是在安装上还需要注意一些细节问题。以下的解决方法也是在网上放狗加撞墙实验得出的结论。


一、win平台
如果是在windows平台上安装，推荐采用ODBC连接方式。除了需要安装R和MySQL之外，还要安装RODBC扩展包，以及MySQL的ODBC驱动。下面是具体步骤：

1、R下载RODBC包，安装好。
2、在http://dev.mysql.com/downloads/connector/odbc
下载mySQL ODBC，安装好。
3、windows：控制面板->管理工具->数据源（ODBC）->双击->添加->选中mysql ODBC driver一项
填写：data source name 一项填入你要使用的名字，自己随便命名，例如：mysql_data;
description一项随意填写，例如mydata
TCP/IP Server 填写服务器IP，本地数据库一般为：127.0.0.1
user 填写你的mysql用户名
password 填写你的mysql密码
然后数据库里会出现你的mysql里的所有数据库，选择一个数据库。
确定。
4、打开R调用数据库：

library(RODBC)
channel <- odbcConnect("mysql_data", uid="abc", pwd="abc")
sqlTables(channel)  #查看数据中的表
data<-sqlFetch(channel,"abc")  # 查看表的内容，存到数据框里

二、linux平台
在linux平台下使用ODBC连接安装比较麻烦，推荐采用JDBC连接方式。具体安装方式如下：

在软件源中增加R官方源，方法具体见官网说明；之后在term中运行发下命令，安装需要的软件：
sudo apt-get update
sudo apt-get install r-base
sudo apt-get install r-dev
sudo apt-get install r-cran-rmysql
sudo apt-get install r-cran-dbi
sudo apt-get install mysql-server my-client
sudo apt-get install libmysqlclient-dev

在R中加载RMySQL两个包，并使用dbConnect函数设置好用户名和密码

library(RMySQL)
con <- dbConnect(MySQL(),
                 user='abc', ＃ 用户名
                 password='abc', ＃ 密码
                 dbname='abc', ＃ 要使用的数据库名称
                 host="localhost") ＃ 主机地址
dbListTables(con) ＃ 列出所有数据库中的表
＃ 运行sql语句并返回数据到R 

data <- dbGetQuery(con, "select * from abc")
SQL查询可以通过dbSendQuery或dbGetQuery传给数据库管理系统。dbGetQuery传送查询语句，把结果以数据框形式返回。dbSendQuery传送查询，返回的结果是继承"DBIResult"的一个子类的对象。"DBIResult"类可用于取得结果，而且还可以通过调用dbClearResult清除结果。函数fetch用于获得查询结果的部分或全部行，并以列表返回。函数dbHasCompleted确定是否所有行已经获得了，而dbGetRowCount返回结果中行的数目。如果只是简单的读整个表，也可以用dbReadTable函数。


在设置连接时会在R语言环境中明文标示了数据库用户和密码，为了安全起见，可以作如下设置：
在本机目录中定义一个配置文档（ ~/.my/cnf），例如定义两个使用MySQL的用户组：
[local]
user = root
password = ultra_secret
host = localhost

[toto]
user = capitaine_flam
password = galaxy
host = milky.way.net

然后在R中使用用户组：

library(RMySQL)
con <- dbConnect(MySQL(), group='toto', dbname="user_profile")
m <- dbGetQuery(con, "SELECT DISTINCT user_id FROM demographics WHERE gender='0'")
dbDisconnect(con)

另外R连接mysql会出现中文乱码变问号情况，查到COS上一个贴子，解决方案如下：

在MySQL的配置文件/etc/mysql/my.cnf中[client]标签下加default-character-set=utf8