MySQL存储过程如何在参数为NULL或缺失时返回全表数据？

核心方案 #

给存储过程参数设置默认值NULL，同时修改查询的WHERE子句，让参数为NULL时自动忽略对应过滤条件，既避免参数缺失时报错，又能在参数无效时返回全表数据。

具体实现代码 #

DELIMITER //
CREATE PROCEDURE GetInstruments_ByFilter(
    -- 给参数添加DEFAULT NULL，确保不传参数时自动设为NULL，避免报错
    IN p_instrument_id INT DEFAULT NULL,
    IN p_instrument_nm VARCHAR(255) DEFAULT NULL
)
BEGIN
    SELECT INSTRUMENT_ID, INSTRUMENT_NM
    FROM tba_instruments
    WHERE 
        -- 参数为NULL时，该条件恒成立，忽略对INSTRUMENT_ID的过滤
        (p_instrument_id IS NULL OR INSTRUMENT_ID = p_instrument_id)
        -- 同理处理INSTRUMENT_NM的过滤
        AND (p_instrument_nm IS NULL OR INSTRUMENT_NM = p_instrument_nm);
END //
DELIMITER ;

逻辑说明 #

1. 参数默认值：每个参数声明时加上DEFAULT NULL，调用存储过程时如果不传该参数，MySQL会自动将其赋值为NULL，不会触发参数缺失的报错。
2. 动态过滤条件：WHERE子句中每个字段的过滤逻辑采用参数 IS NULL OR 字段 = 参数：
   • 当参数为NULL时，参数 IS NULL为真，整个条件成立，相当于不对该字段做过滤；
   • 当参数有有效值时，会执行字段 = 参数的匹配逻辑；
   • 如果所有参数都为NULL，WHERE子句整体为真，直接返回全表数据。

可选扩展（处理空字符串场景） #

如果需要兼容参数传入空字符串（''）时也忽略过滤的场景，可以调整条件为：

WHERE 
    (p_instrument_id IS NULL OR INSTRUMENT_ID = p_instrument_id)
    AND (p_instrument_nm IS NULL OR p_instrument_nm = '' OR INSTRUMENT_NM = p_instrument_nm);

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