r语言连接mysql增删改查_R语言--操作MySQL数据库

用R语言做数据分析时，常常需要从多种数据源取数据，其中数据库是非常常见的数据源。用R操作MySQL数据库，可以说是数据分析师必备的技能了，本文介绍RMySQL包，可以在R语言中对数据库进行增删改查的操作。

软件版本

win10 64bit

r3.6.1

rstudio 1.2

RMySQL 0.10.20

安装包

install.packages('RMySQL')

创建连接

用dbConnect函数创建连接，驱动类型设置为MySQL()，用户名user、密码password、主机host、端口port、数据库dbname这些参数需要根据实际情况修改，其中端口port默认值是3306。

# 加载包

library(RMySQL)

# 创建连接

con

还可以通过一些命令，查看连接的相关信息。

# 连接概述

summary(con)

## <0>

## User: root

## Host: 192.168.1.244

## Dbname: test

## Connection type: 192.168.1.244 via TCP/IP

##

## Results:

0>

# 连接详情

dbGetInfo(con)

## $host

## [1] "192.168.1.244"

##

## $user

## [1] "root"

##

## $dbname

## [1] "test"

##

## $conType

## [1] "192.168.1.244 via TCP/IP"

##

## $serverVersion

## [1] "5.7.23"

##

## $protocolVersion

## [1] 10

##

## $threadId

## [1] 465075

##

## $rsId

## list()

# 列出连接下所有表

dbListTables(con)

## [1] "city" "result"

## [3] "t_json" "tb_newChiller"

创建表

dbCreateTable函数创建表

# 方法1：字符向量

dbCreateTable(con,'table1',fields =c("id"='int',"value"="float"))

# 方法2：数据框

dbCreateTable(con,'table2',fields = mtcars)

注意点：

设置fields参数时，用命名的字符向量(名字为字段名，值为字段数据类型)或数据框表示

表只能创建一次，重复创建会报错。

sql语句创建表

运行dbSendQuery函数，调用sql语句创建表,该函数返回一个RMySQLResult类。该方法可以重复运行，不会报错。

# 方法3：sql语句

dbSendQuery(con,'create table if not exists table3(id int,value float)')

## <11>

11>

读取表

dbReadTable函数读取表

# 先设置编码(windows)

dbSendQuery(con,'set names gbk')

## <11>

11>

# 读取完整表

dbReadTable(con,'city')

## id name city_code

## 1 1 北京 000001

## 2 4 南京 000002

## 3 7 广州 000003

## 4 10 上海 000004

## 5 11 深圳 000005

## 6 13 苏州 000006

注意点：

返回的是整个表的数据

为解决中文显示问题，若运行R的操作系统为window，需要设置编码为gbk，为Linux则需要设置为utf8

返回的数据格式为data.frame

sql语句读取表

通过运行dbGetQuery或dbSendQuery函数，调用sql语句读取表。

dbGetQuery函数运行sql语句，返回data.frame。可以在sql语句中编写筛选语句。

# 读取完整表

dbGetQuery(con,'select * from city')

## id name city_code

## 1 1 北京 000001

## 2 4 南京 000002

## 3 7 广州 000003

## 4 10 上海 000004

## 5 11 深圳 000005

## 6 13 苏州 000006

# 读取筛选的表

dbGetQuery(con,'select id,name from city where id > 10')

## id name

## 1 11 深圳

## 2 13 苏州

dbSendQuery函数运行sql语句，返回MySQLResult类。对该类使用dbFetch函数，可以筛选行，返回data.frame。用完后需要用dbClearResult函数清理结果。

# 筛选前3行数据

res

## id name city_code

## 1 1 北京 000001

## 2 4 南京 000002

## 3 7 广州 000003

dbClearResult(res)

## [1] TRUE

写入表

dbWriteTable函数写入表

# 写入表

dbWriteTable(con,"iris",iris,row.names=FALSE,append=TRUE)

## [1] TRUE

注意点：

第一次写入时，若数据库中无该表，会根据表名自动创建

重复写入时，需要设置写入行为：

参数设置

解释

append=TRUE

追加写入，新数据在原数据行末添加

overwrite=TRUE

覆盖写入，新数据覆盖原数据

row.names=FALSE

不写入数据框的行名

append和overwrite两个参数不能同时设置为TRUE。

sql语句插入数据

运行dbSendStatment函数，调用sql语句插入数据。

dbSendStatement(con,'insert into table1 values(1,12)')

## <1701147206>

1701147206>

注意点：

重复运行会重复插入数据

删除表

用dbRemoveTable函数删除表。

# 删除表

dbRemoveTable(con,'table1')

## [1] TRUE

dbRemoveTable(con,'table2')

## [1] TRUE

dbRemoveTable(con,'table3')

## [1] TRUE

dbRemoveTable(con,'iris')

## [1] TRUE

完成所有的操作后，需要关闭连接。

# 关闭连接

dbDisconnect(con)

## [1] TRUE