如何在MySQL函数中实现SELECT查询的动态多条件筛选

当前可用函数代码 #

CREATE FUNCTION getStock (productId INT, customerId INT) RETURNS INT(11)
BEGIN 
    DECLARE qty INT(11) DEFAULT 0;
    
    IF (customerId = 0)
        THEN
            SELECT (......) INTO qty 
            WHERE product_id = productId;
        ELSE
            SELECT (...) INTO QTY 
            WHERE product_id = productId AND customer_id = customerId;
    ENDIF;
    
    RETURN qty;
END;

重构需求 #

需要将函数修改为接收productId、forceCustomerId、ignoreCustomerId三个参数，实现以下动态筛选逻辑：

• 当forceCustomerId非空且不为0时，强制匹配该客户ID；
• 当ignoreCustomerId非空且不为0时，排除该客户ID。

之前尝试通过拼接SQL语句实现，但不确定正确的语法和方法。

解决方案 #

方法一：静态SQL实现（推荐，无注入风险） #

不需要动态拼接SQL，直接在WHERE子句中用逻辑表达式处理条件，MySQL优化器能更好地处理查询计划，同时避免SQL注入问题：

CREATE FUNCTION getStock (productId INT, forceCustomerId INT, ignoreCustomerId INT) RETURNS INT(11)
BEGIN 
    DECLARE qty INT(11) DEFAULT 0;
    
    -- 替换成你实际的库存计算逻辑（比如SUM、MAX等）和表名
    SELECT COALESCE(SUM(stock_quantity), 0) INTO qty
    FROM your_stock_table
    WHERE product_id = productId
      -- 处理强制匹配客户ID：参数无效时不限制，否则强制匹配
      AND (forceCustomerId IS NULL OR forceCustomerId = 0 OR customer_id = forceCustomerId)
      -- 处理排除客户ID：参数无效时不限制，否则排除该ID
      AND (ignoreCustomerId IS NULL OR ignoreCustomerId = 0 OR customer_id != ignoreCustomerId);
    
    RETURN qty;
END;

方法二：动态SQL实现（适合复杂动态场景） #

如果必须用动态拼接SQL的方式，需要注意使用预处理语句避免注入，同时处理不同参数组合的执行逻辑：

DELIMITER //
CREATE FUNCTION getStock (productId INT, forceCustomerId INT, ignoreCustomerId INT) RETURNS INT(11)
BEGIN 
    DECLARE qty INT(11) DEFAULT 0;
    DECLARE query VARCHAR(1000);
    
    -- 初始化基础查询，替换成你实际的字段和表名
    SET query = 'SELECT COALESCE(SUM(stock_quantity), 0) INTO @qty FROM your_stock_table WHERE product_id = ?';
    
    -- 添加强制匹配条件
    IF forceCustomerId IS NOT NULL AND forceCustomerId != 0 THEN
        SET query = CONCAT(query, ' AND customer_id = ?');
    END IF;
    
    -- 添加排除客户条件
    IF ignoreCustomerId IS NOT NULL AND ignoreCustomerId != 0 THEN
        SET query = CONCAT(query, ' AND customer_id != ?');
    END IF;
    
    -- 定义用户变量传递参数（避免直接拼接值导致注入）
    SET @productId = productId;
    SET @forceCustomerId = forceCustomerId;
    SET @ignoreCustomerId = ignoreCustomerId;
    
    -- 预处理并执行动态SQL
    PREPARE stmt FROM query;
    CASE
        WHEN forceCustomerId IS NOT NULL AND forceCustomerId != 0 AND ignoreCustomerId IS NOT NULL AND ignoreCustomerId != 0 THEN
            EXECUTE stmt USING @productId, @forceCustomerId, @ignoreCustomerId;
        WHEN forceCustomerId IS NOT NULL AND forceCustomerId != 0 THEN
            EXECUTE stmt USING @productId, @forceCustomerId;
        WHEN ignoreCustomerId IS NOT NULL AND ignoreCustomerId != 0 THEN
            EXECUTE stmt USING @productId, @ignoreCustomerId;
        ELSE
            EXECUTE stmt USING @productId;
    END CASE;
    DEALLOCATE PREPARE stmt;
    
    SET qty = @qty;
    RETURN qty;
END //
DELIMITER ;

注意：替换代码中的your_stock_table和stock_quantity为你实际的表名和库存字段，同时调整SELECT后的聚合逻辑匹配原函数的业务需求。

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