R: R 数据库连接

Q: 写入数据库用 dbWriteTable 还是 SQL？

dbWriteTable(con, "table", df, overwrite = TRUE) 简单方便；复杂写入用 dbExecute(con, "INSERT...") 或参数化 SQL。

Q: 怎么避免连接泄漏？

用 on.exit(dbDisconnect(con)) 函数结束自动断开；或用 pool 包的连接池（生产推荐）。

最后更新：2026-08-26

前面 3 课我们学了文件型数据（CSV/Excel/JSON）。但企业级数据 90% 在数据库里——MySQL、PostgreSQL、SQL Server、Oracle。这一课我们学 R 怎么连接数据库、用 SQL 查数据、写入数据框到数据库。

读完这一课你就能用 R 查百万行数据库表，并把分析结果写回数据库。

1. 你将学到

数据库基础（关系型数据库、SQL）
DBI 接口规范与 odbc 驱动
RSQLite 本地数据库（无需服务器）
连接 MySQL/PostgreSQL/SQL Server
dbGetQuery / dbExecute / dbReadTable
dbWriteTable 写入数据框
dplyr 自动翻译 SQL
生产实践：连接池、断开连接

2. 一个数据分析师的故事

(1) 痛点：数据在数据库里

Bob是分析师，需要从公司 MySQL 拉取数据：

SQL

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 100;

他在 Navicat 里跑 SQL → 导出 CSV → 用 R 读 CSV。每次都要折腾 5 分钟。如果直接用 R 连数据库——

(2) R 的解法

library(DBI)
library(RMySQL)

# 1. 连接数据库
con <- dbConnect(RMySQL::MySQL(),
                 dbname = "shop",
                 host = "localhost",
                 user = "root",
                 password = "secret")

# 2. 直接跑 SQL
result <- dbGetQuery(con, "
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY customer_id
  ORDER BY total DESC
  LIMIT 100
")

# 3. 或用 dplyr 翻译 SQL
library(dplyr)
result2 <- tbl(con, "orders") |>
  filter(order_date >= "2024-01-01") |>
  group_by(customer_id) |>
  summarise(total = sum(amount)) |>
  arrange(desc(total)) |>
  collect()  # 拉到 R 内存

# 4. 断开
dbDisconnect(con)

1 行连接 + 1 行 SQL。这就是 R 数据库连接的威力。

3. R 数据库生态

(1) 4 个核心包

包	作用	依赖
DBI	数据库接口规范（统一 API）	纯 R
RSQLite	SQLite 驱动（本地数据库）	纯 R（无需服务器）
RMySQL / RMariaDB	MySQL/MariaDB 驱动	需 MySQL 客户端库
RPostgreSQL	PostgreSQL 驱动	需 PostgreSQL 客户端库
odbc	ODBC 通用接口	需安装 ODBC 驱动
dbplyr	dplyr SQL 翻译	DBI

(2) 安装

# 1. 核心接口（必装）
install.packages("DBI")

# 2. 本地数据库（无需服务器，推荐新手用）
install.packages("RSQLite")

# 3. 生产数据库（按需）
install.packages("RMySQL")        # MySQL
install.packages("RMariaDB")      # MariaDB（推荐替代 RMySQL）
install.packages("RPostgreSQL")   # PostgreSQL
install.packages("odbc")          # 通用 ODBC

4. RSQLite：本地数据库（推荐入门）

(1) 什么是 SQLite？

graph LR
    A["SQLite"] --> B["无服务器"]
    A --> C["单文件存储"]
    A --> D["嵌入式"]
    A --> E["零配置"]
    A --> F["Python/R/Excel 都能读"]
    
    style A fill:#d4edda
    style B fill:#cce5ff
    style C fill:#f8d7da
    style D fill:#e1d4ff

SQLite = 文件型数据库（一个文件就是一个库），无需服务器、零配置。手机、iPhone、Android、Python、R 内置都用 SQLite。

(2) 创建/连接数据库

library(DBI)
library(RSQLite)

# 1. 创建/连接（文件不存在自动创建）
con <- dbConnect(SQLite(), "my_database.db")

# 2. 断开连接（重要！用完必断）
dbDisconnect(con)

# 3. 临时数据库（内存中，重启丢失）
con <- dbConnect(SQLite(), ":memory:")

(3) 写入数据框

# 准备数据
sales <- data.frame(
  id = 1:5,
  product = c("A", "B", "A", "C", "B"),
  amount = c(100, 200, 150, 300, 250)
)

con <- dbConnect(SQLite(), "shop.db")

# 写入表（覆盖：overwrite / 追加：append）
dbWriteTable(con, "sales", sales, overwrite = TRUE)

# 验证
dbListTables(con)
# [1] "sales"

(4) 读数据

# 方式 1：读整个表
df <- dbReadTable(con, "sales")
print(df)

# 方式 2：执行 SQL
result <- dbGetQuery(con, "SELECT * FROM sales WHERE amount > 150")
print(result)

# 方式 3：dplyr（懒查询，最后 collect）
library(dplyr)
result2 <- tbl(con, "sales") |>
  filter(amount > 150) |>
  collect()

(5) 其他操作

# 1. 列出所有表
dbListTables(con)
# [1] "sales" "products" "customers"

# 2. 表是否存在
dbExistsTable(con, "sales")
# [1] TRUE

# 3. 删除表
dbRemoveTable(con, "sales")

# 4. 查看表字段
dbListFields(con, "sales")
# [1] "id" "product" "amount"

# 5. 提交事务
dbCommit(con)
dbRollback(con)

5. 连接生产数据库

(1) MySQL/MariaDB

# MySQL
library(RMySQL)
con <- dbConnect(MySQL(),
                 dbname = "shop",
                 host = "localhost",
                 port = 3306,
                 user = "root",
                 password = "your_password")

# MariaDB（推荐，开源）
library(RMariaDB)
con <- dbConnect(MariaDB(),
                 dbname = "shop",
                 host = "localhost",
                 port = 3306,
                 user = "root",
                 password = "your_password")

(2) PostgreSQL

library(RPostgreSQL)
con <- dbConnect(PostgreSQL(),
                 dbname = "shop",
                 host = "localhost",
                 port = 5432,
                 user = "postgres",
                 password = "your_password")

(3) ODBC（连接 SQL Server/Oracle）

library(odbc)
con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "localhost",
                 Database = "shop",
                 UID = "sa",
                 PWD = "your_password")

