MySQL 什么是动态 SQL？

What is dynamic SQL?

提问by augustin

I just asked an SQL related question, and the first answer was: "This is a situation where dynamic SQL is the way to go."

我刚刚问了一个与 SQL 相关的问题，第一个答案是：“这是一种情况，动态 SQL 是要走的路。”

As I had never heard of dynamic SQLbefore, I immediately searched this site and the web for what it was. Wikipedia has no article with this title. The first Google results all point to user forums where people ask more or less related questions.

由于我以前从未听说过动态 SQL，因此我立即搜索了该站点和 Web 以了解它是什么。维基百科没有具有此标题的文章。第一个谷歌结果都指向用户论坛，人们在那里提出或多或少的相关问题。

However, I didn't find a clear definition of what a 'dynamic SQL' is. Is it something vendor specific?I work with MySQLand I didn't find a reference in the MySQL handbook (only questions, mostly unanswered, in the MySQL user forums).

但是，我没有找到“动态 SQL”的明确定义。它是供应商特定的东西吗？我使用MySQL，但在 MySQL 手册中没有找到参考资料（在 MySQL 用户论坛中只有问题，大部分没有答案）。

On the other hand, I found many references to stored procedures. I have a slightly better grasp of what stored procedures are, although I have never used any. How are the two concepts related? Are they the same thing or does one uses the other?

另一方面，我发现了很多对存储过程的引用。尽管我从未使用过任何存储过程，但我对什么是存储过程有了更好的了解。这两个概念有什么关系？它们是相同的东西还是一个使用另一个？

Basically, what is needed is a simple introduction to dynamic SQLfor someone who is new to the concept.

基本上，需要为不熟悉动态 SQL的人做一个简单的动态 SQL介绍。

P.S.: If you feel like it, you may have a go at answering my previous question that prompted this one: SQL: How can we make a table1 JOIN table2 ON a table given in a field in table1?

PS：如果您愿意，您可以尝试回答我之前提出的问题：SQL：我们如何使 table1 JOIN table2 ON 在 table1 中的字段中给出的表？

采纳答案by Rowland Shaw

Dynamic SQLis merely where the query has been built on the fly - with some vendors, you can build up the text of the dynamic query within one stored procedure, and then execute the generated SQL. In other cases, the term merely refers to a decision made by code on the client (this is at least vendor neutral)

动态 SQL只是动态构建查询的地方——对于一些供应商，您可以在一个存储过程中构建动态查询的文本，然后执行生成的 SQL。在其他情况下，该术语仅指客户端代码做出的决定（这至少是供应商中立的）

回答by BradC

Other answers have defined whatdynamic SQL is, but I didn't see any other answers that attempted to describe whywe sometimes need to use it. (My experience is SQL Server, but I think other products are generally similar in this respect.)

其他答案已经定义了什么动态SQL是的，但我没有看到，试图描述任何其他的答案，为什么我们有时需要使用它。（我的经验是SQL Server，但我认为其他产品在这方面大体相似。）

Dynamic SQL is useful when you are replacing parts of a query that can't be replaced using other methods.

当您替换无法使用其他方法替换的查询部分时，动态 SQL 很有用。

For example, every time you call a query like:

例如，每次您调用如下查询时：

SELECT OrderID, OrderDate, TotalPrice FROM Orders WHERE CustomerID = ?? 

you will be passing in a different value for CustomerID. This is the simplest case, and one that can by solved using a parameterized query, or a stored procedure that accepts a parameter, etc.

您将为 CustomerID 传入不同的值。这是最简单的情况，可以通过使用参数化查询或接受参数的存储过程等来解决。

Generally speaking, dynamic SQL should be avoided in favor of parameterized queries, for performance and security reasons. (Although the performance difference probably varies quite a bit between vendors, and perhaps even between product versions, or even server configuration).

一般而言，出于性能和安全原因，应避免使用动态 SQL 以支持参数化查询。（尽管性能差异可能因供应商而异，甚至可能因产品版本或服务器配置而异）。

Other queries are possibleto do using parameters, but might be simpleras dynamic SQL:

其他查询也可以使用参数进行，但可能比动态 SQL更简单：

SELECT OrderID, OrderDate, TotalPrice FROM Orders 
WHERE CustomerID IN (??,??,??)

If you always had 3 values, this is as easy as the first one. But what if this is a variable-length list? Its possible to do with parameters, but can be very difficult. How about:

如果你总是有 3 个值，这和第一个一样简单。但是如果这是一个可变长度的列表呢？它可能与参数有关，但可能非常困难。怎么样：

