当前位置：theitroad>如何将 R 与 MySQL 连接或如何安装 RMySQL 包？

如何将 R 与 MySQL 连接或如何安装 RMySQL 包？

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/10292326/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 13:05:32 　来源:igfitidea点击:

How to Connect R with MySQL or how to install RMySQL package?

mysqlrinstallationrmysql

提问by Sharad

I am new in R and i am trying to connect R with MySQL. I have installed mysql-5.5.22-winx64and R-2.12.0for 64 bit. I have already set MYSQL_HOME environment path(C:\Program Files\MySQL\MySQL Server 5.5)and trying to follow these steps:

我是 R 新手，我正在尝试将 R 与 MySQL 连接起来。我已经安装mysql-5.5.22-winx64和R-2.12.064位。我已经设置MYSQL_HOME environment path(C:\Program Files\MySQL\MySQL Server 5.5)并尝试执行以下步骤：

  1. Install latest RTools from here
  2. install MySQL or header and library files of mysql
  3. create or edit file C:\Program Files\R\R-2.12.1\etc\Renviron.siteand add line like MYSQL_HOME=C:/mysql(path to your mysql files)
  4. copy libmysql.libfrom mysql/libto mysql/lib/optto meet dependencies.
  5. copy libmysql.dllto C:\Program Files\R\R-2.12.1\binor to windows/system32 directory.

  6. run install.packages('RMySQL',type='source')and wait while compilation will end. but still i am getting this error:-

    *> install.packages('RMySQL',type='source')
 --- Please select a CRAN mirror for use in this session ---
 trying URL 'http://ftp.iitm.ac.in/cran/src/contrib/RMySQL_0.9-3.tar.gz'
 Content type 'application/x-gzip' length 165363 bytes (161 Kb)
 opened URL
 downloaded 161 Kb

* installing *source* package 'RMySQL' ...
ERROR: configuration failed for package 'RMySQL'
* removing 'C:/PROGRA~1/R/R-212~1.0/library/RMySQL'
* restoring previous 'C:/PROGRA~1/R/R-212~1.0/library/RMySQL'

 The downloaded packages are in
  ‘C:\Users\sharad\AppData\Local\Temp\RtmpdQHwCb\downloaded_packages'
 Warning message:
      In install.packages("RMySQL", type = "source") :
installation of package 'RMySQL' had non-zero exit status*
  1. 从这里安装最新的 RTools
  2. 安装 MySQL 或 mysql 的头文件和库文件
  3. 创建或编辑文件C:\Program Files\R\R-2.12.1\etc\Renviron.site并添加类似行MYSQL_HOME=C:/mysql（mysql 文件的路径）
  4. libmysql.lib从mysql/libto复制mysql/lib/opt以满足依赖关系。
  5. 复制libmysql.dll到C:\Program Files\R\R-2.12.1\bin或windows/system32 directory。

  6. 运行install.packages('RMySQL',type='source')并等待编译结束。但我仍然收到此错误：-

    *> install.packages('RMySQL',type='source')
 --- Please select a CRAN mirror for use in this session ---
 trying URL 'http://ftp.iitm.ac.in/cran/src/contrib/RMySQL_0.9-3.tar.gz'
 Content type 'application/x-gzip' length 165363 bytes (161 Kb)
 opened URL
 downloaded 161 Kb

* installing *source* package 'RMySQL' ...
ERROR: configuration failed for package 'RMySQL'
* removing 'C:/PROGRA~1/R/R-212~1.0/library/RMySQL'
* restoring previous 'C:/PROGRA~1/R/R-212~1.0/library/RMySQL'

 The downloaded packages are in
  ‘C:\Users\sharad\AppData\Local\Temp\RtmpdQHwCb\downloaded_packages'
 Warning message:
      In install.packages("RMySQL", type = "source") :
installation of package 'RMySQL' had non-zero exit status*

Please suggest me how can I solve this problem?

请建议我如何解决这个问题？

Finally I got the solution:- You can see the solution in detail on my blog.

