使用 SQL 创建动态 where 子句

本文翻译自 Creating Dynamic Where Clauses with SQL
译者：耐心的农夫2020
时间：2020-04-12

使用用户界面数据库查询时，一种非常常见的场景是“动态搜索查询”，即在 where 子句中包含可选条件的 select 语句。尽管有很多方法可以实现这种效果，但是不同的方法复杂程度不一样。总的来说，不同方法在实现、可读性、性能和可读性方面做了取舍。本文介绍了实现这个效果的不同方法。

配置介绍

在我讨论不同方法之前，我想要先展示一下创建表和插入记录的脚本，以便说明接下来所有的概念。下面的清单创建了一张具有两个 VARCHAR 字段的表，并填充了一些数据，包括空值。

CREATE TABLE TestData
(
    ColumnA VARCHAR(20),
    ColumnB VARCHAR(25)
)

-- Insert test data
INSERT INTO TestData
    (ColumnA)
VALUES
    ('Me')

INSERT INTO TestData
    (ColumnA, ColumnB)
VALUES
    ('Me', 'You')

INSERT INTO TestData
    (ColumnA, ColumnB)
VALUES
    ('You', 'Me')

运行上面的脚本后，我们就有了下面这样一张表。

ColumnA	ColumnB
Me	null
Me	You
You	Me

现在我们有了数据，我们可以讨论一个搜索过程的实现，该过程允许同时搜索 ColumnA 和 ColumnB，但是两个搜索都可以省略。如果未选择任何内容，则应该返回所有条目，而不仅仅是“空”条目。

动态 SQL

通常，想要构建动态 SQL 语句以满足这些需求的人第一个想法就是使用动态 SQL 进行构建。我们大多数人已经了解使用动态 SQL 相关的问题和风险。通常这种办法应该是最后一个选项，因为这样做不安全。下面是一个如何通过动态 SQL 进行操作的快速示例。

DECLARE @ColumnA VARCHAR(20)
DECLARE @ColumnB VARCHAR(25)
DECLARE @SQL NVARCHAR(1000)

SET @SQL = 'SELECT
    ColumnA,
    ColumnB
FROM TestData
WHERE 1=1'

IF(@ColumnA IS NULL)
BEGIN
    SET @SQL = @SQL + '
               AND ColumnA = ''' + @ColumnA + ''''
END

IF(@ColumnB IS NULL)
BEGIN
    SET @SQL = @SQL + ' 
                AND ColumnB = ''' + @ColumnB + ''''
END

EXEC(@SQL)

总的来说，这是可行的。但是在 @ColumnA 和 @ColumnB 数据中进行级联存在风险。一个单引号 ' 或者其他字符会破坏脚本的执行。不推荐使用这种方法查询系统。个人认为这种方法是“最后一搏”的努力。

使用 Coalesce

第二种方法（我过去用过）使用 SQL Server 中提供的 Coalesce 函数。这个函数的作用是接受多个参数，并返回第一个非空的参数。所以如果你执行 COALESCE(@ColumnA, ColumnA) ，如果参数值为空，那就用这一列的值替换，使得该条件总是为 true。下面是使用这种方法的一个例子。

DECLARE @ColumnA VARCHAR(20)
DECLARE @ColumnB VARCHAR(25)

SELECT
    ColumnA,
    ColumnB
FROM TestData
WHERE ColumnA = COALESCE(@ColumnA, ColumnA)
    AND ColumnB = COALESCE(@ColumnB, ColumnB)

现在这是更加简洁的一种方法，而且格式相当容易阅读。但是，当对可能为空的数据进行过滤时，可能会碰到问题。我们的测试数据得出的结果集实际上类似于以下内容。

ColumnA	ColumnB
Me	You
You	Me

结果不包含 ColumnB 为空的第一行数据，这是因为 COALESCE 没有给该字段提供空值并且没有使条件为 true。所以这种方法在处理非空数据列的时候非常有效，但是如果有空的列，就不是一种好方法。

使用 CASE

我要提供的第三种方法解决了 COALESCE 方法的空列问题和动态 SQL 方法的安全问题。虽然这种方法最好，但是有一个缺点，可读性差。这种方法在每个列的两边使用 CASE 子句，如果参数为空，就使用值 1，从而生成了 1 = 1 子句；如果参数不为空，就构建了一个标准格式的 where 子句。下面是一个简单实现。

DECLARE @ColumnA VARCHAR(20)
DECLARE @ColumnB VARCHAR(25)

SELECT 
    ColumnA,
    ColumnB
FROM TestData
WHERE CASE WHEN @ColumnA IS NULL
            THEN 1
            ELSE ColumnA
      END = CASE WHEN @ColumnA IS NULL
                    THEN 1
                    ELSE @ColumnA
      END
    AND CASE WHEN @ColumnB IS NULL
            THEN 1
            ELSE ColumnB
      END = CASE WHEN @ColumnB IS NULL
                    THEN 1
                    ELSE @ColumnB
      END

这种方法可读性很差，但是可行的，并且不会使用动态 SQL 创建查询。与其他方法相比，查看执行计划发现不会对性能产生影响。最好的部分是，它可以显示所有记录，并且是真正动态的，并且满足我们的特定需求。

总结

我希望本文能够作为一个很好的概述，展示一些在 SQL Server 语句中创建动态 where 子句的不同方法。我确信还有其他方法也能达到同样的效果。但是本文展示的是我觉得有用的方法。请随时在下面分享您的反馈。