MySQL 8.0使用MEMBER OF搭配子查询出现1064语法错误如何解决

• 语法错误核心触发点：MEMBER OF运算符的右侧仅支持直接传入JSON数组值/返回单值JSON数组的标量表达式，不支持直接跟子查询。你将SELECT子查询直接放在MEMBER OF右侧的写法不符合MySQL语法规范，因此直接抛出语法错误。
• 隐藏匹配问题：你存储的JSON数组格式为字符串类型ID["1","2"]，如果hotel表的id字段为数值类型，即便语法修复后也会出现类型不匹配导致匹配失败的问题。

以下方案均适配MySQL 8.0.26版本：

方案1：使用JSON_TABLE拆分数组后关联（推荐，兼容性最强） #

将auth_events表的JSON数组拆分为多行普通数据，再通过IN子句匹配：

SELECT h.id, h.hotel_name 
FROM hotel h
WHERE h.id IN (
    SELECT j.hotel_id
    FROM auth_events a,
         JSON_TABLE(
             a.details,
             '$[*]' COLUMNS (hotel_id INT PATH '$')
         ) j
    WHERE a.id_user=1 AND a.id_auth_catalog=10
);

方案2：修复MEMBER OF语法适配子查询 #

如果确定内层子查询仅返回单条JSON数组结果，可通过额外括号将子查询转为标量表达式，同时处理类型匹配问题：

SELECT id, hotel_name FROM hotel 
WHERE CAST(id AS CHAR) MEMBER OF (
    -- 额外一层括号将子查询转为标量值
    (SELECT details FROM auth_events WHERE id_user=1 AND id_auth_catalog=10)
);

方案3：使用JSON_CONTAINS函数实现匹配 #

通过JSON包含判断实现需求，写法更简洁：

SELECT h.id, h.hotel_name 
FROM hotel h
JOIN auth_events a 
  ON a.id_user=1 AND a.id_auth_catalog=10
WHERE JSON_CONTAINS(a.details, CAST(h.id AS JSON), '$');

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