我在使用 glue_sql 将值插入数据库中，但它会自动把布尔值转换为整数。有没有办法让 glue_sql 不再这么做？

library(glue)
library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), "test.sqlite")

rows <- list("ABC", 1, pi, TRUE, FALSE)

glue_sql("INSERT into table values ({rows*})", rows = rows, .con = con)

#> <SQL> INSERT into table values ('ABC', 1, 3.14159265358979, 1, 0)

我正在使用 glue 1.8.0，DBI 1.3.0

解决方案

先说，原帖作者实际使用的数据库管理系统是PostgreSQL，而不是SQLite，因此我会忽略SQLite那边布尔值不是受支持的数据类型的问题。

ODBC之所以会这样，是因为并非所有DBMS对逻辑值的处理都相同；有的在 BIT 字段中要求使用0/1的整数，有的则以更易读的方式支持布尔值（不仅仅是一个整数）。要成为覆盖所有可能方言的包装层，最简单的方法是假设字面量 true（无论大小写）都不被支持。因此，ODBC很可能在 glue_sql() 中总是这么做。

然而，如果你能切换到 RPostgres，即原生驱动，那么你就会得到期望的行为。

我有 odbccon（面向PostgreSQL的 ODBC驱动）和 pgcon（原生驱动，使用同一个数据库）。

# setup
odbccon <- DBI::dbConnect(odbc::odbc(), ...)
pgcon   <- DBI::dbConnect(RPostgres::Postgres(), ...)
# quick setup
DBI::dbExecute(pgcon, "create table mytable (v1 text, v2 int, v3 float, v4 boolean, v5 boolean)")
rows <- list("ABC", 1, pi, TRUE, FALSE)

glue::glue_sql("INSERT into mytable values ({rows*})", rows = rows, .con = odbccon)
# <SQL> INSERT into mytable values ('ABC', 1, 3.14159265358979, 1, 0)
glue::glue_sql("INSERT into mytable values ({rows*})", rows = rows, .con = pgcon)
# <SQL> INSERT into mytable values ('ABC', 1::float8, 3.14159265358979::float8, TRUE, FALSE)

基本的插入在 pgcon 上工作，但在 odbccon 上不行：

DBI::dbExecute(odbccon, glue::glue_sql("INSERT into mytable values ({rows*})", rows = rows, .con = odbccon))
# Error in `DBI::dbExecute()`:
# ! ODBC failed with error 00000 from .
# ✖ ERROR: column "v4" is of type boolean but expression is of type integer;
# • Error while executing the query
# • <SQL> 'INSERT into mytable values ('ABC', 1, 3.14159265358979, 1, 0)'
# ℹ From 'nanodbc/nanodbc.cpp:1802'.
# Run `rlang::last_trace()` to see where the error occurred.

DBI::dbExecute(pgcon, glue::glue_sql("INSERT into mytable values ({rows*})", rows = rows, .con = pgcon))
# [1] 1

不过……说实话，我宁愿使用 DBI::dbAppendTable()，因为它能彻底解决这个问题。

rows2 <- setNames(as.data.frame(rows), c("v1", "v2", "v3", "v4", "v5"))
DBI::dbAppendTable(odbccon, "mytable", rows2)
DBI::dbAppendTable(pgcon, "mytable", rows2)
# [1] 1
DBI::dbGetQuery(odbccon, "select * from mytable")
#    v1 v2       v3 v4 v5
# 1 ABC  1 3.141593  1  0
# 2 ABC  1 3.141593  1  0
# 3 ABC  1 3.141593  1  0
DBI::dbGetQuery(pgcon, "select * from mytable")
#    v1 v2       v3   v4    v5
# 1 ABC  1 3.141593 TRUE FALSE
# 2 ABC  1 3.141593 TRUE FALSE
# 3 ABC  1 3.141593 TRUE FALSE

需要注意的是，原生驱动返回的是真正的 logical 变量，而不是来自ODBC的整数。

底线：

• 如果可能，使用原生驱动，它会快一点，对这些事情的处理也更自然；或者
• 如果你必须使用ODBC，可以使用 dbAppendTable()，不必担心，或者你需要围绕 glue_sql() 编写一个包装器（或一个完整的替代方案）。