什么是动态SQL？

Question

我刚刚问了一个与 SQL 相关的问题，第一个答案是：“这是动态 SQL 的最佳选择。”

因为我从未听说过动态 SQL 之前，我立即在这个网站和网络上搜索它是什么。维基百科没有此标题的文章。第一个谷歌结果都指向用户论坛，人们在其中询问或多或少相关的问题。

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

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

基本上，对于刚接触动态 SQL 概念的人来说，需要的是对动态 SQL 的简单介绍。

PS：如果您愿意，可以尝试回答我上一个引发此问题的问题：SQL：我们如何使 table1 JOIN table2 ON table1 字段中给出的表？

Answer 1

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

Answer 2

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

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

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

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

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

一般来说，应避免使用动态 SQL，而应使用参数化查询，因为性能和安全原因。 （尽管供应商之间的性能差异可能很大，甚至产品版本甚至服务器配置之间的性能差异可能也很大）。

其他查询可能使用参数来执行，但可能更简单为动态 SQL：

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

如果您始终有 3 个值，则这就像第一个。但如果这是一个可变长度列表怎么办？可以使用参数来完成，但可能非常困难。怎么样：

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

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

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

假设您有一堆 Orders 表（并不是说这是一个很棒的设计），但您可能会发现自己希望可以做一些事情就像：

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

使用任何其他方法都无法完成此操作。在我的环境中，我经常遇到如下查询：

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)

再次强调，并不是说这一定是很棒的设计，但这些类型的查询非常需要动态 SQL。

希望这有帮助。

Answer 3

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

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

可以使用动态 sql 重写为：

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

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

Answer 4

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

一个例子：

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)

Answer 5

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

Select * from table1 where id = 1

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

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

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

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

Answer 6

Rowland 是正确的，并且作为附录，除非您正确使用参数（而不是仅从提供的文本内联连接参数值）等）它也可能是一个安全风险。调试起来也很麻烦，等等。

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

Answer 7

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

静态 SQL

然而，大多数时候，SQL 字符串不是动态构建的，而是静态构建的，无论是在过程语言（如 PL/SQL）中：

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

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

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

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

动态 SQL

有时，需要在给定一些输入参数的情况下动态构造 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;

或在 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)) {
  ..
}

或在 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)) {
  ..
}

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

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

Answer 8

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

SQL-92 标准有一整章关于动态 SQL（第 17 章），但它仅适用于完整的 SQL-92，而且据我所知还没有供应商实现了它。

Answer 9

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