mysql - R 和 MySQL - 在使用 dbWriteTable() 写入之前检查表中是否存在一行

Question

我正在使用 R 包 RMySQL 和 dbConnect 将我的 R 与我公司的 MySQL 数据库连接起来。简而言之，我的 R 脚本连接到 MySQL 数据库，从数据库中提取一个表 (table1)，使用该表进行一些分析，然后写入 MySQL 数据库中的另一个表 (table2)。

所以 table2 不是从数据库中提取的，而是需要附加我在 R 中所做的工作。将我在 R 中完成的一些其他分析添加到表中，然后将表重新写入 MySQL数据库，将新信息附加到前一个数据库中。我目前用来写入 table2 的代码行是:

dbWriteTable(con_hub, value = my_R_dataframe, name = "table2", append = TRUE, row.names = FALSE)

在编写此代码之前，我需要确保 my_R_dataframe 中没有已存在于 table2 数据库表中的行，因为重复项很难处理。有人对此有什么建议吗？另外 - 我宁愿不必将 table2 从 MySQL 数据库中拉到 R 中，因为该查询会为我的代码增加相当多的时间。

谢谢，

Answer 1

考虑以下两种方法之一:SQL 驱动的重复数据删除或 R 驱动的重复数据删除解决方案。对于前者，您需要使用运行经典 LEFT JOIN...IS NULL/NOT EXISTS/NOT IN 的临时暂存表。 SQL 查询。对于后者，您将导入数据框中的所有内容，附加您当前的 df，然后运行 ​​R 的 unique()。但是，听起来您并不打算使用后者，但我会向 future 的读者展示。

SQL (使用具有目标表的精确结构的临时表)

# OVERWRITE TEMP EACH TIME
dbWriteTable(con_hub, value = my_R_dataframe, 
                      name = "table2_temp", 
                      overwrite = TRUE,                        
                      row.names = FALSE)

# RUN LEFT JOIN...IS NULL QUERY (COMPARE COLS --COL1, COL2, COL3-- ADD/REMOVE AS NEEDED)
dbSendQuery(con_hub, paste0("INSERT INTO table2", 
                            " SELECT * FROM table2_temp",
                            " LEFT JOIN table2",
                            "   ON table2_temp.col1 = table2.col1", 
                            "   AND table2_temp.col2 = table2.col2",
                            "   AND table2_temp.col3 = table2.col3",
                            " WHERE table2.col1 IS NULL",
                            "   OR table2.col2 IS NULL",
                            "   OR table2.col3 IS NULL"))

R (读入table2数据，考虑是否太占资源，如果全列去重优先)

# RETRIEVE table2 DATA
table2df <- dbGetQuery(con_hub, "SELECT * FROM table2")

# APPEND BOTH DATAFRAMES
stackeddf <- rbind(table2df, my_R_dataframe)

# RETURN UNIQUE ROWS
finaldf <- unique(stackeddf)

# OVERWRITE DESTINATION TABLE EACH TIME
dbWriteTable(con_hub, value = finaldf, 
                      name = "table2", 
                      overwrite = TRUE,                         
                      row.names = FALSE)

# CLEAN UP ENVIRON OF UNNEEDED OBJECTS
rm(table2df, stackeddf, finaldf)
gc()