⚠️ 注意：生产数据库需要先安装对应数据库的客户端库（如 MySQL 需要 libmysqlclient-dev）。

6. dbplyr：dplyr 自动翻译 SQL

(1) 核心思路

graph LR
    A["dplyr 链式操作"] --> B["dbplyr 翻译成 SQL"]
    B --> C["数据库执行"]
    C --> D["collect 拉回 R 内存"]
    
    style A fill:#cce5ff
    style B fill:#d4edda
    style C fill:#f8d7da
    style D fill:#fff3cd

(2) 实战

library(dplyr)
library(dbplyr)  # 加载翻译器

# 1. 创建"懒"表（不真查数据库）
orders <- tbl(con, "orders")

# 2. 写 dplyr 代码（不会立即执行）
query <- orders |>
  filter(order_date >= "2024-01-01", amount > 100) |>
  group_by(customer_id) |>
  summarise(
    total = sum(amount),
    n_orders = n()
  ) |>
  arrange(desc(total)) |>
  head(100)

# 3. 查看翻译的 SQL
query |> show_query()
# SELECT `customer_id`, SUM(`amount`) AS `total`, COUNT(*) AS `n_orders`
# FROM `orders`
# WHERE (`order_date` >= '2024-01-01') AND (`amount` > 100.0)
# GROUP BY `customer_id`
# ORDER BY `total` DESC
# LIMIT 100

# 4. 拉到 R 内存
result <- query |> collect()

💡 提示：tbl() + dplyr + collect() 是数据库分析的黄金组合——不用手写 SQL，R 代码自动翻译成 SQL 在数据库执行。

(3) 性能优势

数据库执行 vs 拉到 R 内存：

数据量	拉到 R	在数据库执行
@,000 rows of	0.1s	0.1s（没差异）
@,000 rows of	10s	0.5s（数据库有索引）
1 亿行	卡死	5s（数据库算力）

💡 提示：大表分析永远用 tbl() + collect()——只把结果拉回 R，不拉全表。

7. 写入数据框到数据库

(1) dbWriteTable

# 1. 创建/覆盖表
dbWriteTable(con, "sales_summary", summary_df, overwrite = TRUE)

# 2. 追加到现有表
dbWriteTable(con, "sales_log", new_data, append = TRUE)

# 3. 临时表（会话结束自动删除）
dbWriteTable(con, "temp_data", df, temporary = TRUE)

# 4. 行名处理
dbWriteTable(con, "df", df, row.names = FALSE)  # 不写入行名

(2) 实战

# 读 R 数据 → 清洗 → 写回数据库
library(dplyr)
library(readr)

# 1. 读 CSV
sales <- read_csv("sales.csv")

# 2. 清洗
clean_sales <- sales |>
  filter(!is.na(amount)) |>
  mutate(date = as.Date(date)) |>
  group_by(region, product) |>
  summarise(total = sum(amount))

# 3. 写回数据库
con <- dbConnect(SQLite(), "shop.db")
dbWriteTable(con, "sales_by_region_product", clean_sales, overwrite = TRUE)

