233-如何将MySQL与R语言结合起来

Jan 29, 2021 4 min read cnposts

刘小泽写于2021.1.29
之前我们探索了：在Mac上探索MySQL的安装与使用下面内容将切换使用R和终端（简称“T”），意思就是在对应的应用（Rstudio + ternimal）中运行

R：安装R包

install.packages(c('DBI','RMySQL'))

R：加载使用

library(DBI)
# 用 guest 账户登录连接数据库 testdb
con <- DBI::dbConnect(RMySQL::MySQL(), dbname = 'testdb', host = "localhost", port = 3306, user = "guest", password = "guest123")

T：查看mysql概况

# 首先看一下我们用户当前的数据库
mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| testdb             |
+--------------------+
2 rows in set (0.00 sec)

# 然后切换到这个数据库
mysql> use testdb;
Database changed

# 查看该数据库下面的表格，可以看到当前为空
mysql> show tables;
Empty set (0.00 sec)

接下来，我们试图从R中写入一些表格到我们的数据库中

R：写入数据

dbWriteTable(con, "mtcars", mtcars)

此时你可能会遇到两种报错：

第一种：
说明你的数据库当前存在这个文件，你可以试图修改第二个参数即名称

第二种：

我也搜索了一下解决方案，问题主要出在local_infile这个文件上：https://stackoverflow.com/questions/59993844/error-loading-local-data-is-disabled-this-must-be-enabled-on-both-the-client

mysql> show global variables like 'local_infile';
  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
# 看这里是OFF
  
# 我们需要将他激活，结果又报错：因为我们需要使用root用户来运行这个
mysql> set global local_infile=true;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
  
# 重新登录root，然后激活，重新检查
mysql> mysql -u root -p
mysql> set global local_infile=true;
mysql> show global variables like 'local_infile';
  
# 退出，重新登录guest
mysql> exit
mysql> mysql --local_infile=1 -u guest -p

再次运行，就会成功

> dbWriteTable(con, "mtcars", mtcars)
[1] TRUE

T: 看看上面R导入的表格

mysql> USE testdb
mysql> SELECT * FROM mtcars;

R：R中也能像上面👆一样查看

dbGetQuery(con, "SELECT * FROM mtcars")

T：可以查看这个表格中各个列在mysql中的存储类型

mysql> SHOW columns FROM mtcars;

+-----------+--------+------+-----+---------+-------+
| Field     | Type   | Null | Key | Default | Extra |
+-----------+--------+------+-----+---------+-------+
| row_names | text   | YES  |     | NULL    |       |
| mpg       | double | YES  |     | NULL    |       |
| cyl       | double | YES  |     | NULL    |       |
| disp      | double | YES  |     | NULL    |       |
| hp        | double | YES  |     | NULL    |       |
| drat      | double | YES  |     | NULL    |       |
| wt        | double | YES  |     | NULL    |       |
| qsec      | double | YES  |     | NULL    |       |
| vs        | double | YES  |     | NULL    |       |
| am        | double | YES  |     | NULL    |       |
| gear      | double | YES  |     | NULL    |       |
| carb      | double | YES  |     | NULL    |       |
+-----------+--------+------+-----+---------+-------+
12 rows in set (0.00 sec)

R：R中查看存储类型

> dbGetQuery(con, "SHOW columns FROM mtcars")

       Field   Type Null Key Default Extra
1  row_names   text  YES        <NA>      
2        mpg double  YES        <NA>      
3        cyl double  YES        <NA>      
4       disp double  YES        <NA>      
5         hp double  YES        <NA>      
6       drat double  YES        <NA>      
7         wt double  YES        <NA>      
8       qsec double  YES        <NA>      
9         vs double  YES        <NA>      
10        am double  YES        <NA>      
11      gear double  YES        <NA>      
12      carb double  YES        <NA> 

# 另外和R中内置的类型查看函数对比
str(mtcars)

'data.frame':	32 obs. of  11 variables:
 $ mpg : num  21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ...
 $ cyl : num  6 6 4 6 8 6 8 4 4 6 ...
 $ disp: num  160 160 108 258 360 ...
 $ hp  : num  110 110 93 110 175 105 245 62 95 123 ...
 $ drat: num  3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ...
 $ wt  : num  2.62 2.88 2.32 3.21 3.44 ...
 $ qsec: num  16.5 17 18.6 19.4 17 ...
 $ vs  : num  0 0 1 1 0 1 0 1 1 1 ...
 $ am  : num  1 1 1 0 0 0 0 0 0 0 ...
 $ gear: num  4 4 4 3 3 3 3 4 4 4 ...
 $ carb: num  4 4 1 1 2 1 4 2 2 4 ...

主要看不同之处：

数据库将行名row_names作为单独一列输入，格式是text；当然可以通过设置row.names=F取消行名单独作为一列
当然，如果不想让行名作为单独的一列，在写入时可以设置：dbWriteTable(con, "mtcars", mtcars, row.names = FALSE)
如果原来存在这个名字的表，可以选择覆盖：overwrite=T ；或者在原表的基础上继续添加行：append=T（这两个参数默认为FALSE）
- If the append argument is TRUE, the rows in an existing table are preserved, and the new data are appended. If the table doesn’t exist yet, it is created.
- If the overwrite argument is TRUE, an existing table of the same name will be overwritten. This argument doesn’t change behavior if the table does not exist yet.

R：导出信息

library(DBI)
# 用 guest 账户登录连接数据库 demo
con <- DBI::dbConnect(RMySQL::MySQL(), dbname = 'testdb', host = "localhost", port = 3306, user = "guest", password = "guest123")
# 返回查询结果
table_desc <- dbGetQuery(con, "SHOW columns FROM mtcars")
# 转化为 md 表格
knitr::kable(table_desc[, c('Field', 'Type')], format = 'markdown', row.names = F)
# 写入/上传到mysql上，其中file_name就是文件名，file_content就是表格内容
dbWriteTable(con, file_name, file_content, append=F,row.names=F)

一些核心命令

DBI 是一个用于数据库连接的基础包，

表的连接 dbConnect()/dbDisconnect()
创建 dbCreateTable()
读 dbReadTable()
写 dbWriteTable()
删除 dbRemoveTable()
查 dbSendQuery()/dbGetQuery()：继续深入查看表是否存在 dbExistsTable()、表的各个字段 dbListFields()、各个字段的存储类型 dbDataType() 等

最后，为啥要费劲用mysql导入导出表？

因为要各司其职，协同合作

MySQL的优势就是可以存储比较大（成千上万）的数据库和表，这些内容在网页工具中，不适合放在R中去读取，因为R中的单纯读取都要耗费大量的时间。而且可能需要读取多次，或者需要同时用到多个表，因此会造成流程上的效率低下。一般来讲，MySQL主要负责数据库系统管理，优化流程；而R用于具体的对其中的每个表进行分析、统计，得出结果。

mysql web