SELECT OrderID, OrderDate, TotalPrice FROM Orders WHERE CustomerID = ??
ORDER BY ??

This can't be substituted directly, you can do it with a huge complicated CASE statement in the ORDER BY explicitly listing all possible fields, which may or may not be practical, depending on the number of fields available to sort by.

这不能直接替换，您可以在 ORDER BY 中使用一个巨大的复杂 CASE 语句来完成，明确列出所有可能的字段，这可能实用，也可能不实用，具体取决于可用于排序的字段数量。

Finally, some queries simply CAN'T be done using any other method.

最后，有些查询根本无法使用任何其他方法完成。

Let's say you have a bunch of Orders tables (not saying this is great design), but you might find yourself hoping you can do something like:

假设您有一堆 Orders 表（并不是说这是一个很棒的设计），但是您可能会发现自己希望可以执行以下操作：

SELECT OrderID, OrderDate, TotalPrice FROM ?? WHERE CustomerID = ??

This can't be done using any other methods. In my environment, I frequently encounter queries like:

使用任何其他方法都无法做到这一点。在我的环境中，我经常遇到以下查询：

SELECT (programatically built list of fields)
FROM table1 INNER JOIN table2
(Optional INNER JOIN to table3)
WHERE (condition1)
AND (long list of other optional WHERE clauses)

Again, not saying that this is necessarily great design, but dynamic SQL is pretty much required for these types of queries.

同样，并不是说这一定是很棒的设计，但是这些类型的查询几乎需要动态 SQL。

Hope this helps.

希望这可以帮助。

回答by Justin Niessner

Dynamic SQL is simply a SQL statement that is composed on the fly before being executed. For example, the following C# (using a parameterized query):

动态 SQL 只是在执行之前动态组合的 SQL 语句。例如，以下 C#（使用参数化查询）：

var command = new SqlCommand("select * from myTable where id = @someId");
command.Parameters.Add(new SqlParameter("@someId", idValue));

Could be re-written using dynamic sql as:

可以使用动态 sql 重写为：

var command = new SqlCommand("select * from myTable where id = " + idValue);

Keep in mind, though, that Dynamic SQL is dangerous since it readily allows for SQL Injection attacks.

但是请记住，动态 SQL 是危险的，因为它很容易允许 SQL 注入攻击。

回答by Pedro

Dynamic SQL is a SQL built from strings at runtime. It is useful to dynamically set filters or other stuff.

动态 SQL 是在运行时从字符串构建的 SQL。动态设置过滤器或其他东西很有用。

An example:

一个例子：

declare @sql_clause varchar(1000)  
declare @sql varchar(5000)   


set @sql_clause = ' and '    
set @sql = ' insert into #tmp  
 select   
   *
from Table 
where propA = 1 '    

if @param1 <> ''    
begin    
   set @sql = @sql + @sql_clause + ' prop1 in (' + @param1 + ')'    
end    
if @param2 <> ''    
begin    
   set @sql = @sql + @sql_clause + ' prop2 in (' + @param2 + ')'    
end 

exec(@sql)

回答by Skadoosh

It is exactly what Rowlandmentioned. To elaborate on that a bit, take the following SQL:

这正是罗兰提到的。为了详细说明这一点，请使用以下 SQL：

Select * from table1 where id = 1

I am not sure which language you are using to connect to the database, but if I were to use C#, an example of a dynamic SQL query would be something like this:

我不确定您使用哪种语言连接到数据库，但如果我使用 C#，动态 SQL 查询的示例将是这样的：

string sqlCmd = "Select * from table1 where id = " + userid;

You want to avoid using dynamic SQL, because it becomes a bit cumbersome to keep integrity of the code if the query get too big. Also, very important, dynamic SQL is susceptible to SQL injection attacks.

您希望避免使用动态 SQL，因为如果查询太大，保持代码的完整性会变得有点麻烦。此外，非常重要的是，动态 SQL 容易受到 SQL 注入攻击。

A better way of writing the above statement would be to use parameters, if you are using SQL Server.

如果您使用的是 SQL Server，则编写上述语句的更好方法是使用参数。

回答by Lukas Eder

To most databases, every SQL query is "dynamic" meaning that it is a program that is interpreted by the query optimiser given the input SQL string and possibly the parameter bindings ("bind variables").

对于大多数数据库，每个 SQL 查询都是“动态的”，这意味着它是一个由查询优化器在给定输入 SQL 字符串和可能的参数绑定（“绑定变量”）的情况下解释的程序。

Static SQL

