如何通过RSQLite实现SQLite数据库的更新与插入？

先搞定核心报错：添加主键/唯一约束 #

你碰到的ON CONFLICT报错，本质是rows_upsert()得靠SQLite的冲突处理机制，而这个机制要求目标表必须有主键或唯一约束。如果你的df_test表还没设置，得先补上：

library(RSQLite)
library(dplyr)

# 连接硬盘上的数据库
con <- dbConnect(SQLite(), path = "your_database_file.db")

# 给表加主键（假设用来匹配行的列是`id`，替换成你实际的列名）
dbExecute(con, "ALTER TABLE df_test ADD PRIMARY KEY (id);")

# 要是表已经有数据没法直接加主键，就换个方式：新建带约束的表迁移数据
# dbExecute(con, "CREATE TABLE df_test_new AS SELECT * FROM df_test;")
# dbExecute(con, "ALTER TABLE df_test_new ADD PRIMARY KEY (id);")
# dbExecute(con, "DROP TABLE df_test;")
# dbExecute(con, "ALTER TABLE df_test_new RENAME TO df_test;")

dbDisconnect(con)

方案1：用dplyr直接在数据库层面执行Upsert #

约束加好后，就能用rows_upsert()直接操作数据库，不用把5亿行全拉到内存里：

con <- dbConnect(SQLite(), path = "your_database_file.db")

# 假设你的待更新/插入数据是new_data（替换成你实际的数据框）
new_data <- tibble(id = c(1, 2, 3), value = c(10, 20, 30))

# 直接在数据库里执行upsert，in_place=TRUE表示修改原表
rows_upsert(
  x = tbl(con, "df_test"),  # 连接到数据库里的表
  y = new_data,             # 待插入/更新的数据
  by = "id",                # 指定用来匹配行的主键/唯一列
  in_place = TRUE
)

dbDisconnect(con)

方案2：用SQLite原生语句手动实现Upsert #

不想依赖dplyr的话，直接用SQLite的INSERT OR REPLACE或INSERT OR UPDATE语法，通过dbExecute()执行，效率也很高：

con <- dbConnect(SQLite(), path = "your_database_file.db")

# 单条数据的upsert
dbExecute(con, "
  INSERT OR REPLACE INTO df_test (id, value)
  VALUES (?, ?)",
  params = list(1, 100)  # 替换成你的数据
)

# 批量upsert的话，用参数化查询更高效
batch_data <- data.frame(id = c(4,5), value = c(40,50))
stmt <- dbSendStatement(con, "
  INSERT OR REPLACE INTO df_test (id, value)
  VALUES (:id, :value)")
dbBind(stmt, batch_data)  # 绑定批量数据
dbClearResult(stmt)

dbDisconnect(con)

针对大数据集的优化建议 #

• 分批次处理：5亿行数据别一次性操作，把待处理数据分成若干小批次（比如每批10万行）循环执行，避免内存溢出。
• 事务包裹：批量操作时用事务，能大幅提升速度：

dbBegin(con)  # 开启事务
# 这里放你的批量upsert代码
dbCommit(con) # 提交事务

内容的提问来源于stack exchange，提问作者Philipp Schulz