I'm writing a stored procedure which should pass its arguments to IN (..)
part of query in the procedure body, like this:
我正在编写一个存储过程,它应该将其参数传递给过程体中查询的IN(..)部分,如下所示:
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 ;
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.
显然,这不起作用,因为当我传递文本值时,id_list被内插为整数,并且只考虑第一个ID并在IN()条件内使用。
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?
我意识到这个特定类型的过程可以替换为包含的查询,但我认为我的问题仍然存在 - 如果我需要传递这种数据怎么办?
I also realize that this approach of query might not be considered the best practice, but in my use case it's actually better than returning a flat list of ID-ID pairs..
我也意识到这种查询方法可能不被认为是最佳实践,但在我的用例中,它实际上比返回一个ID-ID对的平面列表更好。
1 个解决方案
#1
28
You should be able to use MySQL's FIND_IN_SET()
to use the list of ids:
您应该能够使用MySQL的FIND_IN_SET()来使用id列表:
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;
...
#1
28
You should be able to use MySQL's FIND_IN_SET()
to use the list of ids:
您应该能够使用MySQL的FIND_IN_SET()来使用id列表:
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;
...