将Rdata.table实例更新/插入SQL数据库(例如MySQL)的最简单方法是什么？我有一个data.table的实例，其中配置了一些键列(通过setkey)。现在，每当表的主键列与表的键匹配时(或者我可以手动映射它)，我想update一个相应的SQL表，但对于那些键不匹配的行，insert它们。
最好我不需要自己为它编写SQL。一个原因是数据列的数量相对较多。另一个原因是我希望数据库独立(主要是在MySQL、PostgreSQL和SQLite之间轻松切换)。
这里是另一个修改sqlAppendTable查询的解决方案。
sql_query <- sqlAppendTable(con, "table", DT)
sql_query@.Data <- paste0(sql_query@.Data, "n ","ON DUPLICATE KEY UPDATE col1 = values(col1), col2 = values(col2)")
dbSendQuery(con, sql_query)
更改应更新的列的col1和col2。根据需要添加其他内容。可能您可以先确定主键，然后将其作为一个通用函数。
我认为这很难避免特定于数据库的代码，因为数据库中不同语法的不确定性。
追加销售
ANSI标准术语是MERGE，(根据维基百科)ANSI标准在大多数大型商业数据库中都得到支持。然而，由于添加到标准中的语法相对较新，因此在您希望其可移植的数据库中，存在许多语法不同的非标准实现。
R中的数据库访问
DBI
dplyr等人完成的大部分数据库I/O都是通过包DBI完成的。DBI包有一个INSERT函数dbWriteTable()，但文档很少。
RODBC
RODBC包有一个sqlUpdate()功能，但它有许多问题：
- 文档未提及新记录的处理，即INSERT
- 没有粒度，即所有值都将更新，因此无法引入部分UPSERT
- 使用ROBC保存可能会出现问题，从而导致R会话崩溃
话虽如此，但它可能适用于您的场景。如果没有，我将在下面展示如何构造UPSERT语句，并使用ROBC以参数化的方式执行它。它不是可移植的代码，但它是动态的，只需最少的努力就可以传递不更新的列。
管柱结构
mysql中默认的UPSERT语法是INSERT INTO ... ON DUPLICATE KEY UPDATE ...。这对于动态使用data.table的列名进行字符串构造来说非常容易。下面是一个为任何进行参数化UPSERT的示例函数
library(data.table)
library(RODBC)
library(RODBCext)
irisDT<- data.table(iris, key="Species")
UPSERT<-function(DT, connectionString, destTable){
sql<- paste0("INSERT INTO ",destTable," (",paste(colnames(DT ),
collapse=","),")n",
# This bit inserts the parameterised bit i.e. where your values will go
"VALUES (", paste(rep("?",ncol(DT )),
collapse=",",sep=",") ,")n",
"ON DUPLICATE KEY UPDATE n",
# This specifies on dup. key behaviour - doesn't include key
paste(colnames(DT )[!(colnames(DT ) %in% key(DT ))],
"=VALUE(",
colnames(DT )[!(colnames(DT ) %in% key(DT ))],
")", collapse=",n", sep="")
)
RODBCext::sqlExecute(channel=connectionString,
query=sql,
data=DT
)
}
UPSERT(irisDT)
您可能需要稍微调整一下字符串结构，因为我的MySQL语法可能有点偏离。
注意：确保您在data.table上设置了密钥在这里非常重要，否则您就无法进行UPSERT，当然，除非您修改代码以排除特定列，但我要为您提供一个动态解决方案。
多步骤流程
正如@hadley在评论中所暗示的那样。可以进行多步骤的不可知过程，即
- 从表中获取现有值
- 识别新的行并插入它们
- 非新行，通过update语句发送
这将更加详细，并且取决于将新记录插入数据库的速度，表可能会超出对唯一性和插入表的新记录的限制，您需要更新这些限制，而不是插入重复的行。