如何在准备好的SQL语句中使用非IN条件将[]切片传递给IN条件？ [英] How do I pass a []slice to an IN-condition in a prepared SQL statement with non-IN-conditions as well?

问题描述

假设你有以下的SQL查询：

$ $ $
$ f $
WHERE type = ？
AND子类型（？）

您有以下可能的数据用户界面可以设置这些数据）：

  var类型int 
 var SubTypes [] int 
  

在 SubTypes 的情况下，我们正在讨论多重选择选择。

现在，以下代码将不起作用：

  rows，err：= sqldb.Query（`SELECT * 
 FROM foo 
 WHERE type =？
 AND子类型IN（？）`，Type，SubTypes）
  

因为驱动程序（至少是 mysql 本例中使用的驱动程序）无法识别 [] slice 。键入分解它（ SubTypes ... ）也不起作用，因为A）即使可以，你也不能有多于一个分解参数和B），你的SQL只支持单个项目（（？））。

解决方案

声明不以这种方式工作，至少不是我知道的主要DBMS。我的意思是，在Go中，支持由 database / sql drivers 实现的准备语句应该使用底层DBMS提供的相应工具（一个驱动程序可能会选择模拟这种支持，如果它不是由它所连接的数据库引擎提供的）。

现在在我熟悉的所有DBMS中，准备好的语句的整体思想是，它由DB引擎处理一次并缓存;这里的处理意味着语法检查，编译成一些特定于DB的内部表示和计算出的执行计划。从编译一词来看，语句的文本只处理一次，然后每次调用prepared语句就会告诉服务器这里是我早先提供的准备语句的ID ，以下是用于包含它的占位符的实际参数列表。这就像编译Go程序，然后用不同的命令行标志连续多次调用它。

所以你提出的解决方案是正确的：如果你想要在调用之间混淆语句文本，然后通过一切手段使用客户端文本操作 ¹ ，但不要试图将其结果用作准备语句，除非您真的打算执行结果文本不止一次。

可能会更清楚：您最初尝试准备类似于

  SELECT a，b FROM foo WHERE a IN（？）
  

在尝试为 IN（？）占位符提供一组值时失败，因为在那里需要逗号来指定几个值是语法，而不是价值。

我认为准备类似于

  SELECT a，b FROM foo WHERE IN（？，？，？）
  

，因为它不会违反该规则。不是说这是一个适合你的解决方案;< / p>

另见这个和这个—研究后者将允许你直接在MySQL客户端中使用准备好的语句。

---

¹ 一些引擎提供服务器端SQL生成，随后执行生成的文本。

Imagine you have the following SQL query:

SELECT *
  FROM foo
  WHERE type = ?
    AND subtype IN (?)

And you have the following possible data (we imagine that a user interface can set these data):

var Type int
var SubTypes []int

In the case of SubTypes, we are talking about a multiple choice selection.

Now, the following code won't work:

rows, err := sqldb.Query(`SELECT *
  FROM foo
  WHERE type = ?
    AND subtype IN (?)`, Type, SubTypes)

Because the driver (at least the mysql driver used in this example) doesn't recognise a []slice. Typing to explode it (SubTypes...) doesn't work either, because A) you cannot have more than one exploded parameter and B) even if you could, your SQL only supports a single item ((?)).

解决方案

Prepared statements do not work that way, at least not in major DBMS I know. I mean, in Go, the support for prepared statements implemented by database/sql drivers is supposed to use the corresponding facility provided by the underlying DBMS (a driver might opt to simulate such support if it's not provided by the DB engine it interfaces with).

Now in all the DBMS-s I'm familiar with, the whole idea of prepared statement is that it's processed once by the DB engine and cached; "processed" here means syntax checking, compiling into some DB-specific internal representation and its execution plan figured out. As follows from the term "compiled", the statement's text is processed exactly once, and then each call to the prepared statement just essentially tells the server "here is the ID of that prepared statement I supplied you earlier, and here's the list of actual parameters to use for placeholders it contained". It's like compiling a Go program and then calling it several times in a row with different command-line flags.

So the solution you have come up with is correct: if you want to mess with the statement text between invocation then by all means use client-side text manipulations¹ but do not attempt to use the result of it as a prepared statement unless you really intend to execute the resulting text more than once.

And to be may be more clear: your initial attempt to prepare something like

SELECT a, b FROM foo WHERE a IN (?)

supposedly fails at your attempt to supply a set of values for that IN (?) placeholder because commas which would be required there to specify several values are syntax, not parts of the value.

I think it should still be fine to prepare something like

SELECT a, b FROM foo WHERE a IN (?, ?, ?)

because it does not break that rule. Not that it's a solution for you…

See also this and this — studying the latter would allow you to play with prepared statements directly in the MySQL client.

---

¹ Some engines provide for server-side SQL generation with subsequent execution of the generated text.

这篇关于如何在准备好的SQL语句中使用非IN条件将[]切片传递给IN条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！