MySQL 5.7用JSON_CONTAINS关联JSON_KEYS结果查询无返回如何解决

问题原因 #

你遇到的问题是JSON类型不匹配导致的：

• JSON_KEYS(t.info) 返回的数组元素是字符串类型，你的示例中返回结果为 ["33", "64"]，id为带引号的字符串格式
• 直接把数值类型的tm.id转成JSON后，得到的是数值类型的JSON值，例如33转换后为不带引号的33
  MySQL的JSON_CONTAINS要求查找的元素类型和数组内元素类型完全一致才会返回匹配结果，因此两类值无法匹配，导致查询无返回。

修复后的SQL写法 #

以下两种写法都兼容MySQL 5.7.12版本，可正常返回预期结果：

写法1：调整JSON_CONTAINS的参数类型

SELECT tm.name
FROM taxi t
JOIN taxi_model tm ON JSON_CONTAINS(JSON_KEYS(t.info), JSON_QUOTE(CAST(tm.id AS CHAR)))
WHERE t.id = 1;

先把数值类型的tm.id转成字符串，再用JSON_QUOTE包裹为字符串类型的JSON值，和JSON_KEYS返回的元素类型完全匹配。

写法2：用JSON_SEARCH判断存在性

SELECT tm.name
FROM taxi t
JOIN taxi_model tm ON JSON_SEARCH(JSON_KEYS(t.info), 'one', CAST(tm.id AS CHAR)) IS NOT NULL
WHERE t.id = 1;

JSON_SEARCH会直接查找数组中匹配对应字符串的元素，返回元素的路径，不为空即表示存在匹配项。

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