如何创建可变参数Varchar转Integer的SQL函数适配IN子句？

首先明确结论：你的需求是可行的，但关键在于让函数返回数据库能识别为集合的类型，因为IN子句需要的是一个值集合（要么是括号里的显式列表，要么是子查询返回的结果集）。不过要注意：几乎所有主流数据库都不支持IN fn_yesno_to_int('yes', 'no')这种无括号的写法——IN语法本身要求后面跟括号包裹的内容，所以实际写法会是IN (SELECT * FROM fn_yesno_to_int('yes', 'no'))，这应该是你能接受的（毕竟你只是不想改IN子句的核心逻辑，只是用函数替代手动写0/1）。

下面针对主流数据库给出具体实现：

PostgreSQL 实现 #

PostgreSQL支持可变参数（VARIADIC）和返回集合类型（SETOF INTEGER），非常适合你的场景：

CREATE OR REPLACE FUNCTION fn_yesno_to_int(VARIADIC args TEXT[])
RETURNS SETOF INTEGER AS $$
BEGIN
  -- 遍历所有传入的参数
  FOREACH arg IN ARRAY args LOOP
    CASE arg
      WHEN 'yes' THEN RETURN NEXT 1;
      WHEN 'no' THEN RETURN NEXT 0;
      -- 处理无效输入：可以返回NULL、抛出错误，或者忽略这里选返回NULL
      ELSE RETURN NEXT NULL;
    END CASE;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

使用方式完全符合你的预期（只是多了子查询的括号）：

SELECT * FROM foobar WHERE is_valid IN (SELECT * FROM fn_yesno_to_int('yes', 'no'));

如果你觉得子查询麻烦，PostgreSQL还支持用= ANY替代IN，写法更简洁：

SELECT * FROM foobar WHERE is_valid = ANY(fn_yesno_to_int('yes', 'no'));

SQL Server 实现 #

SQL Server没有直接的可变参数语法，但可以通过表值函数实现。如果参数数量固定，内联表值函数是性能最优的选择：

CREATE FUNCTION fn_yesno_to_int(@arg1 VARCHAR(10), @arg2 VARCHAR(10))
RETURNS TABLE AS
RETURN (
  SELECT CASE @arg1 WHEN 'yes' THEN 1 WHEN 'no' THEN 0 ELSE NULL END AS val
  UNION ALL
  SELECT CASE @arg2 WHEN 'yes' THEN 1 WHEN 'no' THEN 0 ELSE NULL END AS val
);

如果需要支持任意数量的参数，可以用多语句表值函数结合表值参数：

CREATE TYPE YesNoList AS TABLE (val VARCHAR(10));
GO

CREATE FUNCTION fn_yesno_to_int(@args YesNoList READONLY)
RETURNS @result TABLE (val INT) AS
BEGIN
  INSERT INTO @result
  SELECT 
    CASE val 
      WHEN 'yes' THEN 1 
      WHEN 'no' THEN 0 
      ELSE NULL 
    END
  FROM @args;
  RETURN;
END;

使用方式：

-- 固定参数版本
SELECT * FROM foobar WHERE is_valid IN (SELECT val FROM fn_yesno_to_int('yes', 'no'));

-- 可变参数版本
DECLARE @params YesNoList;
INSERT INTO @params VALUES ('yes'), ('no');
SELECT * FROM foobar WHERE is_valid IN (SELECT val FROM fn_yesno_to_int(@params));

MySQL 实现 #

MySQL 8.0.19及以后支持表值函数，我们可以通过JSON数组来处理可变参数：

CREATE FUNCTION fn_yesno_to_int(json_args JSON)
RETURNS TABLE (val INT)
DETERMINISTIC
BEGIN
  RETURN (
    SELECT 
      CASE value 
        WHEN 'yes' THEN 1 
        WHEN 'no' THEN 0 
        ELSE NULL 
      END AS val
    FROM JSON_TABLE(json_args, '$[*]' COLUMNS(value VARCHAR(10) PATH '$')) AS jt
  );
END;

使用方式：

SELECT * FROM foobar WHERE is_valid IN (SELECT val FROM fn_yesno_to_int('["yes", "no"]'));

如果想用逗号分隔字符串代替JSON，也可以调整函数逻辑，用STRING_SPLIT（MySQL 8.0+支持）：

CREATE FUNCTION fn_yesno_to_int(csv_args VARCHAR(255))
RETURNS TABLE (val INT)
DETERMINISTIC
BEGIN
  RETURN (
    SELECT 
      CASE value 
        WHEN 'yes' THEN 1 
        WHEN 'no' THEN 0 
        ELSE NULL 
      END AS val
    FROM STRING_SPLIT(csv_args, ',')
  );
END;

使用：

SELECT * FROM foobar WHERE is_valid IN (SELECT val FROM fn_yesno_to_int('yes,no'));

注意事项 #

• 无效输入处理：上面的例子都默认返回NULL，你可以根据业务需求改成抛出错误（比如PostgreSQL用RAISE EXCEPTION，SQL Server用THROW）或者忽略无效值。
• 性能考量：确保函数是确定性的（相同输入返回相同输出），这样数据库可以缓存函数结果，避免重复计算。
• 语法限制：再次强调，IN fn_yesno_to_int(...)这种无括号的写法在主流数据库里都不被支持，因为IN的语法规范要求后面必须是括号包裹的列表或子查询——但IN (SELECT * FROM ...)已经是最接近你需求的写法了，不需要修改IN的核心逻辑，只是用函数生成了筛选值。

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