Mysql 之 IFNULL(expr1,expr2) 对空不可判

目标

当传入参数 @OrderId为空时 不做过滤 sql语句如下

SELECT o.*  FROM `order` AS o
LEFT JOIN receivemoneyconfirm AS re ON o.OrderID=re.FK_Order
WHERE re.FK_Order=IFNULL(@OrderId,re.FK_Order)

起先一直以为 当 @OrderId为空时 对receivemoneyconfirm 表是不做过滤的
也就是

SELECT o.*  FROM `order` AS o
LEFT JOIN receivemoneyconfirm AS re ON o.OrderID=re.FK_Order

与

SELECT o.*  FROM `order` AS o
LEFT JOIN receivemoneyconfirm AS re ON o.OrderID=re.FK_Order
WHERE re.FK_Order=re.FK_Order #1=1

查询结果是一致的

事实上

当字段 re.FK_Order 可为空时 WHERE re.FK_Order=re.FK_Order 并不等于 WHERE 1=1 ； 因为Mysql里 null的判断是 is null
所以

SELECT o.*  FROM `order` AS o
LEFT JOIN receivemoneyconfirm AS re ON o.OrderID=re.FK_Order
WHERE re.FK_Order=re.FK_Order 

这样写 其实对表receivemoneyconfirm 进行了过滤 过滤条件是 re.FK_Order 不为空

正确写法

用IF(expr1,expr2,expr3)替代IFNULL(expr1,expr2)

SELECT o.*  FROM `order` AS o
LEFT JOIN receivemoneyconfirm AS re ON o.OrderID=re.FK_Order
WHERE 
IF(@OrderId is not null,re.FK_Order=@OrderId, TRUE)