最后我得到了解决方案：-您可以在我的博客上详细查看解决方案。

I was working for last 2 days on Installation of R with RMySQL package, finally got the solution for that, here are the steps to install RMySQL package:-

我过去 2 天一直在用 RMySQL 包安装 R，终于找到了解决方案，以下是安装 RMySQL 包的步骤：-

  1. DOWNLOAD SOFTWARE FROM THE FOLLOWING LINKS:

       * a. R2.13.2:  Download R from http://cran.stat.sfu.ca/index.html

    b. RTools 214:  Download RTools from http://cran.cict.fr

    c. RMySQL 0.8-0.tar.gz: Download RMySQL from 
     http://biostat.mc.vanderbilt.edu/wiki/main/RMySQL/RMySQL_0.8-0.tar.gz

    d. MySQL Server 5.0: download it from http://dev.mysql.com

    e. RSTUDIO (optional): download it from http://rstudio.org*

  2. SET THE FOLLOWING ENVIRONMENT VARIABLES

        * a. MYSQL_HOME : <drive>/path to MySQL installation folder
         e.g. MYSQL_HOME= C:\Program Files\MySQL\MySQL Server 5.5\  

      b. R_HOME: <drive>/path to R installation
         e.g. R_HOME=C:\Program Files\R\R-2.13.2\

       c. PATH: Modify path to accommodate the above variables. *

    Be sure that the following paths areincluded in your Windows PATH variable: \Rtools\2.14\bin \Rtools\2.14\MinGW\bin \Rtools\2.14\MinGW64\bin

  3. CREATE FOLDER AND COPY FILES

        * a. OPT: Create a folder OPT under
             C:\Program Files\MySQL\MySQL Server 5.5\lib and
         copy MYSQLLIB.LIB  the above path.
       Also copy libmysql.dll to
              <drive>\<path>\R\R-2.14.0\bin\(64 bit) Or
              <Drive>\<path>\R\R-2.14.0\bin\i386\ (32 bit) and
         to C:\Windows\System32.

      b. Renviron.site: create or edit a file 
              <DRIVE>\<path>\R\R-2.14.0\etc\Renviron.site and 
        add a line: 
          MYSQL_HOME =”C:/Program Files/MySQL/MySQL Server 5.5/”
        NB: USE FORWARD SLASH AND DOUBLE QUOTES HERE

      c. libMySQL.dll: Copy this file to
            C:\Program Files\R\R-2.13.2\bin\i386 as well as
            C:\Program Files\R\R-2.13.2\bin*

    1. RUN COMMANDS

      a. Install.Packages: Run R GUI by clicking on the R icon on desktop or from Start menu. Type INSTALL.PACKAGES(“RMySQL”,type=”Sources”).This will download the required software from repositories.

      b. Command Prompt: Copy the downloaded zip file (in step 4.a.) and paste it under R installation folder. Go to start menu and open Command Prompt. Go to the R installation folder and type R CMD INSTALL RMySQL_0.8-0.tar.gz

      *COMMANDS:

      library(RMySQL) drv = dbDriver("MySQL") con = dbConnect(drv,host="localhost",dbname="test",user="root",pass="root") album = dbGetQuery(con,statement="select * from t_master") album*

  1. 从以下链接下载软件：

       * a. R2.13.2:  Download R from http://cran.stat.sfu.ca/index.html

    b. RTools 214:  Download RTools from http://cran.cict.fr

    c. RMySQL 0.8-0.tar.gz: Download RMySQL from 
     http://biostat.mc.vanderbilt.edu/wiki/main/RMySQL/RMySQL_0.8-0.tar.gz

    d. MySQL Server 5.0: download it from http://dev.mysql.com

    e. RSTUDIO (optional): download it from http://rstudio.org*

  2. 设置以下环境变量

        * a. MYSQL_HOME : <drive>/path to MySQL installation folder
         e.g. MYSQL_HOME= C:\Program Files\MySQL\MySQL Server 5.5\  

      b. R_HOME: <drive>/path to R installation
         e.g. R_HOME=C:\Program Files\R\R-2.13.2\

       c. PATH: Modify path to accommodate the above variables. *

    确保以下路径包含在您的 Windows PATH 变量中： \Rtools\2.14\bin\Rtools\2.14\MinGW\bin\Rtools\2.14\MinGW64\bin

  3. 创建文件夹和复制文件

        * a. OPT: Create a folder OPT under
             C:\Program Files\MySQL\MySQL Server 5.5\lib and
         copy MYSQLLIB.LIB  the above path.
       Also copy libmysql.dll to
              <drive>\<path>\R\R-2.14.0\bin\(64 bit) Or
              <Drive>\<path>\R\R-2.14.0\bin\i386\ (32 bit) and
         to C:\Windows\System32.

      b. Renviron.site: create or edit a file 
              <DRIVE>\<path>\R\R-2.14.0\etc\Renviron.site and 
        add a line: 
          MYSQL_HOME =”C:/Program Files/MySQL/MySQL Server 5.5/”
        NB: USE FORWARD SLASH AND DOUBLE QUOTES HERE

      c. libMySQL.dll: Copy this file to
            C:\Program Files\R\R-2.13.2\bin\i386 as well as
            C:\Program Files\R\R-2.13.2\bin*

    1. 运行命令

      一种。Install.Packages：通过单击桌面上的 R 图标或从开始菜单运行 R GUI。输入 INSTALL.PACKAGES(“RMySQL”,type=”Sources”)。这将从存储库下载所需的软件。

      湾 命令提示符：复制下载的 zip 文件（在步骤 4.a. 中）并将其粘贴到 R 安装文件夹下。转到开始菜单并打开命令提示符。转到 R 安装文件夹并键入 R CMD INSTALL RMySQL_0.8-0.tar.gz

      *命令：

      library(RMySQL) drv = dbDriver("MySQL") con = dbConnect(drv,host="localhost",dbname="test",user="root",pass="root")专辑= dbGetQuery(con,statement= “从 t_master 中选择 *”）专辑*

采纳答案by jcb

It is not a direct answer but still you may find it helpful:

这不是一个直接的答案，但您仍然可能会发现它有帮助：

  1. Use a more up to date version of R (currently at 2.15)

  2. On Windows platforms I'd rather use RODBC + Windows MySQL driver, unless you are in a environment with heterogenous platforms (i.e. Linux and Windows) where code is heavily shared among team members. And even then choosing between using RMySQL and RODBC in the same script depending on the platform it runs is a simple if() {...} else {...}

  1. 使用更新的 R 版本（目前为 2.15）

  2. 在 Windows 平台上，我宁愿使用 RODBC + Windows MySQL 驱动程序，除非您处于具有异构平台（即 Linux 和 Windows）的环境中，其中代码在团队成员之间大量共享。甚至根据它运行的平台在同一脚本中使用 RMySQL 和 RODBC 之间进行选择也很简单if() {...} else {...}

Notice that I am not saying there are no success stories with what you are trying to do, but IMHO you'll be up and running sooner with the above.

请注意，我并不是说您尝试做的事情没有成功的案例，但恕我直言，您会更快地启动并运行上述内容。

回答by hadley

You can now skip all the complicated steps and just do install.packages("RMySQL")

您现在可以跳过所有复杂的步骤，只需执行 install.packages("RMySQL")

回答by Elliot Koss

I ran into this over the weekend at a hackathon on Mac OSX - took me a solid 4 hours to piece everything together despite having a few reference materials (mentioned at the end). I didn't find an easy walk-through, so I decided to post one while it is fresh in my mind.

周末我在 Mac OSX 上的黑客马拉松上遇到了这个问题——尽管有一些参考资料（最后提到），但我还是花了 4 个小时把所有东西拼凑起来。我没有找到一个简单的演练，所以我决定在我还记得的时候发布一个。

I'm not sure of the compatibility with Windows, but hopefully these instructions will make it easier for you too.

我不确定与 Windows 的兼容性，但希望这些说明也能让您更轻松。

I was trying to get R and MySQL to communicate in a local environment (there may need to be changes for a server environment). I use XAMPP (though I didn't use RMySQL for the connection), but in the end I was able to use a PHP page to write an R file, execute that file, and have R write to a MySQL table. To the best of my knowledge this only works for MacOSX...

我试图让 R 和 MySQL 在本地环境中进行通信（可能需要对服务器环境进行更改）。我使用 XAMPP（虽然我没有使用 RMySQL 进行连接），但最后我能够使用 PHP 页面来编写 R 文件，执行该文件，并将 R 写入 MySQL 表。据我所知，这仅适用于 MacOSX...

All software used was in dmg form so no binary installs necessary.

使用的所有软件都是 dmg 形式，因此不需要二进制安装。

  1. Download Rand run some basic commands to make sure that you have it working.

  2. In R, you need to install RODBC (if you don't have it already). Type this into the R console.

  1. 下载 R并运行一些基本命令以确保它正常工作。

  2. 在 R 中，您需要安装 RODBC（如果您还没有安装）。将其输入到 R 控制台中。

install.packages("RODBC")

This installs RODBC, but since OS Mavericks, certain files are no longer included, so you get an error message

这将安装 RODBC，但由于 OS Mavericks，某些文件不再包含在内，因此您会收到一条错误消息

ODBC headers sql.h and sqlext.h not found

找不到 ODBC 标头 sql.h 和 sqlext.h

and you need to get the sql.h and sqlext.h files in the right place.

并且您需要在正确的位置获取 sql.h 和 sqlext.h 文件。

To do this the easiest way, make sure that you have homebrewinstalled (easy instructions). Then use this codein terminal to make the install.

要以最简单的方式执行此操作，请确保已安装自制软件（简单说明）。然后在终端中使用此代码进行安装。

Once that's done, you enter into the R console one more time

完成后，您再次进入 R 控制台

install.packages("RODBC")

  1. Search MySQL for the appropriate ODBC installation. I'm running Mac OSX 10.6 so I downloaded the dmg and installed it. This took care of itself.

  2. Now comes the tricky part. Apparently Mac OX took out the ODBC Administrator after a recent OS release, so you need to download ODBC Manager (http://www.odbcmanager.net/). It too is a dmg file so just drag and drop to your utilities folder.

  1. 在MySQL 中搜索合适的 ODBC 安装。我运行的是 Mac OSX 10.6，所以我下载了 dmg 并安装了它。这照顾了自己。

  2. 现在是棘手的部分。显然 Mac OX 在最近的操作系统发布后删除了 ODBC 管理器，因此您需要下载 ODBC 管理器 ( http://www.odbcmanager.net/)。它也是一个 dmg 文件，因此只需将其拖放到您的实用程序文件夹即可。

I had difficulties with the 5.3.6 dmg install (kept failing), so I installed 5.2.7 instead.

我在安装 5.3.6 dmg 时遇到了困难（一直失败），所以我安装了 5.2.7。

  1. Open ODBC Manager. You need to configure the DSN, so click the tab "System DSN" and click "add".

  2. You'll get a popup window asking you to select a driver. Mine had "MySQL ODBC 5.2 Driver" based on my MySQL ODBC install. Click "Ok". If you don't see the driver, then you need to confirm that the MySQL ODBC installed.

  3. In the next popup window, make the Data Source Name (DSN) whatever you want - but remember that this is the name you need to use to call from R. In the keyword area below (keywords will be in quotes and the value will be in parentheses), ADD

    "database" (with value of your database name)

    "server" (for the local environment do NOT use localhost - instead use the local IP address 127.0.0.1. *** This was the KEY piece for me)

    "uid" (database user ID)

    "pwd" (database password)

    "socket" (not sure if this was required, but after multiple tutorials it was left in my configuration and things work, so maybe you need it. You can find your socket location in my.cnf - do a spotlight search. The socket file location is under CLIENT)

    Here's what my configuration looked like:

    DSN ("test" - this was the at the top)

    database ("televisions")

    socket ("/Applications/XAMPP/xamppfiles/var/mysql.sock")

    uid ("root")

    pwd ("")

    server ("127.0.0.1")

  4. In R, execute below - I believe these last 3 steps need to be done every time you start R and before you make a MySQL query.

    library(RODBC)

  5. Make sure that you've turned on MySQL and Apache from the XAMPP control panel.

  6. Then execute

    odbcConnect("test") - notice how I used my DSN in the double quotes. Interchange as necessary.

  1. 打开 ODBC 管理器。您需要配置 DSN，因此单击“系统 DSN”选项卡，然后单击“添加”。

  2. 您将看到一个弹出窗口，要求您选择驱动程序。我有基于我的 MySQL ODBC 安装的“MySQL ODBC 5.2 驱动程序”。单击“确定”。如果没有看到驱动程序，则需要确认安装了 MySQL ODBC。

  3. 在下一个弹出窗口中，根据需要设置数据源名称 (DSN) - 但请记住，这是从 R 调用所需的名称。在下面的关键字区域中（关键字将用引号引起来，值将是在括号中），添加

    “数据库”（带有您的数据库名称的值）

    “服务器”（对于本地环境，不要使用 localhost - 而是使用本地 IP 地址 127.0.0.1。*** 这对我来说是关键部分）

    “uid”（数据库用户 ID）

    “pwd”（数据库密码）

    “socket”（不确定这是否是必需的，但经过多个教程后，它留在我的配置中并且一切正常，所以也许您需要它。您可以在 my.cnf 中找到您的套接字位置 - 进行聚光灯搜索。套接字文件位置在 CLIENT 下）

    这是我的配置：

    DSN（“测试” - 这是顶部）

    数据库（“电视”）

    套接字（“/Applications/XAMPP/xamppfiles/var/mysql.sock”）

    uid（“根”）

    密码 ("")

    服务器（“127.0.0.1”）

  4. 在 R 中，在下面执行 - 我相信在每次启动 R 和进行 MySQL 查询之前都需要完成最后 3 个步骤。

    库（RODBC）

  5. 确保您已从 XAMPP 控制面板打开 MySQL 和 Apache。

  6. 然后执行

    odbcConnect("test") - 注意我如何在双引号中使用我的 DSN。必要时互换。

This should get you up and running. You can read other tutorials about making MySQL queries in R.

这应该能让你启动并运行。您可以阅读有关在 R 中进行 MySQL 查询的其他教程。

I hacked this together from a lot of great posts on Stack Overflow (thanks everyone!), random other sites/email exchange histories, and the "R In A Nutshell" book by Joseph Adler, but let me know if I missed something or it's unclear.

我从 Stack Overflow 上的很多很棒的帖子（谢谢大家！）、随机的其他网站/电子邮件交换历史以及 Joseph Adler 的“R In A Nutshell”一书中一起破解了这个问题，但如果我错过了什么或者它是什么，请告诉我不清楚。

Good luck!

祝你好运！

回答by cardamom

I also spent a few hours trying to make this work in Windows 10, getting errors. The fixes I found for RMySQL were messy and complex but RODBC has more painlessly and elegantly lead to a solution. I had R, RStudio, MySQL Server, MySQL Workbench and the following additional steps were required to make it work:

我还花了几个小时试图在 Windows 10 中进行这项工作，但出现错误。我为 RMySQL 找到的修复程序混乱而复杂，但 RODBC 更轻松、更优雅地找到了解决方案。我有 R、RStudio、MySQL Server、MySQL Workbench，并且需要以下附加步骤才能使其工作：

  1. Install the package in RStudio install.packages(RODBC)
  2. Download and install the MySQL ODBC connector here
  3. Configure the MySQL ODBC connector. Hereare some instructions. Just search from the start screen on Windows 10 for 'ODBC' and it pops up that window. Get the connection parameters right and use the test button to make sure it's working. It shows a list of your databases in a drop down menu once it connects. In "Data Source Name" give it a name which will go into the R script, say 'mysql_odbc'
  1. 在 RStudio 中安装包 install.packages(RODBC)
  2. 在此处下载并安装 MySQL ODBC 连接器
  3. 配置 MySQL ODBC 连接器。这里有一些说明。只需从 Windows 10 的开始屏幕搜索“ODBC”，它就会弹出该窗口。正确获取连接参数并使用测试按钮确保其正常工作。连接后，它会在下拉菜单中显示您的数据库列表。在“数据源名称”中，给它一个将进入 R 脚本的名称，例如“mysql_odbc”

Now you can connect, run a query and disconnect:

现在您可以连接、运行查询并断开连接：

library(RODBC)
cursor <- odbcConnect("mysql_odbc", uid="root", pwd="HaysPuffyWalton5")
out <- sqlQuery(cursor, "SELECT * FROM emp WHERE deptno = 10");
close(cursor);

回答by Javiar Sandra

This worked for me: Using Windows XP SP3 32bit OS, R Studio, Rv2.15.3

这对我有用：使用 Windows XP SP3 32 位操作系统、R Studio、Rv2.15.3

Follow the instructions from: http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL

按照以下说明操作：http: //biostat.mc.vanderbilt.edu/wiki/Main/RMySQL

However if you have downloaded MySQL edition 5.6, you will have to copy libmysql.libpresent for example in this path: D:\Program Files\MySQL\MySQL Server 5.6\liband create a new folder as in: D:\Program Files\MySQL\MySQL Server 5.6\lib\optand paste the above libmysql.libin the optfolder. Then the Renviron.sitefile will have this: MYSQL_HOME=D:/Program Files/MySQL/MySQL Server 5.6. Please note the forward slash.

但是，如果您下载了 MySQL 5.6 版，则必须复制libmysql.lib当前路径，例如：D:\Program Files\MySQL\MySQL Server 5.6\lib并创建一个新文件夹，如：D:\Program Files\MySQL\MySQL Server 5.6\lib\opt并将上述内容粘贴libmysql.lib到opt文件夹中。然后Renviron.site文件会有这个：MYSQL_HOME=D:/Program Files/MySQL/MySQL Server 5.6. 请注意正斜杠。

Finally follow instructions from install.packages line as provided in the link above. I had to do a few trials to get it finally right.

最后按照上面链接中提供的 install.packages 行中的说明进行操作。我不得不做一些试验才能最终正确。

回答by Andre Mikulec

IF THIS ERROR IN R

如果这个错误在 R

library(RMySQL)
#-----------
Loading required package: DBI

Error : .onLoad failed in loadNamespace() for 'RMySQL', details

  call: i$Location

  error: $ operator is invalid for atomic vectors

Error: package or namespace load failed for 'RMySQL'

THEN FROM OUTSIDE R

然后从外部 R

set MYSQL_HOME=F:/Program Files/MySQL/MySQL Server 5.6

AND BACK INSIDE:

回到里面：

library(RMySQL)

#Loading required package: DBI
#MYSQL_HOME defined as F:/Program Files/MySQL/MySQL Server 5.6

OR JUST FROM INSIDE R

或者只是从 R 内部

Sys.setenv(MYSQL_HOME = "F:/Program Files/MySQL/MySQL Server 5.6")
library(RMySQL)

#Loading required package: DBI
#MYSQL_HOME defined as F:/Program Files/MySQL/MySQL Server 5.6

回答by user3262061

Follow the guide here:

请按照此处的指南进行操作：

http://www.ahschulz.de/2013/07/23/installing-rmysql-under-windows/

http://www.ahschulz.de/2013/07/23/installing-rmysql-under-windows/

I followed it, and it worked like a charm. :)

我跟着它，它就像一个魅力。:)

相关推荐

PostgreSQL 与 MySQL 有何不同？

PostgreSQL 与 MySQL 有何不同？

设置 MySQL 并在 Dockerfile 中导入转储

设置 MySQL 并在 Dockerfile 中导入转储

如何合并两个 MySQL 表？

如何合并两个 MySQL 表？

MySQL 中的分隔符

MySQL 中的分隔符

相关推荐
最近更新
标签