如何在MySQL中搜索JSON数据？

I am inserting my data in a database with json_encoded. Now I want to search in "feature", but I can't.

我使用json_encoded将数据插入数据库。现在我想搜索“功能”，但我不能。

MySQL query:

MySQL查询：

SELECT  `id` ,  `attribs_json` 
FROM  `products` 
WHERE  `attribs_json` REGEXP  '"1":{"value":[^"3"$]'

This query shows me all rows with key "1" and value is any thing not value is "3"

这个查询显示了所有带有键“1”的行，而值是任何不值为“3”的东西

My data is:

我的数据是：

{"feature":{"1":{"value":"["2","3"]"},
            "2":{"value":["1"]},
            "5":{"value":""},
            "3":{"value":["1"]},
            "9":{"value":""},
            "4":{"value":"\u0633\u0627\u062a\u0646"},
            "6":{"value":""},
            "7":{"value":""},
            "8":{"value":""}
           },
"show_counter":"0",
"show_counter_discount":""
}}

4 个解决方案

#1

If you have MySQL version >= 5.7, then you can try this:

如果你有MySQL版本> = 5.7，那么你可以试试这个：

SELECT JSON_EXTRACT(name, "$.id") AS name
FROM table
WHERE JSON_EXTRACT(name, "$.id") > 3

Output:

输出：

+-------------------------------+
| name                          | 
+-------------------------------+
| {"id": "4", "name": "Betty"}  | 
+-------------------------------+

Please check MySQL reference manual for more details:
https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

有关更多详细信息，请查看MySQL参考手册：https：//dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

#2

Storing JSON in database violates the first normal form.

在数据库中存储JSON违反了第一个普通表单。

The best thing you can do is to normalize and store features in another table. Then you will be able to use a much better looking and performing query with joins. Your JSON even resembles the table.

您可以做的最好的事情是在另一个表中规范化和存储功能。然后，您将能够使用连接更好看和执行查询。您的JSON甚至类似于表格。
Mysql 5.7 has builtin JSON functionality:
http://mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/

Mysql 5.7内置了JSON功能：http：//mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/
Correct pattern is:

正确的模式是：
```
WHERE  `attribs_json` REGEXP '"1":{"value":[^}]*"3"[^}]*}'
```
[^}] will match any character except }

[^}]将匹配除}之外的任何字符

#3

If your are using MySQL Latest version following may help to reach your requirement.

如果您使用MySQL最新版本可能有助于达到您的要求。

select * from products where attribs_json->"$.feature.value[*]" in (1,3)

#4

I use this query

我用这个查询

SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"])key_word([^"])"';
or
SELECT id FROM table_name WHERE field_name RLIKE '"key_name":"[[:<:]]key_word[[:>:]]"';

The first query I use it to search partial value. The second query I use it to search exact word.

第一个查询我用它来搜索部分值。第二个查询我用它来搜索确切的单词。

#1