MySQL实现变量非空生效的条件WHERE子句及结果异常排查

你最开始写的CASE表达式没有加ELSE分支：当变量为NULL时，CASE的返回值也是NULL，在WHERE子句中NULL会被判定为逻辑假，直接把所有行都过滤掉，自然拿不到正确结果。
后来加了ELSE 1的写法逻辑上是成立的，出现结果不一致的情况，常见原因有两个：

• 两次查询前给用户变量的赋值不一致，要注意MySQL的用户变量是连接级别的，同一个连接里之前的赋值会一直保留，不会自动重置
• 用CASE包裹的条件会让MySQL优化器无法正常识别字段上的索引，全表扫描过程中如果出现字段和变量的隐式类型转换，很容易出现匹配偏差。

这种「变量非空才加过滤条件」的需求，不要用CASE写，直接用OR写等价逻辑即可，语义更直白，也更方便优化器做索引选择：

-- 变量定义
set @CountryId = null ;
set @CityId = 1324368075 ;
set @StateProvince = null ;
set @CategoryId = null ; 
set @TransactionTypeId = null;
set @Price = 5000;

SELECT 
  Count(*) 
FROM 
  meerkat.property 
WHERE 
  (@CountryId IS NULL OR CountryId = @CountryId)
  AND (@CityId IS NULL OR CityId = @CityId) 
  AND (@CategoryId IS NULL OR CategoryId = @CategoryId) 
  AND (@StateProvince IS NULL OR StateProvince = @StateProvince) 
  AND (@TransactionTypeId IS NULL OR TransactionTypeId = @TransactionTypeId) 
  AND (@Price IS NULL OR Price <= @Price) 
  AND IsPublic = 1 
  AND IsBlocked = 0;

这段逻辑和需求完全匹配：对每个过滤维度，如果对应变量为NULL，OR前面的判断直接为真，相当于这个维度不做过滤；如果变量不为NULL，就必须满足后面的字段匹配条件。

如果换了写法还是出现结果不匹配，按以下顺序检查：

• 每次执行查询前，先显式给所有变量重新赋值，避免连接内残留的旧值影响结果
• 核对字段类型和变量类型是否一致，比如CityId如果是varchar类型，传入数字值会触发隐式转换，导致部分数据匹配错误
• 如果两次查询间隔时间较长，先确认表中IsPublic、IsBlocked等固定过滤字段对应的数据没有发生增删改。

如果property表数据量超过百万级，上面的OR写法会让优化器很难选择最优索引，这时候更推荐用动态SQL拼接：只把非空变量对应的过滤条件拼到最终查询里，保证查询能命中对应索引，示例如下：

-- 初始化基础SQL，固定条件直接写死
SET @sql = 'SELECT Count(*) FROM meerkat.property WHERE IsPublic = 1 AND IsBlocked = 0';

-- 逐个判断变量，非空才拼接对应条件
IF @CountryId IS NOT NULL THEN
  SET @sql = CONCAT(@sql, ' AND CountryId = ', QUOTE(@CountryId));
END IF;
IF @CityId IS NOT NULL THEN
  SET @sql = CONCAT(@sql, ' AND CityId = ', QUOTE(@CityId));
END IF;
IF @StateProvince IS NOT NULL THEN
  SET @sql = CONCAT(@sql, ' AND StateProvince = ', QUOTE(@StateProvince));
END IF;
IF @CategoryId IS NOT NULL THEN
  SET @sql = CONCAT(@sql, ' AND CategoryId = ', QUOTE(@CategoryId));
END IF;
IF @TransactionTypeId IS NOT NULL THEN
  SET @sql = CONCAT(@sql, ' AND TransactionTypeId = ', QUOTE(@TransactionTypeId));
END IF;
IF @Price IS NOT NULL THEN
  SET @sql = CONCAT(@sql, ' AND Price <= ', QUOTE(@Price));
END IF;

-- 执行拼接好的SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

拼接时用QUOTE()函数处理变量值，可以避免SQL注入风险。

内容的提问来源于stack exchange，提问作者Amokachi