MySQL存储过程中逗号分隔ID字符串适配IN子句问题

我有一个查询客户表的MySQL存储过程find_customers，定义如下：

find_customers(IN customer_ids VARCHAR(255), OUT customer_data JSON)
BEGIN
   SELECT 
    JSON_ARRAYAGG(
        JSON_OBJECT(main_table.id, main_table.name)
    ) INTO customer_data
   FROM
        customers AS main_table
  WHERE
    main_table.id IN(customer_ids);
   
END

问题是传入的customer_ids是逗号分隔的ID字符串（比如'1,3'），但IN子句无法识别这种格式，把整个字符串当成单一值匹配，导致查询不到数据。我试过以下三种方法都没用：

• main_table.id IN(REPLACE(customer_ids, "'", ""))
• main_table.id IN(TRIM(BOTH "'" FROM customer_ids))
• main_table.id IN(SUBSTRING_INDEX(customer_ids, ",", 100))

客户表示例数据：

期望传入customer_ids为'1,3'时，返回结果：

[
   {
        "1": "Gul"
    },
    {
        "3": "Qayoom"
    }
]

问题原因 #

你之前的方法无效，核心原因是IN()接收字符串参数时，会把它当成单一整体值处理。比如IN('1,3')等价于匹配id = '1,3'的记录，而非id=1或id=3。字符串处理函数只是修改了字符串内容，但本质还是单一字符串，无法拆分出多个独立ID。

解决方案 #

方法1：使用FIND_IN_SET函数（兼容所有MySQL版本）

直接用FIND_IN_SET函数替代IN子句，该函数会自动拆分逗号分隔的字符串并匹配元素：

修改后的存储过程：

find_customers(IN customer_ids VARCHAR(255), OUT customer_data JSON)
BEGIN
   SELECT 
    JSON_ARRAYAGG(
        JSON_OBJECT(main_table.id, main_table.name)
    ) INTO customer_data
   FROM
        customers AS main_table
  WHERE
    FIND_IN_SET(main_table.id, customer_ids) > 0;
   
END

方法2：使用JSON_TABLE（MySQL 8.0+推荐）

如果你的MySQL版本是8.0及以上，可将字符串转成JSON数组，再通过JSON_TABLE生成临时表关联查询，性能优于FIND_IN_SET：

修改后的存储过程：

find_customers(IN customer_ids VARCHAR(255), OUT customer_data JSON)
BEGIN
   SELECT 
    JSON_ARRAYAGG(
        JSON_OBJECT(main_table.id, main_table.name)
    ) INTO customer_data
   FROM
        customers AS main_table
  JOIN JSON_TABLE(
    CONCAT('["', REPLACE(customer_ids, ',', '","'), '"]'),
    '$[*]' COLUMNS(id INT PATH '$')
  ) AS ids ON main_table.id = ids.id;
   
END

这里先把'1,3'转换为["1","3"]格式的JSON数组，再通过JSON_TABLE生成包含id列的临时表，最后与客户表关联筛选。

方法3：动态SQL（需注意SQL注入）

如果坚持用IN子句形式，可拼接动态SQL执行，但必须添加参数格式验证，避免注入风险：

修改后的存储过程：

find_customers(IN customer_ids VARCHAR(255), OUT customer_data JSON)
BEGIN
   -- 验证参数仅包含数字和逗号，防止SQL注入
   IF customer_ids REGEXP '^[0-9,]+$' THEN
      SET @sql = CONCAT(
          'SELECT JSON_ARRAYAGG(JSON_OBJECT(id, name)) INTO @customer_data FROM customers WHERE id IN(', customer_ids, ')'
      );
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      SET customer_data = @customer_data;
   ELSE
      -- 参数非法时返回空JSON数组
      SET customer_data = '[]';
   END IF;
END

该方法会动态生成WHERE id IN(1,3)格式的SQL语句，正则验证确保参数仅含合法字符。

以上三种方法，传入customer_ids = '1,3'时，均可返回期望的JSON结果。

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