c# - sql查询中的可选搜索参数和具有空值的行

Question

好的，这是我的问题:

在我开始描述之前，让我告诉你，我已经在谷歌上搜索了很多，我发布这个问题是为了寻找一个好的最佳解决方案:)

我正在 WCF 上构建休息服务以获取 userProfiles...用户可以通过提供类似 userProfiles?location=London 的内容来过滤 userProfiles

现在我有以下方法

GetUserProfiles(string firstname, string lastname, string age, string location)

我构建的 sql 查询字符串是:select firstname, lastname, .... from profiles where (firstName like '%{firstname}%') AND (lastName like '%{lastName}%') ....等等所有变量都被字符串格式化程序替换。

问题在于它会过滤任何具有空值的名字、姓氏、年龄或位置的行....

做类似 (firstName like '%{firstName}%' OR firstName IS NULL) 的事情会很乏味，而且语句会变得无法维护! (在这个例子中只有 4 个参数，但在我的实际方法中有 10 个)

最好的解决方案是什么？....通常如何处理这种情况？

使用的数据库:MySql

Answer 1

NULL 的一个基本属性是，当您将它与任何(包括NULL)进行比较时，它返回false，这就是为什么您所做的不起作用。

所以要回答的第一个问题是:当他们输入 lastname 时，为什么要返回带有 NULL lastname 的行“史密斯”？可能您的意思是要么名字或姓氏需要匹配才能返回，在这种情况下您没有运行正确的查询。最天真的解决方案是:

SELECT firstname, lastname, ....
FROM profiles
WHERE IFNULL(firstName LIKE'%{firstname}%'
OR lastName LIKE '%{lastName}%'

现在这将适用于数百甚至可能是数千行，但由于以下几个原因无法扩展:

1. OR 在性能方面通常很糟糕。尽可能避免使用它们。如果您查看由经验丰富的程序员编写的数据库应用程序，您可能不会找到一个单一的 OR 条件(除了那些在拥有一次的背后宣传 OR 优点的人)编写了一个每月有 3 个用户和 2 个点击的留言簿应用程序)；
2. 在前面执行带有 % 的 LIKE 将意味着不会起诉任何索引。

(2) 有多种解法。可能在 MySQL 中最简单的是使用 full text searching在 MyISAM 表上。如果您输入“han”，它不会找到像“Johannes”这样的匹配项，但通常就足够了。

通常您通过在多个查询中使用 UNION 或(最好)UNION ALL 来处理 OR 条件。例如:

SELECT firstname, lastname, ....
FROM profiles
WHERE firstName LIKE'%{firstname}%'
AND lastName LIKE '%{lastName}%'
UNION ALL
SELECT firstname, lastname, ....
FROM profiles
WHERE firstName LIKE'%{firstname}%'
AND lastname IS NULL
UNION ALL
SELECT firstname, lastname, ....
FROM profiles
WHERE firstName IS NULL
AND lastName LIKE '%{lastName}%'
UNION ALL
SELECT firstname, lastname, ....
FROM profiles
WHERE firstName IS NULL
AND lastName IS NULL

看起来又大又丑，但 UNION ALL 的扩展性非常好(忽略 LIKE 条件开头的 %)。它基本上是连接(在本例中)四个查询。 UNION 将在结果行上执行隐式 DISTINCT 但我们知道这里不会重叠，因为我们正在检查 NULL .

另一种可能性是不将NULL 视为您要搜索的内容。这是一个更好、更直观的解决方案(恕我直言)。如果有人输入姓氏“Smith”，您真的希望显示姓氏为 NULL 的行吗？

或者您希望他们出现是因为您的名字可能匹配？如果是这样，您需要一个稍微不同的查询。例如:

SELECT firstname, lastname, ....
FROM profiles
WHERE id IN (
  SELECT id 
  FROM profiles
  WHERE firstName LIKE'%{firstname}%'
  UNION ALL
  SELECT id 
  FROM profiles
  WHERE lastName LIKE '%{lastName}%'
)