如何在 MySQL 中搜索 JSON 数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30411210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to search JSON data in MySQL?
提问by reza
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":""
}}
回答by Zeeweesoft
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
回答by Vishnu Prasanth G
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)
回答by Naktibalda
Storing JSON in database violates the first normal form.
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.
Mysql 5.7 has builtin JSON functionality:
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}
将 JSON 存储在数据库中违反了第一范式。
您能做的最好的事情是将特征标准化并存储在另一个表中。然后，您将能够使用具有更好外观和执行的连接查询。您的 JSON 甚至类似于表格。
Mysql 5.7 具有内置的 JSON 功能：http:
//mysqlserverteam.com/mysql-5-7-lab-release-json-functions-part-2-querying-json-data/正确的模式是：
WHERE `attribs_json` REGEXP '"1":{"value":[^}]*"3"[^}]*}'[^}]将匹配任何字符，除了}
回答by Valentino
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.
我使用它来搜索部分值的第一个查询。第二个查询我用它来搜索确切的词。
回答by romantic zang
I think...
我认为...
Search partial value:
搜索部分值：
SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"])*key_word([^"])*"';
Search exact word:
搜索确切词：
SELECT id FROM table_name WHERE field_name RLIKE '"key_name":"[[:<:]]key_word[[:>:]]"';
回答by 0x00
for MySQL all (and 5.7)
对于 MySQL 全部（和 5.7）
SELECT LOWER(TRIM(BOTH 0x22 FROM TRIM(BOTH 0x20 FROM SUBSTRING(SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"'),LOCATE(0x2C,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"')+1,LENGTH(json_filed)))),LOCATE(0x22,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"'),LOCATE(0x2C,SUBSTRING(json_filed,LOCATE('\"ArrayItem\"',json_filed)+LENGTH('\"ArrayItem\"')+1,LENGTH(json_filed))))),LENGTH(json_filed))))) AS result FROM `table`;
