需要动态搜索多个SQL表格的模式

Question

我正在寻找一种动态搜索多个表的模式。

我无法控制遗留（且设计不良）的数据库表结构。

考虑一个类似于简历搜索的场景，用户可以针对简历中的任何数据执行搜索，并返回符合其搜索条件的简历列表。任何字段都可以随时进行搜索，并与一个或多个其他字段组合进行搜索。

实际的SQL查询根据搜索的字段动态创建。大多数解决方案都涉及复杂的if语句块，但我不能不认为现在必须有更优雅的解决方案，因为这必须是一个已经解决的问题。

嗯，所以我已经开始通过代码动态构建SQL。看起来非常糟糕。如果我真的想支持请求查询任何表中任何字段的任何组合，这将是一个巨大的if语句集合。

我相信我读到过COALESCE仅在数据不包含NULL值时才起作用。那是正确的吗？如果是这样，就行不通，因为我的数据中到处都是NULL值。

- Geoff

Answer 1

据我所知（我也是写过反对可怕遗留数据库的人之一），动态WHERE子句是不存在的。这个问题还没有被解决。

个人而言，我更喜欢在代码中生成我的动态搜索。这样测试很方便。请注意，在代码中创建SQL查询时，不要将用户输入连接在一起。使用@变量！

唯一的替代方法是使用COALESCE运算符。假设您有以下表：

Users
-----------
Name nvarchar(20)
Nickname nvarchar(10)

如果你想选择性地搜索名称或昵称，可以使用以下查询：

SELECT Name, Nickname
FROM Users
WHERE
    Name = COALESCE(@name, Name) AND
    Nickname =  COALESCE(@nick, Nickname)

如果你不想搜索任何东西，只需传入null。例如，将“brian”作为@name传入，将null作为@nick传入，将导致评估以下查询：

SELECT Name, Nickname
FROM Users
WHERE
    Name = 'brian' AND
    Nickname =  Nickname

合并运算符将null转换为一个恒等式评估，这个评估总是为真的，在where子句中不会产生影响。

- user1228

Answer 2

你需要的是类似于SphinxSearch（适用于MySQL）或Apache Lucene这样的工具。

就像你在例子中所说的，假设有一个由多个字段组成的简历：

- 名字 - 地址 - 教育（这可能是一个独立的表） - 工作经验（这可能会变成一个表，其中每一行代表以前的一份工作）

使用WHERE在所有这些字段中搜索单词很快就会变成一个非常长的查询，需要多个JOIN。

相反，你可以改变你的参考框架，把整个简历看作一个单一的文档，你只需要搜索该文档即可。

这就是Sphinx Search等工具的作用。它们创建了你的“文档”的全文索引，然后你可以查询Sphinx，它会告诉你在数据库中找到了哪条记录。

真正好的搜索结果。

不要担心这些工具不是您的关系型数据库的一部分，使用适当的模型“文档”而不是不正确的“表格”模型对于此应用程序将节省您很多麻烦。

- elviejo79

Answer 3

搜索和规范化可能会相互矛盾。因此，首先要做的可能是获得某种“视图”，显示所有可作为单个行进行搜索的字段，并使用单个键获取简历。然后，您可以在其前面添加类似于Lucene的内容，以便为这些行提供全文索引。它的工作方式是，您在该视图中请求“x”，它将返回给您该键。这是一个很好的解决方案，并且在Joel自己的播客中推荐，在前两个月内就有了。

- ChanChan