如何使用RMySQL包更新MySQL表

Instead of append or overwrite an entire table to database, is there any function in RMySQL that can update only part of the table? Because some of our data could be imcomplete someday, that I need to remain the old table and only replace the rows that has new data coming in, here is the function I wrote, but did not work, any help would be appreciated:

而不是将整个表追加或覆盖到数据库,RMySQL中是否有任何只能更新表的一部分的函数?因为有些日子我们的某些数据可能不完整,所以我需要保留旧表并且只替换有新数据的行,这是我写的函数,但是没有用,任何帮助都会受到赞赏:

col.info <- "(id int, timestamp bigint, yyyy int, mm int, dd int, value double, 
PRIMARY KEY(id, timestamp, yyyy, mm, dd))"

Func <- function(con, tbl.name, dat.set, col.info) {
  if (dbExistsTable(con, tbl.name)) {  
  dbWriteTable(con, tbl.name, dat.set, row.names=F, append=T);  #what can I change the append for??

  } else { 
  dbSendQuery(con, paste("CREATE TABLE IF NOT EXISTS", tbl.name, col.info, sep=" ")); 
  dbWriteTable(con, tbl.name, dat.set, row.names=F, append=T);
  }
} 

Func(conn_table, "daily_update", df, col.info)

1 个解决方案

#1

I recently came across the dbx package which does exactly what you need.

我最近遇到了dbx软件包,它完全符合您的需求。

The following code will execute an upsert (on duplicate key update) query on your database table. Just replace the where_cols argument with whichever columns act as your primary key.

以下代码将在数据库表上执行upsert(重复键更新)查询。只需将where_cols参数替换为作为主键的列。

install.packages("dbx")
library(dbx)
dbx::dbxUpsert(con, tbl.name, dat.set, where_cols = c("id"))

#1