在MySQL列中搜索包含特定值的JSON字符串

Question

我目前有一个MySQL表格，其中包含一个列来存储类别ID。这些ID以JSON字符串的形式存储。我正在寻找查询这些JSON字符串中特定ID的最有效方法。

例如：

表格： posts

字段： cats

这里是cats列中JSON字符串的一些示例值：

---

[111, 123, 456]

[123, 345, 999]

[555, 777, 888]

假设我想查询所有包含ID为“123”的行。我知道可以使用一系列LIKE比较来实现这一点，但我相信有更有效的方法来查询JSON字符串。如果有其他想法，将不胜感激。

谢谢！

- VerySeriousSoftwareEndeavours

---

没有一种有效的方法来进行这种类型的搜索。这就是为什么以这种方式存储数据是一个极其糟糕的想法。 - Barmar

嗨@McWayWeb，你解决了你的问题吗？有任何答案帮助了你吗？如果是的话，请尝试标记为已解决，以帮助其他人并保持S.O的清洁。谢谢！ - JP. Aulet

Answer 1

需求：必须包含id为2、4、6和7的猫咪。

查询条件：JSON_CONTAINS(posts.cats->"$.ids", '[2]');

- nikolay.hristov

Answer 2

有关存储JSON值的讨论非常广泛 (在MySQL中以JSON格式存储数据)，但您可以使用本地MySQL函数来实现：

Mysql官方文档：https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

在

搜索JSON值的函数

中，有：

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');

或者使用类似于REGEX的东西：

SELECT * FROM posts where cats  REGEXP '"id":"[[:<:]]123[[:>:]]"';

或者使用：

extract_json_value( json_text TEXT CHARSET utf8 xpath TEXT CHARSET utf8 ) 返回 TEXT CHARSET utf8

这里有一篇处理mysql上JSON的好文章：http://rpbouman.blogspot.com.es/2015/11/mysql-few-observations-on-json-type.html

希望对您有所帮助！

- JP. Aulet

Answer 3

WHERE JSON_CONTAINS(cats, ID_TO_SEARCH) = 1

这应该可以正常工作。MySQL 5.7支持JSON类型，具体可参考此文档。

- csn

Answer 4

如果您确实需要在JSON字符串中查询特定值，最好尝试将单个值存储在数据库中。在上面的示例中，您可以创建一个连接到帖子表的字段表。然后，一个简单的连接语句就可以获取所有正确的值。

- Gremash