PricingLoginStart free
R Academy · 课时

连接 PostgreSQL 与 MySQL

使用 RPostgres 和 RMySQL 驱动连接基于服务器的数据库

第 2 / 4 课13 个步骤

连接 PostgreSQL 与 MySQL 是 CoddyKit 上的免费 R Academy 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 R Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 R Academy 课程共包含 4 节课。

生产数据库连接

RSQLite 非常适合开发和测试，但生产数据通常位于 PostgreSQL、MySQL、SQL Server 或云数据库中。DBI 接口保持不变，只有驱动软件包和连接参数需要改变。这正是 DBI 的主要优势。

library(DBI)

# Driver packages for each database:
# PostgreSQL  -> RPostgres::Postgres()
# MySQL       -> RMySQL::MySQL() or RMariaDB::MariaDB()
# SQL Server  -> odbc::odbc()
# BigQuery    -> bigrquery::bigquery()
# Redshift    -> RPostgres::Postgres()  (same protocol)
# DuckDB      -> duckdb::duckdb()

# All use the same DBI functions:
# dbConnect, dbGetQuery, dbExecute, dbDisconnect

连接到 PostgreSQL

RPostgres::Postgres() 是 PostgreSQL 的驱动。请传入连接参数：host、port（默认值为 5432）、dbname、user 和 password。切勿将凭据硬编码，应改用环境变量。

library(DBI)
# library(RPostgres)  # Uncomment when available

# PostgreSQL connection pattern:
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'db.example.com',
#   port     = 5432,
#   dbname   = 'analytics',
#   user     = 'analyst',
#   password = 'secretpassword'
# )

# Query exactly like SQLite:
# result <- dbGetQuery(con, 'SELECT * FROM sales LIMIT 5')
# dbDisconnect(con)

cat('PostgreSQL uses the same DBI interface as SQLite!')

使用环境变量保存凭据

将密码硬编码在代码中会带来安全风险。请将凭据存储为环境变量，并使用 Sys.getenv() 读取。在生产环境中，可以在 .Renviron、.env 文件（不要提交到 git）或秘密管理系统中设置这些变量。

library(DBI)

# Set environment variables (normally done outside R):
# In .Renviron file:
# DB_HOST=db.example.com
# DB_NAME=analytics
# DB_USER=analyst
# DB_PASS=secretpassword

# Read credentials from environment
get_pg_connection <- function() {
  dbConnect(
    RPostgres::Postgres(),  # driver
    host     = Sys.getenv('DB_HOST'),
    port     = as.integer(Sys.getenv('DB_PORT', '5432')),
    dbname   = Sys.getenv('DB_NAME'),
    user     = Sys.getenv('DB_USER'),
    password = Sys.getenv('DB_PASS')
  )
}

cat('Sys.getenv() reads environment variables safely.')
cat('\nDB_HOST value:', nchar(Sys.getenv('DB_HOST')), 'chars')

连接到 MySQL / MariaDB

MySQL 连接使用 RMariaDB::MariaDB()（现代 MySQL 驱动）或 RMySQL::MySQL()。连接参数与 PostgreSQL 类似。对于 MySQL 和 MariaDB 服务器，建议使用 MariaDB 驱动。

library(DBI)

# MySQL / MariaDB connection pattern:
# library(RMariaDB)
# con <- dbConnect(
#   RMariaDB::MariaDB(),
#   host     = Sys.getenv('MYSQL_HOST'),
#   port     = 3306,
#   dbname   = Sys.getenv('MYSQL_DB'),
#   user     = Sys.getenv('MYSQL_USER'),
#   password = Sys.getenv('MYSQL_PASS')
# )

# SSL connection:
# con <- dbConnect(
#   RMariaDB::MariaDB(),
#   host = 'secure-db.example.com',
#   ssl.ca = '/path/to/ca-cert.pem'
# )

cat('RMariaDB supports both MySQL and MariaDB servers.')

连接超时与重新连接

