MySQL查询IN子句含重复值时如何返回对应次数的行？

问题 #

我希望通过单条MySQL查询重复获取同一列的结果。执行语句：

SELECT appellation FROM persona WHERE character_id IN (853,12,853) ORDER BY FIELD(character_id,853,12,853)

后，返回结果仅包含两行：

character_id | appellation
---------------------------
853          | John Cena
12           | Chris Brown

但我期望返回三行，即重复显示character_id为853对应的行：

character_id | appellation
---------------------------
853          | John Cena
12           | Chris Brown
853          | John Cena

请问MySQL中是否有实现该需求的方法？

解决方案 #

原查询里的IN子句会自动对传入的ID去重，不管写多少个重复值，只会保留唯一ID对应的结果，所以没法返回重复行。可以用以下几种方法实现需求：

方法1：用UNION ALL拼接查询 #

UNION ALL不会对结果去重，为每个需要的ID单独写SELECT语句再拼接即可：

SELECT character_id, appellation FROM persona WHERE character_id = 853
UNION ALL
SELECT character_id, appellation FROM persona WHERE character_id = 12
UNION ALL
SELECT character_id, appellation FROM persona WHERE character_id = 853
ORDER BY FIELD(character_id, 853, 12, 853)

方法2：派生表生成ID列表后关联查询 #

先构建包含重复ID的派生表，再和原表关联，适合ID数量较多的场景：

SELECT p.character_id, p.appellation
FROM (
    SELECT 853 AS id UNION ALL
    SELECT 12 AS id UNION ALL
    SELECT 853 AS id
) AS ids
JOIN persona p ON p.character_id = ids.id
ORDER BY FIELD(p.character_id, 853, 12, 853)

方法3：MySQL 8.0+ 用JSON_TABLE解析ID数组 #

如果你的MySQL版本是8.0及以上，可以用JSON_TABLE把包含重复ID的JSON数组转换成临时表，再关联查询：

SELECT p.character_id, p.appellation
FROM JSON_TABLE(
    '[853,12,853]',
    '$[*]' COLUMNS(id INT PATH '$')
) AS ids
JOIN persona p ON p.character_id = ids.id
ORDER BY FIELD(p.character_id, 853, 12, 853)

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