mysql - dbWriteTable 删除 MySQL 中的主键和索引

Question

我创建一个 MySQL 表如下:

create table schema_name.test (
col1 varchar(10),
col2 varchar(10),
primary key (col1),
index col1_index (col1)
)

然后我使用 dbWriteTable 将 data.frame 写入表中如下:

> test <- data.frame(col1 = c("a","b"),col2 = c("apple","banana"))
> types <- c("varchar(10)","varchar(10)")
> names(types) <- names(test)
> dbWriteTable(conn = con, overwrite = TRUE, value = test, name = "test",field.types = types)

执行此操作并检查 MySQL 环境中的表后，我发现主键和索引已被删除(con 这里是使用 dbConnect( odbc::odbc (),"my_dsn_name"))。这是不受欢迎的。

我试图实现的行为是覆盖表中的数据，同时保持表的元数据/结构完好无损。

我知道我可以创建另一个具有相同结构的表，使用 dbWriteTable 将数据写入该表，然后使用插入语句将数据从另一个表复制到目标表中，但这是相当大的开销，感觉应该是没有必要的。按顺序执行所有数据的删除和追加也同样存在问题。

是否有更优雅的解决方案来解决这个问题，并且破坏键和索引是有意的行为吗？文档非常稀疏，似乎没有提供此问题的解决方案。

Answer 1

首先我想指出您当前的表定义有冗余:

CREATE TABLE schema_name.test (
    col1 VARCHAR(10),
    col2 VARCHAR(10),
    PRIMARY KEY (col1),
    INDEX col1_index (col1)
);

MySQL会自动在主键上创建索引，因此不需要定义索引。

关于您实际的 R 问题，如果您不希望 R 覆盖表，请尝试使用 overwrite=FALSE 调用 dbWriteTable。来自摸索here ，似乎带有 overwrite=TRUE 的 dbWriteTable 可能会删除您的 MySQL 表，然后重新创建它。

根据下面的评论，如果您想删除表中的所有数据，然后插入新数据，您可以先截断表，然后调用dbWriteTable:

dbGetQuery(con, "TRUNCATE TABLE schema_name.test")
dbWriteTable(conn=con, overwrite=FALSE, append=TRUE, value=test, name="test", field.types=types)