# 4. 验证
result <- dbGetQuery(con, "SELECT * FROM sales_by_region_product LIMIT 5")
print(result)

8. 生产实践

(1) 连接配置最佳实践

# 1. 用 .Renviron 存密码（不写在代码里）
# 在 ~/.Renviron 添加：
# DB_PASSWORD=your_password
password <- Sys.getenv("DB_PASSWORD")

# 2. 连接配置封装
db_connect <- function() {
  dbConnect(RMariaDB::MariaDB(),
            dbname = "shop",
            host = Sys.getenv("DB_HOST", "localhost"),
            port = as.integer(Sys.getenv("DB_PORT", 3306)),
            user = Sys.getenv("DB_USER", "root"),
            password = Sys.getenv("DB_PASSWORD"))
}

# 3. 用 withConnection 模式
con <- db_connect()
on.exit(dbDisconnect(con))  # 函数结束自动断开

(2) 错误处理

# 1. 简单 tryCatch
result <- tryCatch(
  {
    con <- dbConnect(SQLite(), "shop.db")
    dbGetQuery(con, "SELECT * FROM sales LIMIT 10")
  },
  error = function(e) {
    message("数据库错误：", e$message)
    NULL
  },
  finally = {
    if (exists("con") && !is.null(con)) dbDisconnect(con)
  }
)

# 2. 检查连接
if (dbIsValid(con)) {
  cat("连接正常\n")
} else {
  cat("连接已断开\n")
}

(3) 批量操作

# 1. 批量插入（事务）
dbBegin(con)
for (chunk in split(data, ceiling(seq_len(nrow(data)) / 1000))) {
  dbWriteTable(con, "big_table", chunk, append = TRUE)
}
dbCommit(con)

# 2. 进度条
library(progress)
pb <- progress_bar$new(total = nrow(data))
for (i in seq_len(nrow(data))) {
  dbExecute(con, "INSERT INTO log VALUES (?, ?)", params = list(data$id[i], data$msg[i]))
  pb$tick()
}

9. 完整示例：SQLite 销售数据库分析

下面是一个完整工作流示例，把本课所有知识串起来。

▶ 示例：本地销售数据库完整分析

R 📖 仅展示

# ============================================
# 本地销售数据库完整分析
# 功能：用 RSQLite 建库、查数据、分析
# ============================================

library(DBI)
library(RSQLite)
library(dplyr)

# 1. 创建数据库 + 写初始数据
con <- dbConnect(SQLite(), "shop_demo.db")
dbWriteTable(con, "customers", data.frame(
  id = 1:5,
  name = c("Alice", "Bob", "Charlie", "Diana", "Eve"),
  city = c("北京", "上海", "广州", "北京", "深圳"),
  register_date = as.Date("2023-01-01") + c(0, 30, 60, 90, 120)
), overwrite = TRUE)

dbWriteTable(con, "orders", data.frame(
  id = 1:20,
  customer_id = sample(1:5, 20, replace = TRUE),
  order_date = as.Date("2024-01-01") + sample(0:90, 20),
  amount = sample(100:2000, 20)
), overwrite = TRUE)

cat("=== 表结构 ===\n")
print(dbListTables(con))

