编写含动态SQL的存储过程：基于Coupon表query列匹配URL

背景说明 #

现有一张名为Coupon的数据库表，其中query列存储着WHERE语句格式的逻辑条件字符串，示例如下：

coupon1.query
=> "'/hats' = :url"
coupon2.query
=> "'/pants' = :url OR '/shoes' = :url"

核心需求 #

编写一个存储过程，输入参数为：

• 优惠券ID列表
• 当前URL

该存储过程需完成以下操作：

1. 读取每个指定优惠券的query列值
2. 将URL参数代入条件字符串执行验证
3. 返回所有匹配成功的优惠券ID

预期行为示例 #

示例1：传入coupon1、coupon2的ID及@url='/hats'，返回coupon1的ID
示例2：传入coupon1、coupon2的ID及@url='/pants'，返回coupon2的ID
示例3：传入coupon1、coupon2的ID及@url='/shirts'，无ID返回

ActiveRecord验证逻辑示例 #

可以用Ruby on Rails的ActiveRecord测试该匹配逻辑：

@url = '/hats'
@query = coupon1.query 
# 结果为 "'/hats' = :url"
Coupon.where(@query, url: @url).count
=> 2   
# 匹配成功，coupon1的ID应被返回

@query = coupon2.query 
# 结果为 "'/pants' = :url OR '/shoes' = :url"
Coupon.where(@query, url: @url).count
=> 0
# 匹配失败，coupon2的ID不返回

当前低效的循环实现 #

目前可通过ActiveRecord循环实现，但当优惠券数量庞大、且query包含复杂AND/OR逻辑时，性能会很差：

# 假设coupon1的ID为1，coupon2的ID为2
@coupons = [coupon1, coupon2]
@url = '/hats'
@coupons.map do |coupon|
    if Coupon.where(coupon.query, url: @url).count > 0
        coupon.id
    else
        nil
    end
end
=> [1, nil]

存储过程实现（以PostgreSQL为例） #

以下是PostgreSQL环境下的存储过程实现，利用动态SQL完成条件验证：

CREATE OR REPLACE FUNCTION match_coupons(p_coupon_ids INT[], p_url TEXT)
RETURNS SETOF INT AS $$
DECLARE
    v_coupon RECORD;
    v_query TEXT;
    v_match BOOLEAN;
BEGIN
    -- 遍历传入的优惠券ID列表
    FOR v_coupon IN SELECT id, query FROM coupon WHERE id = ANY(p_coupon_ids) LOOP
        -- 替换参数占位符为实际URL值，同时转义避免SQL注入
        v_query := REPLACE(v_coupon.query, ':url', quote_literal(p_url));
        
        -- 执行动态SQL验证条件是否成立
        EXECUTE 'SELECT EXISTS(' || v_query || ')' INTO v_match;
        
        -- 若匹配成功，返回当前优惠券ID
        IF v_match THEN
            RETURN NEXT v_coupon.id;
        END IF;
    END LOOP;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;

使用方式 #

调用存储过程获取匹配的优惠券ID：

-- 示例1：传入ID数组[1,2]和URL'/hats'
SELECT * FROM match_coupons(ARRAY[1,2], '/hats');
-- 返回结果：1

-- 示例2：传入ID数组[1,2]和URL'/pants'
SELECT * FROM match_coupons(ARRAY[1,2], '/pants');
-- 返回结果：2

-- 示例3：传入ID数组[1,2]和URL'/shirts'
SELECT * FROM match_coupons(ARRAY[1,2], '/shirts');
-- 返回结果：无数据

注意事项 #

1. SQL注入风险：使用quote_literal函数对URL参数进行转义，避免恶意注入
2. 兼容性：若使用MySQL，存储过程语法需调整，核心逻辑保持一致
3. 性能优化：对coupon.id建立索引，可提升遍历查询的效率

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