mysql - 我如何在 MySqlWhere 子句中使用仅适用于列不为空的可选参数？

Question

我试图根据与插入对象的匹配从数据库中选择行，但在 WHERE 子句中，我需要条件仅适用于该列值不为空的情况。因此，基本上仅当比较表中的列不为空时，才将列与插入行中的列进行比较。我希望我解释得足够清楚，抱歉英语不好。

我的 table 看起来像这样:

id | provider | provider_sub | region
1    2          200            3
2    3          null           5
3    3          301            8
4    4          409            null
5    5          null           null

上表表示用于对插入对象进行分类的过滤器，如下所示:

{provider:2,provider_sub:200,region:null} -> no matching row
{provider:3,provider_sub:null,region:5} -> matching with row 2
{provider:4,provider_sub:409,region:6} -> no matching row
{provider:5,provider_sub:7,region:2} -> matching with row 4

我的尝试是这样的:

SET @provider = 1;
SET @provider_sub = null;
SET @region = null;

SELECT * FROM pool_filter 
WHERE 
    provider = IF(provider IS NOT NULL, @provider, provider) AND
    provider_sub = IF(provider_sub IS NOT NULL, @provider_sub, provider_sub) AND


      region = IF(region IS NOT NULL, @region, region) AND
  COALESCE(@provider,@provider_sub, @region) IS NOT NULL;

我无法让它按预期工作，因为当将提供程序设置为 5 并且其余为 null 进行查询时，它没有像我预期的那样选择第 4 行？这是为什么？

Answer 1

这里的问题是比较的任意端都可能存在NULL值。 SQL 中的 NULL 比较与非 NULL 值不同。这是我推荐的查询:

SELECT *
FROM pool_filter 
WHERE 
    (provider = @provider OR @provider IS NULL) AND
    (provider_sub = @provider_sub OR @provider_sub IS NULL) AND
    (region = @region OR @region IS NULL);

使用此逻辑，如果列和用户变量均非 NULL 且匹配，则 WHERE 子句中的每个条件都将匹配，或 用户变量为NULL。例如，在 @provider 的 NULL 值的情况下，该条件将有效地退出。