# 2. SQL 查询：每个客户的总订单额
cat("\n=== SQL 查询（客户总订单）===\n")
result_sql <- dbGetQuery(con, "
  SELECT c.name, c.city, COUNT(o.id) AS n_orders, SUM(o.amount) AS total
  FROM customers c
  LEFT JOIN orders o ON c.id = o.customer_id
  GROUP BY c.id
  ORDER BY total DESC
")
print(result_sql)

# 3. 同样的查询用 dplyr（dbplyr 翻译 SQL）
cat("\n=== dplyr 查询（自动翻译 SQL）===\n")
customers_tbl <- tbl(con, "customers")
orders_tbl <- tbl(con, "orders")

result_dplyr <- customers_tbl |>
  left_join(orders_tbl, by = c("id" = "customer_id")) |>
  group_by(id, name, city) |>
  summarise(
    n_orders = n(),
    total = sum(amount)
  ) |>
  arrange(desc(total)) |>
  collect()

print(result_dplyr)

# 4. 复杂分析：每月销售趋势
cat("\n=== 每月销售趋势 ===\n")
monthly_sales <- orders_tbl |>
  mutate(month = format(order_date, "%Y-%m")) |>
  group_by(month) |>
  summarise(
    orders = n(),
    revenue = sum(amount),
    avg_amount = round(mean(amount), 2)
  ) |>
  collect()
print(monthly_sales)

# 5. 找出高价值客户
cat("\n=== 高价值客户（订单额 > 2000）===\n")
vip_customers <- customers_tbl |>
  left_join(orders_tbl, by = c("id" = "customer_id")) |>
  group_by(id, name, city) |>
  summarise(total = sum(amount, na.rm = TRUE)) |>
  filter(total > 2000) |>
  arrange(desc(total)) |>
  collect()
print(vip_customers)

# 6. 把分析结果写回数据库
dbWriteTable(con, "vip_customers", vip_customers, overwrite = TRUE)
dbWriteTable(con, "monthly_sales", monthly_sales, overwrite = TRUE)
cat("\n=== 报表已写回数据库 ===\n")
print(dbListTables(con))

# 7. 验证写回的表
cat("\n=== 验证 vip_customers ===\n")
vip_reloaded <- dbReadTable(con, "vip_customers")
print(vip_reloaded)

# 8. 断开连接
dbDisconnect(con)
cat("\n=== 数据库已断开 ===\n")

# 9. 清理文件
file.remove("shop_demo.db")

逻辑代码 70 行（超过 40 行限制，仅展示）

预期输出（节选）：

TEXT 📖 仅展示

=== SQL 查询（客户总订单）===
     name city n_orders total
1    Eve   深圳        5  5180
2  Diana   北京        5  4643
3 Charlie 广州        4  4156
4    Bob   上海        3  3461
5  Alice   北京        3  3033

=== 每月销售趋势 ===
    month orders revenue avg_amount
1  2024-01      4     5239    1309.75
2  2024-02      5     6491    1298.20
...

❓ 常见问题

Q 怎么连远程 MySQL？

A dbConnect(RMariaDB::MariaDB(), dbname, host, user, password)。密码用环境变量 Sys.getenv("DB_PASSWORD") 避免硬编码。

Q 写入数据库用 dbWriteTable 还是 SQL？

A dbWriteTable(con, "table", df, overwrite = TRUE) 简单方便；复杂写入用 dbExecute(con, "INSERT...") 或参数化 SQL。

Q 怎么避免连接泄漏？

A 用 on.exit(dbDisconnect(con)) 函数结束自动断开；或用 pool 包的连接池（生产推荐）。

📖 小节

DBI 是 R 数据库接口规范；具体驱动（RSQLite/RMariaDB/RPostgreSQL）实现该规范
RSQLite 推荐入门——无需服务器、纯 R、文件型数据库（一个 .db 文件）
5 个核心函数：dbConnect 连接 / dbDisconnect 断开 / dbGetQuery 查 SQL / dbWriteTable 写 / dbReadTable 读整表
dbplyr 是杀手锏：tbl(con, "table") + dplyr + collect() 让 R 代码自动翻译 SQL
写入用 dbWriteTable(con, "name", df, overwrite = TRUE) 覆盖 / append = TRUE 追加
生产实践：密码用 Sys.getenv()、事务 dbBegin/dbCommit/dbRollback、on.exit 断开连接
大表分析永远用 tbl() + collect()——只在数据库算，不拉到 R 内存

📝 作业

基础题：用 RSQLite 创建一个本地数据库 test.db，写入 1 个数据框（5 行 3 列），用 dbReadTable 读回验证数据完整。
基础题：对上题的数据库执行 3 个 SQL 查询：① SELECT * FROM table ② SELECT COUNT(*) FROM table ③ SELECT col, COUNT(*) FROM table GROUP BY col。
基础题：用 dbExecute 创建 1 个新表（含 id/name/age 字段），用 dbWriteTable 写入 5 行数据，用 dbRemoveTable 删除。验证每个操作。
进阶题：模拟销售数据库（customers + orders 两表），用 tbl() + dplyr + collect() 完成：① 找出订单数 > 3 的客户 ② 按月统计销售额 ③ 找出消费最高的 3 个客户。截图保存。
挑战题：用 dbplyr 把一段复杂 dplyr 操作翻译成 SQL：① 多个 filter ② group_by + summarise ③ arrange + head ④ inner_join 两表。用 show_query() 查看 SQL 字符串，验证与手写 SQL 一致。

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

R: R 数据库连接

1. 你将学到

2. 一个数据分析师的故事

(1) 痛点：数据在数据库里

(2) R 的解法

3. R 数据库生态

(1) 4 个核心包

(2) 安装

4. RSQLite：本地数据库（推荐入门）

(1) 什么是 SQLite？

(2) 创建/连接数据库

(3) 写入数据框

(4) 读数据

(5) 其他操作

5. 连接生产数据库

(1) MySQL/MariaDB

(2) PostgreSQL

(3) ODBC（连接 SQL Server/Oracle）

6. dbplyr：dplyr 自动翻译 SQL

(1) 核心思路

(2) 实战

(3) 性能优势

7. 写入数据框到数据库

(1) dbWriteTable

(2) 实战

8. 生产实践

(1) 连接配置最佳实践

(2) 错误处理

(3) 批量操作

9. 完整示例：SQLite 销售数据库分析

▶ 示例：本地销售数据库完整分析

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好