RODBC::sqlSave写入MySQL datetime失败的问题及快速解决需求

问题重现 #

1. MySQL建表语句 #

CREATE TABLE fractest( c1 DATETIME(0), c2 DATETIME(2), c3 DATETIME(4) );

2. R代码执行报错 #

x <- data.frame(c1 = Sys.time(),c2 = Sys.time(),c3 = Sys.time())
conn <- RODBC::odbcConnect('MyDatabase')
RODBC::sqlSave(
 channel = conn,
 dat = x,
 append = TRUE,
 tablename = 'fractest',
 rownames = FALSE,
 safer = TRUE,
 fast = TRUE
 )

报错信息：

Error in if (any(notOK <- (coldata[, 3L] == 0L))) { : 
  missing value where TRUE/FALSE needed

3. 排查结果 #

RODBC 1.3-20中sqlColumns返回的COLUMN_SIZE和DECIMAL_DIGITS为NA，而旧版本1.3-17能正常返回数值：

RODBC:::sqlColumns(conn, 'fractest')[c(4L, 5L, 7L, 9L)]
  COLUMN_NAME DATA_TYPE COLUMN_SIZE DECIMAL_DIGITS
1          c1        93          NA             NA
2          c2        93          NA             NA
3          c3        93          NA             NA

原因分析 #

sqlSave在fast=TRUE模式下，会依赖sqlColumns返回的COLUMN_SIZE做逻辑判断，当该值为NA时，if语句无法解析TRUE/FALSE，触发报错。这个问题是RODBC 1.3-20对MySQL DATETIME(n)类型的元数据读取逻辑出现了问题。

快速解决方案 #

方案1：手动修补sqlColumns返回结果 #

临时修正sqlColumns的返回值，填充datetime字段对应的COLUMN_SIZE和DECIMAL_DIGITS，再执行sqlSave：

# 获取表结构
col_info <- RODBC:::sqlColumns(conn, 'fractest')

# 根据datetime精度手动赋值
# DATETIME(0)格式为YYYY-MM-DD HH:MM:SS，长度19；DECIMAL_DIGITS为0
col_info$COLUMN_SIZE[col_info$COLUMN_NAME == 'c1'] <- 19
col_info$DECIMAL_DIGITS[col_info$COLUMN_NAME == 'c1'] <- 0
# DATETIME(2)格式为YYYY-MM-DD HH:MM:SS.xx，长度22；DECIMAL_DIGITS为2
col_info$COLUMN_SIZE[col_info$COLUMN_NAME == 'c2'] <- 22
col_info$DECIMAL_DIGITS[col_info$COLUMN_NAME == 'c2'] <- 2
# DATETIME(4)格式为YYYY-MM-DD HH:MM:SS.xxxx，长度24；DECIMAL_DIGITS为4
col_info$COLUMN_SIZE[col_info$COLUMN_NAME == 'c3'] <- 24
col_info$DECIMAL_DIGITS[col_info$COLUMN_NAME == 'c3'] <- 4

# 临时替换RODBC内部的sqlColumns函数
assignInNamespace("sqlColumns", function(channel, tablename, ...) col_info, ns = "RODBC")

# 执行sqlSave
RODBC::sqlSave(
 channel = conn,
 dat = x,
 append = TRUE,
 tablename = 'fractest',
 rownames = FALSE,
 safer = TRUE,
 fast = TRUE
 )

# 恢复原函数（避免影响后续操作）
rm("sqlColumns", envir = as.environment("package:RODBC"))

方案2：切换到DBI+RMariaDB #

RODBC属于较旧的数据库接口，推荐使用更现代的DBI+RMariaDB组合，对MySQL datetime精度支持更完善，写入效率也有保障：

library(DBI)
library(RMariaDB)

# 建立连接
conn <- dbConnect(
  MariaDB(),
  dbname = 'MyDatabase',
  host = 'localhost', # 根据实际配置修改
  user = 'your_username',
  password = 'your_password'
)

# 写入数据
dbWriteTable(conn, 'fractest', x, append = TRUE, row.names = FALSE)

# 断开连接
dbDisconnect(conn)

方案3：降级RODBC到1.3-17版本 #

如果不想修改代码，直接回退到无bug的旧版本：

# 需要先安装devtools
# install.packages("devtools")
devtools::install_version("RODBC", version = "1.3-17", repos = "http://cran.us.r-project.org")

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