MySQL存储过程：基于配送公司参数的条件式连接与过滤实现咨询

在MySQL存储过程中，针对不同配送公司类型的分支逻辑，主要有两种实现方式，以下是具体示例和说明：

方式一：IF分支直接编写不同查询逻辑 #

适合分支逻辑差异较大、代码可读性要求高的场景，直接通过IF/ELSEIF区分不同参数对应的查询语句：

DELIMITER //

CREATE PROCEDURE GetDeliveryRecords(IN p_delivery_type VARCHAR(50))
BEGIN
    IF p_delivery_type = 'CENTRAL_FULFILMENT' THEN
        -- 中央仓配送：关联额外表过滤
        SELECT 
            o.order_id,
            o.customer_name,
            cf.warehouse_id,
            cf.inventory_status
        FROM orders o
        JOIN central_fulfilment cf ON o.order_id = cf.order_id
        JOIN warehouse w ON cf.warehouse_id = w.warehouse_id
        WHERE w.is_active = 1;
    ELSEIF p_delivery_type = 'STANDARD' THEN
        -- 标准配送：WHERE子句添加特定过滤条件
        SELECT 
            o.order_id,
            o.customer_name,
            o.delivery_address
        FROM orders o
        WHERE o.delivery_method = 'STANDARD' 
          AND o.order_status = 'SHIPPED';
    ELSE
        -- 处理其他未匹配的配送类型（可选）
        SELECT 
            o.order_id,
            o.customer_name
        FROM orders o;
    END IF;
END //

DELIMITER ;

方式二：动态SQL拼接语句 #

适合逻辑灵活、分支较多且存在代码复用场景的需求，通过拼接SQL字符串实现动态逻辑：

DELIMITER //

CREATE PROCEDURE GetDeliveryRecordsDynamic(IN p_delivery_type VARCHAR(50))
BEGIN
    DECLARE sql_query VARCHAR(1000);
    
    -- 初始化基础查询字段
    SET sql_query = 'SELECT o.order_id, o.customer_name ';
    
    -- 根据参数拼接关联表、字段和过滤条件
    IF p_delivery_type = 'CENTRAL_FULFILMENT' THEN
        SET sql_query = CONCAT(sql_query, ', cf.warehouse_id, cf.inventory_status ');
        SET sql_query = CONCAT(sql_query, 'FROM orders o ');
        SET sql_query = CONCAT(sql_query, 'JOIN central_fulfilment cf ON o.order_id = cf.order_id ');
        SET sql_query = CONCAT(sql_query, 'JOIN warehouse w ON cf.warehouse_id = w.warehouse_id ');
        SET sql_query = CONCAT(sql_query, 'WHERE w.is_active = 1 ');
    ELSEIF p_delivery_type = 'STANDARD' THEN
        SET sql_query = CONCAT(sql_query, ', o.delivery_address ');
        SET sql_query = CONCAT(sql_query, 'FROM orders o ');
        SET sql_query = CONCAT(sql_query, 'WHERE o.delivery_method = ''STANDARD'' ');
        SET sql_query = CONCAT(sql_query, 'AND o.order_status = ''SHIPPED'' ');
    ELSE
        SET sql_query = CONCAT(sql_query, 'FROM orders o ');
    END IF;
    
    -- 执行动态生成的SQL
    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

关键注意事项 #

• 若参数可能包含外部输入，使用动态SQL时需避免直接拼接参数，改用占位符绑定防止SQL注入，示例如下：

  -- 安全的动态SQL参数绑定示例
  IF p_delivery_type = 'STANDARD' THEN
      SET sql_query = 'SELECT o.order_id, o.customer_name FROM orders o WHERE o.delivery_method = ? AND o.order_status = ?';
      PREPARE stmt FROM sql_query;
      SET @method = 'STANDARD';
      SET @status = 'SHIPPED';
      EXECUTE stmt USING @method, @status;
  END IF;
  

• 建议添加ELSE分支处理未匹配的参数值，避免无返回结果的情况。

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