R DBI::dbGetQuery where子句将字符串解释为列名

Question

RStudio，封装DBI和odbc，连接到Amazon Redshift。我需要离职表中退休人员的记录。如果我使用where子句:

leavers <- dbGetQuery(con, 
'select distinct
"employee number",
"leaving reason"

from leaverstable where "employee number" = 12345')

这工作。但是，如果我将where子句交换为where "leaving reason" = Retirement'或where "leaving reason" = "Retirement"'，则会得到错误消息[SQLState 42703] error: column "retirement"不存在。我也尝试了having而不是where，但我得到相同的。

(顺便说一句:如果我交换单引号和语音标记，即语音标记在外面，单引号在里面('employee number')，那么它即使在第一个例子中也不起作用。)

我可以拉入整个表，然后在R中过滤除Retirement以外的记录，但是为了几千条记录，我拉入了300万条记录。我知道很少关于SQL，所以抱歉，如果这是微不足道的，但有人可以帮助，请?

Answer 1

带引号的标识符和带引号的字符串字面值在R和SQL之间是不同的。

| Lang | Identifier    | String Literal                 |
|------|---------------|--------------------------------|
| R    | `Some Column` | 'Some string' or "Some string" |
| SQL  | "Some Column" | 'Some string'                  |

在R中，我们通常只在列名上使用反号，当它们包含空格、以数字开头或违反R的任何其他"正常名称规则"时。我们可以总是使用它们，如mtcars$`cyl`(相当于mtcars$cyl)，但当没有必要时，通常不这样做。

试

leavers <- dbGetQuery(con, 
'select distinct
"employee number",
"leaving reason"

from leaverstable where "employee number" = 12345
or "leaving reason" = 'Retirement'
')

(注意我们需要转义引号，没什么大不了的)

更好的方法是使用绑定参数。除此之外，它们还提供了防止无意的SQL注入(该术语的稍微延伸)的安全性。也就是说,

leavers <- dbGetQuery(con, 
'select distinct
"employee number",
"leaving reason"     
from leaverstable where "employee number" = ?
or "leaving reason" = ?
', params = list(12345, "Retirement"))

，这里使用双引号的是在R域，而不是sql域，因为我们从R传递一个简单的字符串。这里，我们可以使用"Retirement"或'Retirement'，它们将是相同的，因为发生的事情是这个字符串在内部作为字符串有效地传递给SQL，它将正确地解释为字符串字面量，因为它被告知它是一个字符串，它不需要花费时间从SQL查询中解析它。(使用绑定参数还有其他优点，请参阅参数化查询。)

使用?作为要绑定的参数的占位符在DBMSes之间是不同的;例如，ODBC总是(我相信)使用?，无论DBMS类型如何;postgres使用$1(和$2，…);sqlite使用?、?1或:name;SQL Server(本地客户端)使用?.