运行时间较长的脚本可能会遇到连接超时。请使用 dbIsValid(con) 检查连接是否仍然有效。对于 ETL 脚本，可以考虑在每个处理批次开始时重新连接，而不是让一个连接持续打开数小时。

library(DBI)
library(RSQLite)

# Safe function with reconnection
query_with_check <- function(con, sql) {
  if (!dbIsValid(con)) {
    stop('Connection is no longer valid. Reconnect.')
  }
  dbGetQuery(con, sql)
}

# Demonstrate with SQLite
con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 't', data.frame(x=1:3))

cat('Connection valid:', dbIsValid(con), '\n')
print(query_with_check(con, 'SELECT * FROM t'))

dbDisconnect(con)
cat('After disconnect, valid:', dbIsValid(con))

连接池概念

每次查询都新建数据库连接的成本很高。连接池会维护一组打开的连接并重复使用它们。对于每秒会收到大量请求的 Web API 和 Shiny 应用，这一点至关重要。

# The 'pool' package provides connection pooling for R
# library(pool)

# Create a pool (connections managed automatically)
# my_pool <- pool::dbPool(
#   drv      = RPostgres::Postgres(),
#   dbname   = Sys.getenv('DB_NAME'),
#   host     = Sys.getenv('DB_HOST'),
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS'),
#   minSize  = 2,   # Always keep 2 connections ready
#   maxSize  = 10   # Maximum 10 simultaneous connections
# )

# Use the pool like a regular connection:
# result <- dbGetQuery(my_pool, 'SELECT * FROM table')

# Close the pool on shutdown:
# pool::poolClose(my_pool)

cat('pool package: connection reuse for high-traffic apps!')

使用 SQL 参数查询（安全方式）

切勿将用户输入直接拼接到 SQL 字符串中（存在 SQL 注入风险）。请使用 sqlInterpolate(con, sql, .dots=list(...)) 这样的参数化查询，或使用 glue 软件包中的 glue_sql() 安全地插入值。

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
  data.frame(id=1:4, name=c('Alice','Bob','Carol','Dave'), score=c(85,92,78,88)))

# UNSAFE (SQL injection possible):
# name_input <- 'Alice' # imagine user-provided
# dbGetQuery(con, paste('SELECT * FROM students WHERE name =', name_input))

# SAFE: use sqlInterpolate
name_input <- 'Alice'
safe_sql <- sqlInterpolate(con,
  'SELECT * FROM students WHERE name = ?name',
  name = name_input
)
print(dbGetQuery(con, safe_sql))
dbDisconnect(con)

通过 SSH 隧道访问远程数据库

许多生产数据库无法直接从互联网访问。一种常见做法是使用 SSH 隧道：通过 SSH 将本地端口转发到远程数据库端口，然后让 R 连接到 localhost:local_port。

# SSH tunnel setup (run in terminal before connecting from R):
# ssh -N -L 5433:db-server.internal:5432 user@jump-host.example.com

# Then connect in R as if the DB is local:
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'localhost',
#   port     = 5433,   # Local forwarded port
#   dbname   = 'analytics',
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS')
# )

# You can also script the tunnel with:
# system('ssh -fN -L 5433:db:5432 user@jump-host')

cat('SSH tunneling makes private databases accessible to R.')

分块读取大型表

当数据库表包含数百万行时，一次性加载全部数据可能耗尽内存。请使用 LIMIT/OFFSET 或基于游标的获取方式，分块处理该表。结合 dbFetch() 可以流式获取结果。

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'big_table', data.frame(id=1:100, value=rnorm(100)))

# Process in chunks of 25 rows
chunk_size <- 25
offset <- 0
total_processed <- 0

repeat {
  chunk <- dbGetQuery(con, sprintf(
    'SELECT * FROM big_table LIMIT %d OFFSET %d',
    chunk_size, offset
  ))
  if (nrow(chunk) == 0) break
  total_processed <- total_processed + nrow(chunk)
  offset <- offset + chunk_size
}

cat('Total rows processed:', total_processed, '\n')
dbDisconnect(con)

