问题描述
我正在编写一个存储过程，它应该将其参数传递给过程主体中查询的 IN (..) 部分，如下所示:
I'm writing a stored procedure which should pass its arguments to IN (..) part of query in the procedure body, like this:
DELIMITER //
CREATE PROCEDURE `get_users_per_app` (id_list TEXT)
BEGIN
SELECT app_id, GROUP_CONCAT(user_id) FROM app_users WHERE app_id IN (id_list) GROUP BY app_id;
END//
DELIMITER ;
这显然不起作用，因为当我传递一个文本值时，id_list 被插入为一个整数，并且只有第一个 ID 被考虑并用于 IN()IN()代码>条件.
This, obviously, doesn't work because when I pass a textual value, id_list is interpolated as an integer and only first ID is considered and used inside of IN() condition.
我意识到可以用包含的查询代替这种特定类型的过程，但我认为我的问题仍然存在 - 如果我需要传递此类数据怎么办?
I realize that this particular type of procedure could be instead replaced by the contained query, but I think that my question still stands - what if I needed to pass this kind of data?
我也意识到这种查询方法可能不被视为最佳实践，但在我的用例中，它实际上比返回 ID-ID 对的平面列表要好..
推荐答案
你应该能够使用 MySQL 的 FIND_IN_SET() 使用 id 列表:
You should be able to use MySQL's FIND_IN_SET() to use the list of ids:
CREATE PROCEDURE `get_users_per_app` (id_list TEXT)
BEGIN
SELECT
app_id, GROUP_CONCAT(user_id)
FROM
app_users
WHERE
FIND_IN_SET(app_id, id_list) > 0
GROUP BY app_id;
...
这篇关于如何将 ID 列表传递给 MySQL 存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！