如果参数为空，则 MySQL 存储过程会跳过 WHERE 条件

Question

我正在编写应该根据几个过程参数搜索记录的存储过程。问题是并非所有参数都必须传递，有时它们可​​能被设置为 NULL。

有没有办法写出这样的东西？

CREATE PROCEDURE testProc(IN p_idWorker INTEGER, IN p_idEffect INTEGER)
BEGIN
    SELECT 
        * 
    FROM 
        CallHistory
    WHERE
        idWorker = IFNULL(p_idWorker, ANYTHING)
        AND 
        idEffect = IFNULL(p_idEffect, ANYTHING);
END$$

Answer 1

像这样:

 ...
 WHERE (p_idWorker IS NULL OR idWorkder = p_idWorker)
   AND (p_idEffect IS NULL OR idEffect = p_idEffect);

或者，就像您所做的那样，但不是 Anything，而是像这样使用列名:

   ...
   WHERE
        idWorker = IFNULL(p_idWorker, idWorker )
        AND 
        idEffect = IFNULL(p_idEffect, idEffect );