dbGetInfo() — 连接元数据

dbGetInfo(con) 返回连接元数据：服务器版本、数据库名称、用户等。对于需要连接多个环境的脚本，这有助于记录日志、进行诊断，并验证您是否连接到了正确的数据库实例。

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

info <- dbGetInfo(con)
print(info)

# For PostgreSQL, info would include:
# $host, $port, $dbname, $username, $server.version

dbDisconnect(con)

比较 SQLite 与 PostgreSQL 的使用场景

原型开发、嵌入式数据和测试适合使用 SQLite；生产环境、多用户访问和高级 SQL 功能适合使用 PostgreSQL。DBI 代码几乎完全相同——唯一的区别在于驱动程序和连接参数。

library(DBI)
library(RSQLite)

# SQLite (development/testing)
con_dev <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con_dev, 'data', mtcars)
dev_result <- dbGetQuery(con_dev, 'SELECT COUNT(*) n FROM data')
cat('SQLite dev DB rows:', dev_result$n, '\n')
dbDisconnect(con_dev)

# PostgreSQL (production) — same interface:
# con_prod <- dbConnect(RPostgres::Postgres(),
#   host=Sys.getenv('PG_HOST'), dbname=Sys.getenv('PG_DB'),
#   user=Sys.getenv('PG_USER'), password=Sys.getenv('PG_PASS'))
# prod_result <- dbGetQuery(con_prod, 'SELECT COUNT(*) n FROM data')
# dbDisconnect(con_prod)

cat('Same DBI code works for both!')

快速检查

在 R 脚本中提供数据库凭据的推荐方式是什么？

在交互式课程中回答

回顾：PostgreSQL 和 MySQL 连接

生产环境数据库连接的要点：

  • PostgreSQL 驱动程序：使用带有 host、port、dbname、user、password 参数的 RPostgres::Postgres()
  • MySQL/MariaDB 驱动程序：使用带有类似参数的 RMariaDB::MariaDB()
  • 始终使用 Sys.getenv('VAR') 获取凭据——绝不要将凭据硬编码
  • dbIsValid(con) 用于检查连接是否仍然有效
  • pool packages 用于管理高流量应用的连接池
  • sqlInterpolate() 可使用用户提供的值防止 SQL 注入
  • SSH 隧道：将本地端口转发出去，以访问私有数据库
  • 所有 DBI 函数在不同后端上的工作方式都相同
library(DBI)
library(RSQLite)

# Production-ready connection pattern (SQLite for demo)
open_connection <- function() {
  con <- dbConnect(RSQLite::SQLite(), ':memory:')
  if (!dbIsValid(con)) stop('Failed to connect')
  cat('Connected successfully\n')
  con
}

run_query <- function(con, sql) {
  if (!dbIsValid(con)) stop('Connection lost')
  dbGetQuery(con, sql)
}

con <- open_connection()
dbWriteTable(con, 't', data.frame(x=1:3, y=4:6))
print(run_query(con, 'SELECT * FROM t'))
dbDisconnect(con)
免费开始

用 AI 导师学习 R — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程
43
课程
159
免费开始学习查看完整课程

常见问题解答

「连接 PostgreSQL 与 MySQL」课时是免费的吗？

是的 — 「连接 PostgreSQL 与 MySQL」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 R Academy 课程的其余内容，请升级到 CoddyKit PRO。 R Academy 课程共包含 4 节课。

「连接 PostgreSQL 与 MySQL」这节课中我会学到什么？

使用 RPostgres 和 RMySQL 驱动连接基于服务器的数据库 你通过在浏览器中直接运行的动手代码来练习 R Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 R Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 R Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。

「连接 PostgreSQL 与 MySQL」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 R Academy 课中编写并运行代码吗？

能。每节 R Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. DBI 与 RSQLite 基础
  2. 连接 PostgreSQL 与 MySQL
  3. dbplyr：使用 dplyr 语法执行 SQL
  4. 参数化查询与事务
← 返回 R Academy