如何通过RODBC在MySQL中执行批量UPDATE多语句查询？

以下是几种可行的解决方案，按优先级和安全性排序：

1. 用CASE WHEN改写为单条UPDATE语句（最优方案） #

不用依赖多语句执行，直接把所有更新逻辑合并成一条SQL，性能和安全性都拉满，还能避免全表扫描：

require(RODBC)

# 合并后的单条UPDATE语句
q <- "UPDATE myTable 
      SET NumberOfSignals = CASE 
          WHEN ItemID = 1361 THEN 4
          WHEN ItemID = 1362 THEN 5
          WHEN ItemID = 1363 THEN 6
          ELSE NumberOfSignals  -- 不匹配的行保持原值，避免误改
      END 
      WHERE ItemID IN (1361, 1362, 1363);"  -- 限定更新范围，提升效率

res <- sqlQuery(con, q, stringsAsFactors = FALSE)

如果是从数据框批量生成更新逻辑，可以用R自动拼接SQL：

# 假设你有一个包含更新规则的dataframe
update_df <- data.frame(ItemID = c(1361,1362,1363), NumberOfSignals = c(4,5,6))

# 自动生成CASE分支
case_clauses <- apply(update_df, 1, function(row) {
  sprintf("WHEN ItemID = %d THEN %d", row[1], row[2])
})
case_part <- paste(case_clauses, collapse = " ")

# 生成IN条件
in_part <- paste(update_df$ItemID, collapse = ", ")

# 组装最终SQL
q <- sprintf("UPDATE myTable SET NumberOfSignals = CASE %s ELSE NumberOfSignals END WHERE ItemID IN (%s);", case_part, in_part)

2. 修改RODBC连接参数开启多语句支持 #

如果一定要用多条UPDATE语句，需要在MySQL ODBC连接中开启allowMultiQueries=TRUE：

• 用odbcConnect时指定额外参数：

con <- odbcConnect("你的DSN名称", believeNRows=FALSE, dbms.args="allowMultiQueries=TRUE")

• 用odbcDriverConnect直接写连接字符串：

con <- odbcDriverConnect("DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATABASE=你的库名;USER=用户名;PASSWORD=密码;allowMultiQueries=TRUE")

之后再执行原来的多语句SQL即可，但要注意开启多语句有SQL注入风险，如果SQL里包含用户输入内容，必须做严格校验。

3. 替换RODBC为DBI+RMariaDB包 #

RODBC属于较老旧的包，DBI生态的RMariaDB对MySQL的多语句支持更友好，无需额外配置：

require(DBI)
require(RMariaDB)

# 创建连接
con <- dbConnect(RMariaDB::MariaDB(), 
                 host = "localhost",
                 dbname = "你的库名",
                 user = "用户名",
                 password = "密码")

# 执行多语句更新
q <- "UPDATE myTable SET NumberOfSignals=4 WHERE ItemID=1361; 
      UPDATE myTable SET NumberOfSignals=5 WHERE ItemID=1362; 
      UPDATE myTable SET NumberOfSignals=6 WHERE ItemID=1363;"

res <- dbExecute(con, q)

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