SQL存储过程传入多值实现分类产品批量查询的可行性问询

当然可以！完全能实现你想要的批量操作、减少数据库请求的需求，我给你分享几种实用的方案，覆盖不同数据库场景：

方案1：使用表值参数（SQL Server 专属，最适合传递矩阵数据） #

如果用的是SQL Server，**表值参数（TVP）**是传递多组结构化数据（也就是你说的“矩阵形式”）的最佳方式，它允许你直接把多行多列的数据集传入存储过程，不用做额外的字符串解析。

步骤示例： #

1. 先定义一个自定义表类型，用来匹配你要传入的多组值结构：

CREATE TYPE CategoryGroup AS TABLE (
    CategoryId INT,
    ParentId INT
);

2. 创建接收这个参数的存储过程，结合递归CTE获取所有子分类并批量查询产品：

CREATE PROCEDURE GetProductsByCategoryGroups
    @CategoryGroups CategoryGroup READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- 递归获取所有传入分类及其下属的所有子分类
    WITH RecursiveCategories AS (
        SELECT CategoryId, ParentId FROM @CategoryGroups
        UNION ALL
        SELECT c.CategoryId, c.ParentId
        FROM categories c
        INNER JOIN RecursiveCategories rc ON c.ParentId = rc.CategoryId
    )

    -- 一次性查询所有关联产品
    SELECT p.*
    FROM products p
    INNER JOIN RecursiveCategories rc ON p.CategoryId = rc.CategoryId;
END

3. 调用存储过程时，直接传入多行数据：

DECLARE @MyCategories CategoryGroup;
-- 插入多组分类ID和父ID（模拟矩阵数据）
INSERT INTO @MyCategories (CategoryId, ParentId)
VALUES (1, 0), (3, 1), (5, 2);

EXEC GetProductsByCategoryGroups @MyCategories;

方案2：用 JSON/XML 传递多组结构化数据（通用跨数据库） #

如果你的数据库不支持表值参数（比如MySQL、PostgreSQL早期版本），可以用JSON或XML来封装多组数据，在存储过程里解析成临时数据集后再处理，兼容性极强。

MySQL 示例（用 JSON）： #

DELIMITER //
CREATE PROCEDURE GetProductsByCategoryGroups(IN categoryData JSON)
BEGIN
    -- 创建临时表存储解析后的分类数据
    CREATE TEMPORARY TABLE TempCategories (
        CategoryId INT,
        ParentId INT
    );

    -- 解析JSON数组到临时表
    INSERT INTO TempCategories
    SELECT
        JSON_UNQUOTE(JSON_EXTRACT(categoryData, CONCAT('$[', idx, '].CategoryId'))) AS CategoryId,
        JSON_UNQUOTE(JSON_EXTRACT(categoryData, CONCAT('$[', idx, '].ParentId'))) AS ParentId
    FROM
        (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS indexList
    WHERE
        JSON_EXTRACT(categoryData, CONCAT('$[', idx, ']')) IS NOT NULL;

    -- 递归获取所有子分类并查询产品
    WITH RECURSIVE RecursiveCategories AS (
        SELECT CategoryId FROM TempCategories
        UNION ALL
        SELECT c.CategoryId
        FROM categories c
        INNER JOIN RecursiveCategories rc ON c.ParentId = rc.CategoryId
    )

    SELECT p.*
    FROM products p
    INNER JOIN RecursiveCategories rc ON p.CategoryId = rc.CategoryId;

    -- 清理临时表
    DROP TEMPORARY TABLE TempCategories;
END //
DELIMITER ;

调用时传入JSON字符串：

CALL GetProductsByCategoryGroups('[{"CategoryId":1,"ParentId":0},{"CategoryId":3,"ParentId":1}]');

PostgreSQL 可以用jsonb_to_recordset函数更简洁地解析JSON，原理类似。

方案3：逗号分隔ID字符串+拆分函数（适合简单多ID场景） #

如果你的需求只是传入多个分类ID（不需要多列的矩阵数据），可以用逗号分隔的字符串传递，再通过数据库的拆分函数转成表，这种方式实现最简单。

SQL Server 示例： #

CREATE PROCEDURE GetProductsByCategoryIds
    @CategoryIdList NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 拆分逗号分隔的ID为临时表
    DECLARE @TempIds TABLE (CategoryId INT);
    INSERT INTO @TempIds
    SELECT VALUE FROM STRING_SPLIT(@CategoryIdList, ',');

    -- 递归获取子分类并查询产品
    WITH RecursiveCategories AS (
        SELECT CategoryId FROM @TempIds
        UNION ALL
        SELECT c.CategoryId
        FROM categories c
        INNER JOIN RecursiveCategories rc ON c.ParentId = rc.CategoryId
    )

    SELECT p.*
    FROM products p
    INNER JOIN RecursiveCategories rc ON p.CategoryId = rc.CategoryId;
END

调用：

EXEC GetProductsByCategoryIds '1,3,5';

额外优化提示 #

• 不管用哪种方案，递归CTE都是关键：它能一次性获取所有传入分类的子分类，避免你为每个子分类单独发起查询，从根源上减少数据库请求次数。
• 记得给categories表的ParentId字段加索引，给products表的CategoryId字段加索引，这会让递归和关联查询的性能提升非常明显。
• 如果数据库支持，优先选表值参数或原生JSON解析的方案，比字符串拆分更高效、更不容易出错。

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