静态 SQL

However, most of the time, that SQL string is not constructed dynamically but statically, either in procedural languages like PL/SQL:

但是，大多数情况下，该 SQL 字符串不是动态构建的，而是静态构建的，无论是在 PL/SQL 等过程语言中：

FOR rec IN (SELECT * FROM foo WHERE x = 1) LOOP
  --        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ "static SQL"
  ..
END LOOP;

Or in client / host languages like Java, using JDBC:

或者在客户端/主机语言（如 Java）中，使用 JDBC：

try (ResultSet rs = stmt.executeQuery("SELECT * FROM foo WHERE x = 1")) {
  // "static SQL"                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  ..
}

In both cases, the SQL string is "static" in the language that embeds it. Technically, it will still be "dynamic" to the SQL engine, which doesn't know how the SQL string is constructed, nor that it was a static SQL string.

在这两种情况下，SQL 字符串在嵌入它的语言中都是“静态的”。从技术上讲，它对 SQL 引擎来说仍然是“动态的”，它不知道 SQL 字符串是如何构造的，也不知道它是静态 SQL 字符串。

Dynamic SQL

动态 SQL

Sometimes, the SQL string needs to be constructed dynamically, given some input parameters. E.g. the above query might not need any predicate at all in some cases.

有时，给定一些输入参数，需要动态构造 SQL 字符串。例如，在某些情况下，上面的查询可能根本不需要任何谓词。

You might then choose to proceed to constructing the string dynamically, e.g. in PL/SQL:

然后您可以选择继续动态构造字符串，例如在 PL/SQL 中：

DECLARE
  TYPE foo_c IS REF CURSOR;
  v_foo_c foo_c;
  v_foo foo%ROWTYPE;
  sql VARCHAR2(1000);
BEGIN
  sql := 'SELECT * FROM foo';

  IF something THEN
    sql := sql || ' WHERE x = 1'; -- Beware of syntax errors and SQL injection!
  END IF;

  OPEN v_foo_c FOR sql;
  LOOP
    FETCH v_foo_c INTO v_foo;
    EXIT WHEN v_foo_c%NOTFOUND;
  END LOOP;
END;

Or in Java / JDBC:

或者在 Java/JDBC 中：

String sql = "SELECT * FROM foo";
if (something)
    sql += " WHERE x = 1"; // Beware of syntax errors and SQL injection!
try (ResultSet rs = stmt.executeQuery(sql)) {
  ..
}

Or in Java using a SQL builder like jOOQ

或者在Java 中使用像 jOOQ 这样的 SQL 构建器

// No syntax error / SQL injection risk here
Condition condition = something ? FOO.X.eq(1) : DSL.trueCondition();
for (FooRecord foo : DSL.using(configuration)
   .selectFrom(FOO)
   .where(condition)) {
  ..
}

Many languages have query builder libraries like the above, which shine most when doing dynamic SQL.

许多语言都有像上面这样的查询构建器库，在执行动态 SQL 时最闪耀。

(Disclaimer: I work for the company behind jOOQ)

（免责声明：我为 jOOQ 背后的公司工作）

回答by Bob Palmer

Rowlandis correct, and as an addendum, unless you're properly using parameters (versus just concatonating parameter values inline from provided text, etc.) it can also be a security risk. It's also a bear to debug, etc.

罗兰是正确的，作为附录，除非您正确使用参数（而不是仅从提供的文本中串联参数值等），否则它也可能存在安全风险。调试等等也是熊熊。

Lastly, whenever you use dynamic SQL unwisely, things are unleashed and children are eaten.

最后，每当您不明智地使用动态 SQL 时，事情就会被释放出来，孩子们就会被吃掉。

回答by oezi

I think what's meant is that you should build the query dynamically before executing it. For your other questions this means that you should select the table name you need first and the use your programming language to build a second query for doing what you want (what you want to do in the other question isn't possible directly like you want).

我认为这意味着您应该在执行之前动态构建查询。对于您的其他问题，这意味着您应该首先选择所需的表名，然后使用您的编程语言构建第二个查询以执行您想要的操作（您在另一个问题中想要做的事情不可能像您想要的那样直接）。

回答by onedaywhen

Is it something vendor specific?

它是供应商特定的东西吗？

The SQL-92 Standard has a whole chapter on dynamic SQL (chapter 17) but it only applies to FULL SQL-92 and I know of no vendor that has implemented it.

SQL-92 标准有一整章关于动态 SQL（第 17 章），但它只适用于 FULL SQL-92，我知道没有供应商实现了它。