R访问数据库管理系统（通过RODBC包和RMySQL包两种方式）

R中有多种面向关系型数据库管理系统(DBMS)的接口，包括SQL Server、Access、MySQL、Oracle、DB2等。其中一些包通过原生的数据库驱动来提供访问功能，另一些则是通过ODBC或JDBC来实现访问的。使用R来访问存储在外部数据库中的数据是一种分析大数据集的有效手段，并且能够发挥SQL和R。

一种方式是依赖RODBC包，该包使用开放数据库连接（Open Database Connectivity,ODBC）驱动作为一种连接到DBMS的方法，这就要求用户必须先安装和配置必要的驱动程序，然后才能在R中使用它。在不同平台和很多有种DBMS都有可用的ODBC驱动程序。它们甚至还能针对根本不是数据库的数据存储格式，如CSV或XLS/XLSX。该组件也提供了一套通用方法，利用同一组函数来管理不同类型的数据库。该方法不足的一面是，它依赖在R运行的平台上是否有能与特定DBMS类型配套的ODBC驱动程序。另一种方式是使用DBI（R Special Interest Group on Databases 2013）的组件，例如RMySQL、ROracle、RPostgreSQL和RSQlite。通过它建立到特定DBMS的“本地”链接。DBI组件定义了虚拟函数，而针对特定数据库的组件则针对具体数据库实现了这些函数。这样虽然有一些通用函数集对所有数据库都是同样有效的，而不同的组件作者可以只针对一种类型的数据库进行有针对性的开发和维护工作。

到底使用哪种方式实现R访问数据库管理系统，这纯粹依赖于你个人的习惯。接下来，让我们在Windows系统下演示通过RODBC包和RMySQL包实现连接32位MySQL，并对数据库进行查表、插入表和删除表等的操作。

方式一：通过RODBC包访问32位MySQL数据库

MySQL的安装非常简单（mysql-5.5.28-win32.msi），此处就不再赘述。安装完成后，我们测试下是否安装成功。打开MySQL 5.5 Command Line Client窗口，输入你安装MySQL时设置的密码，即可登录MySQL。如下截图所示：

输入show databases命令查看目前已有的数据库。

假如我们想进入mysql数据库，利用利用usemysql命令；想查看mysql数据库中有哪些表，可以show tables命令。

现在，让我们输入status命令查看安装的MySQL版本。

可见，我计算机安装的是32位的MySQL（为了与服务器上的MySQL版本一致）。此时，我们需要调出32位的ODBC数据源管理器来配置MySQL驱动。由于本机计算机安装的64位的Win 7，所以控制面板中的ODBC数据源管理器也是64位的，我们需要在C:\Windows\SysWOW64文件夹下找到odbcad32.exe，双击打开ODBC数据源管理器界面。

点击添加，得到以下窗口，选择MySQL驱动：

如果找不到MySQL驱动，下载mysql-connector-odbc-5.3.6-win32.msi双击进行安装即可。选择MySQL驱动点击完成后得到的窗口如下：

其中Data Source Name可以填写你自己喜欢的名称（这边假设设置为daniel），Description可填可不填，TCPIP Server是你要连接的MySQL数据库IP地址，如果是你本机计算机，可填写localhost，Port默认端口号是3306，User和Password是登录MySQL的账号和密码，Database是需要连接MySQL中的那个数据库。设置完成后，点击Test按钮验证是否配置OK。

出现ConnectionSuccessful，说明驱动配置成功。接下来，我们就需要在R中安装RODBC包（通过install.packages(“RODBC”)），实现R访问数据库管理系统。大家需要注意一点，如果你们是64位的计算机，安装R时默认是安装32位和64位两个版本的，此时需要在32位的R中安装RODBC包。

包下载安装好后，就可以利用包中的odbcConnect(dsn, uid = "", pwd = "", ...)函数进行数据库连接，并继续数据的传输及分析工作。

>library(RODBC)
> channel <-odbcConnect("daniel","root","123456")
> channel
RODBC Connection 1
Details:
  case=tolower
  DSN=daniel
  UID=root
  PWD=******

可以通过odbcGetInfo命令查看连接数据库的详细信息。

假如想把R中的mtcars数据集保存到MySQL中，可以通过sqlSave命令实现。第一个参数channel是建立的链接，第二个参数dat是指R中的数据集，第三个参数tablename是指MySQL中的表明，append参数是逻辑值，默认为FALSE表示建立新表（如果表明已经存在则会报错），TRUE表示在已有表中插入新数据。

我们现在MySQL输入desc mydata命令查看表mydata的数据结构，并通过SQL语句查看行数。

可见，表mydata已经存在，且有32条记录。我们在R中运行以下命令：

sqlSave(channel,mtcars,"mydata",append= FALSE)

此时报错，错误原因就是将append参数设置为FALSE,提示MySQL中mydata表已经存在。

我们将append参数设置为TRUE再次尝试。

运行成功，回到MySQL中，查看mydata的行数。

行数比之前多了一倍，证明数据已经在后面追加成功。

现在，我们在R中利用sqlDrop命令将MySQL中的表mydata删除。运行以下命令

sqlDrop(channel,"mydata")

回到MySQL中，通过SQL语句查看表mydata的前六行：

错误提示说明表mydata不存在。

再次运行sqlSave(channel,mtcars,"mydata",append= FALSE)命令重新将mtcars数据集保存到MySQL中，生成新表mydata。现在我们在R中利用sqlFetch命令和sqlQuery命令进行MySQL表的提取和SQL语句查询。

当然，我们也可以执行更复杂的SQL语句，完成多表查询或数据统计工作。比如想根据vs和am变量进行分组计算mpg的平均值，可以执行以下命令：

sqlQuery(channel,"selectvs,am,avg(mpg) from mydata group by vs,am")

结果与利用aggregate(mtcars$mpg,by= list(mtcars$vs,mtcars$am),mean)命令相同。

好了，常用的一些函数功能已经介绍了，最后，大家别忘记通过odbcClose命令关闭连接。

>odbcClose(channel)
>odbcGetInfo(channel)
Error inodbcGetInfo(channel) : argument is not an open RODBC channel 

方式二：通过RMySQL包访问32位MySQL数据库

此处还是以Windows为例进行演示（linux系统类似）。RMySQL包的安装非常简单，不需要配置ODBC数据源管理器，直接在R中通过install.packages(“RMySQL”)语句进行安装。

安装完成后，通过dbConnect命令建立R与MySQL数据库的连接。

可以通过dbGetInfo命令查看连接信息。

通过dbListTables命令查看连接数据库中的表名。

结果与直接在MySQL运行show tables命令相同。

通过dbListFields命令查看某个表的字段。

通过dbReadTable命令读取表。

通过dbGetQuery命令返回查询数据。

有时候，大家可能会遇到利用RMySQL包读取MySQL中数据是出现中文乱码问题。比如我的MySQL中有一个叫revenue的表，收录了一些游戏收入的数据。

如果我们在R中通过dbGetQuery命令查询该表的前六行数据。

出现中文乱码问题。我们执行以下代码解决中文乱码问题。

可见，中文已经正常显示。

好了，已经非常详细介绍了RODBC包和RMySQL包的安装及用法，大家可以根据自己的习惯选择包来进行尝试。

号外：

4月18日（下周二）谢佳标老师在Hellobi Live直播授课，只要19.99喔。老司机带你快速上手之R语言实践！

参加方式：扫码参团 | 阅读原文