如何在MySQL存储过程中像MS-SQL一样传递表值参数？

嘿，这个问题问得很到位！MySQL本身并没有像MS-SQL那样原生支持表值参数（Table-Valued Parameters, TVP），不过我们有几种靠谱的替代方案，能实现你示例里的批量插入逻辑。下面就针对你给出的MS-SQL存储过程，逐一拆解对应的MySQL实现方式：

方案1：使用临时表（最接近TVP的体验） #

这种方式最贴近MS-SQL中TVP的使用逻辑——先定义一个和目标表字段匹配的临时表，把批量数据先插入进去，再在存储过程中读取这个临时表完成插入。

首先创建临时表模拟TVP的结构：

CREATE TEMPORARY TABLE LocationTableType (
    Name VARCHAR(50) NOT NULL,
    CostRate DECIMAL(10,2) NOT NULL,
    Availability DECIMAL(8,2) NOT NULL
);

然后创建对应的存储过程：

DELIMITER //
CREATE PROCEDURE usp_InsertProductionLocation()
BEGIN
    -- MySQL中没有直接的SET NOCOUNT ON，用这句模拟语义（不影响实际逻辑）
    SET @dummy = 0;
    INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
    SELECT Name, CostRate, Availability, NOW()
    FROM LocationTableType;
END //
DELIMITER ;

使用的时候，先往临时表里塞数据，再调用存储过程：

INSERT INTO LocationTableType VALUES 
    ('Warehouse A', 10.5, 90.0), 
    ('Office B', 5.2, 100.0);
CALL usp_InsertProductionLocation();

小贴士：临时表是会话级的，每个数据库连接的临时表互相独立，不会产生数据冲突，非常适合单会话的批量操作场景。

方案2：使用JSON参数传递批量数据 #

如果不想依赖临时表，MySQL 5.7及以上版本支持JSON类型，可以把批量数据打包成JSON字符串传入存储过程，再通过内置函数解析插入。这种方式更灵活，不需要提前创建临时表。

创建存储过程：

DELIMITER //
CREATE PROCEDURE usp_InsertProductionLocation(IN tvp_data JSON)
BEGIN
    INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
    SELECT 
        JSON_UNQUOTE(JSON_EXTRACT(item, '$.Name')) AS Name,
        JSON_EXTRACT(item, '$.CostRate') AS CostRate,
        JSON_EXTRACT(item, '$.Availability') AS Availability,
        NOW() AS ModifiedDate
    FROM JSON_TABLE(tvp_data, '$[*]' COLUMNS (
        item JSON PATH '$'
    )) AS jt;
END //
DELIMITER ;

调用的时候直接传入JSON数组：

CALL usp_InsertProductionLocation('[
    {"Name": "Warehouse A", "CostRate": 10.5, "Availability": 90.0},
    {"Name": "Office B", "CostRate": 5.2, "Availability": 100.0}
]');

小贴士：这个方案适合需要跨会话传递批量数据，或者动态生成数据的场景，不过要注意保证JSON格式的正确性，避免解析出错。

方案3：使用CSV字符串参数（适用于旧版本MySQL） #

如果你的MySQL版本不支持JSON（比如5.6及以下），可以用CSV字符串来传递批量数据，再在存储过程中拆分处理。这种方式兼容性好，但拆分逻辑相对繁琐。

首先创建存储过程（包含一个辅助拆分函数）：

DELIMITER //
-- 先创建一个字符串拆分辅助函数
DROP FUNCTION IF EXISTS split_csv;
CREATE FUNCTION split_csv(str VARCHAR(1000), delim VARCHAR(10), pos INT)
RETURNS VARCHAR(100)
BEGIN
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
               LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
               delim, '');
END //

-- 创建存储过程
CREATE PROCEDURE usp_InsertProductionLocation(IN tvp_csv VARCHAR(1000))
BEGIN
    INSERT INTO Production.Location (Name, CostRate, Availability, ModifiedDate)
    SELECT 
        split_csv(line, ',', 1) AS Name,
        split_csv(line, ',', 2) AS CostRate,
        split_csv(line, ',', 3) AS Availability,
        NOW() AS ModifiedDate
    FROM (
        -- 这里用UNION ALL生成数字序列，最多支持4行数据，需要更多可以追加
        SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(tvp_csv, '|', n), '|', -1) AS line
        FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers
        WHERE n <= 1 + (LENGTH(tvp_csv) - LENGTH(REPLACE(tvp_csv, '|', '')))
    ) AS lines;
END //
DELIMITER ;

调用的时候传入用|分隔行、用,分隔字段的CSV字符串：

CALL usp_InsertProductionLocation('Warehouse A,10.5,90.0|Office B,5.2,100.0');

小贴士：这个方案适合数据格式简单的场景，要是数据里包含分隔符（比如Name里有逗号），就得额外处理转义逻辑，相对麻烦。

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