mysql - 当 "where in"子句是带有替换 JSON 括号/引号的动态字段时，在子查询中选择行不起作用

Question

我正在尝试编写一个查询来选择子查询中以逗​​号分隔的游戏名称，这些名称以 JSON 格式存储在折扣代码列中。下面的查询当前仅返回一个结果，而根据逻辑我应该期望遵循多个游戏名称。

SELECT
    discount_code, discount_type, percentage_off,
    (
        SELECT
            GROUP_CONCAT(name) 
        FROM
            games AS g
        WHERE
            g.id IN (
                REPLACE(
                    REPLACE(
                        REPLACE(dc.game_ids, '[', '' ), ']', ''
                    ), '"', ''
                )
            )
    ) AS game_names
FROM
    discount_codes AS dc
WHERE
    status = 1 AND
    active = 1
ORDER BY
    id
ASC

知道为什么查询只从子查询返回一个结果(一个游戏名称)吗？

编辑:

示例数据:

id  name                description
-   -                   -
1   Fun and exciting!   Game description...
2   Game Name           Game description...

折扣代码

id  discount_code   discount_type       percentage_off  game_ids
-   -               -                   -               -
1   10OFF           single-use          10              ["1"]
2   FREE            ongoing             100             ["1,2"]

Answer 1

查询仅返回一个结果的原因是 REPLACE 调用的返回值是一个字符串，而不是一组值。当 MySQL 尝试计算该表达式时，它会将字符串转换为整数(以匹配 g.id)，并返回字符串中的第一个值(例如 1,3 ,5 转换为 1)，因此您只能返回一个值。实现所需功能的最佳方法是使用内置的 JSON 函数(假设您使用的是 MySQL 5.7 或更高版本)；但是，考虑到数据的性质，您仍然需要从值中删除双引号:

SELECT
    discount_code, discount_type, percentage_off,
    (
        SELECT
            GROUP_CONCAT(name) 
        FROM
            game AS g
        WHERE
            JSON_SEARCH(REPLACE(dc.game_ids, '"', ''), 'one', g.id) IS NOT NULL

    ) AS game_names
FROM
    discount_codes AS dc
WHERE
    status = 1 AND
    active = 1
ORDER BY
    id
ASC

如果您使用的是 5.7 之前的 MySQL 版本，则可以使用 FIND_IN_SET 代替。请注意，要使 FIND_IN_SET 正常工作，字符串中也不能有空格，这一点很重要，因此您需要添加另一层 REPLACE 嵌套:

SELECT
    discount_code, discount_type, percentage_off,
    (
        SELECT
            GROUP_CONCAT(name) 
        FROM
            game AS g
        WHERE
            FIND_IN_SET(g.id,
                REPLACE(
                    REPLACE(
                        REPLACE(dc.game_ids, '[', '' ), ']', ''
                    ), '"', ''
                )
            )
    ) AS game_names
FROM
    discount_codes AS dc
WHERE
    status = 1 AND
    active = 1
ORDER BY
    id
ASC

Demo on dbfiddle