如何从 MySQL 检索 JSON 数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15701579/
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 retrieve JSON data from MySQL?
提问by pankaj ghadge
I have following tables and their relationship. I am storing JSON data in client_services table. Is their any way to retrieve JSON values using MySQL query like this:
我有以下表格及其关系。我将 JSON 数据存储在 client_services 表中。他们有什么方法可以使用 MySQL 查询来检索 JSON 值，如下所示：
SELECT getJson("quota") as quota,
client_id
FROM client_services
WHERE service_id = 1;
Or can I normalize client_services table further?
或者我可以进一步规范化 client_services 表吗？
Table Services:
表Services：
+----+-----------------------+--------------------------------------------------------+
| id | name | description |
+----+-----------------------+--------------------------------------------------------+
| 1 | MailBox | |
| 2 | SMS | |
| 3 | FTP | |
+----+-----------------------+--------------------------------------------------------+
Table service_features:
表service_features：
+----+------------+----------------------------------+------------------------+
| id | service_id | name | description |
+----+------------+----------------------------------+------------------------+
| 10 | 1 | Forwarding | Forward Mail |
| 11 | 1 | Archive | Archive Mail |
| 12 | 1 | WebMail | NULL |
| 13 | 1 | IMAP | NULL |
| 14 | 2 | Web SMS | NULL |
+----+------------+----------------------------------+------------------------+
Table client_services:
表client_services：
+-----+-----------+------------+-------------------------------------------------------------------------------------------+
| id | client_id | service_id | service_values |
+-----+-----------+------------+-------------------------------------------------------------------------------------------+
| 100 | 1000 | 1 |{ "quota": 100000,"free_quota":20000,"total_accounts":200,"data_transfer":1000000} |
| 101 | 1000 | 2 |{ "quota": 200 } |
| 102 | 1000 | 3 |{ "data_transfer":1000000} |
| 103 | 1001 | 1 |{ "quota": 1000000,"free_quota":2000,"total_accounts":200,"data_transfer":1000000} |
| 104 | 1001 | 2 |{ "quota": 500 } |
| 105 | 1002 | 2 |{ "quota": 600 } |
+-----+-----------+------------+-------------------------------------------------------------------------------------------+
Table client_feature_mappers:
表client_feature_mappers：
+-----+-------------------+--------------------+-----------+
| id | client_service_id | service_feature_id | client_id |
+-----+-------------------+--------------------+-----------+
|10000| 100| 10 | 1000|
|10001| 100| 11 | 1000|
|10002| 100| 12 | 1000|
|10003| 100| 13 | 1000|
|10004| 101| 14 | 1000|
|10005| 103| 10 | 1001|
|10006| 101| 11 | 1001|
|10007| 101| 12 | 1001|
|10008| 101| 13 | 1001|
|10009| 105| 14 | 1002|
+-----+-------------------+--------------------+-----------+
回答by tika
Since a lot of people have asked this question to me personally, I thought I would give this answer a second revision. Here is a gist that has the complete SQL with SELECT, Migration and View Creationand a live sql fiddle(availability not guaranteed for fiddle).
由于很多人都向我个人提出过这个问题，我想我会对这个答案进行第二次修改。这是一个要点，其中包含带有 SELECT、Migration 和 View Creation 的完整 SQL以及一个 live sql fiddle （不保证 fiddle 的可用性）。
Let's say you have table (named: JSON_TABLE) like this:
假设您有这样的表（名为：JSON_TABLE）：
ID CITY POPULATION_JSON_DATA
-----------------------------------------------------------------------
1 LONDON {"male" : 2000, "female" : 3000, "other" : 600}
2 NEW YORK {"male" : 4000, "female" : 5000, "other" : 500}
To Select each json fields, you may do:
要选择每个 json 字段，您可以执行以下操作：
SELECT
ID, CITY,
json_extract(POPULATION_JSON_DATA, '$.male') AS POPL_MALE,
json_extract(POPULATION_JSON_DATA, '$.female') AS POPL_FEMALE,
json_extract(POPULATION_JSON_DATA, '$.other') AS POPL_OTHER
FROM JSON_TABLE;
which results:
结果：
ID CITY POPL_MALE POPL_FEMALE POPL_OTHER
-----------------------------------------------------------------
1 LONDON 2000 3000 600
2 NEW YORK 4000 5000 500
This might be an expensive operation to run based on your data size and json complexity. I suggest using it for
根据您的数据大小和 json 复杂性，这可能是一项昂贵的操作。我建议将它用于
- Migration of table to split database (See Appendix 2-B in gist)
- At least create a view (See Appendix 2-C in gist)
- 将表迁移到拆分数据库（请参阅附录 2-B 中的要点）
- 至少创建一个视图 （请参阅附录 2-C 中的要点）
Watch out for: You may have json starting with double quotes(stringified):
注意：您可能有以双引号（字符串化）开头的json ：
"{"male" : 2000, "female" : 3000, "other" : 600}"
Tested with Mysql 5.7 on Ubuntu and Mac OSX Sierra.
在 Ubuntu 和 Mac OSX Sierra 上使用 Mysql 5.7 进行测试。
回答by pdolinaj
You can use MySQL function SUBSTRING_INDEX to break down the JSON string:
您可以使用 MySQL 函数 SUBSTRING_INDEX 来分解 JSON 字符串：
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(
SUBSTRING_INDEX( service_values, 'quota', -1),
'": ', -1),
' ', 1) AS quota,
client_id
FROM client_services
WHERE service_id=1;
回答by koriander
Firstly you should be aware that your model above is not in First Normal Form, meaning you should have only a single value in each field. However, this definition is dependent on your application query processing needs.
首先你应该知道你上面的模型不是第一范式，这意味着你应该在每个字段中只有一个值。但是，此定义取决于您的应用程序查询处理需求。
So, if all you want is to put a bunch of JSON data in a field and return it to the application as is, then it's fine. You could return the whole JSON data and let the application pick the JSON attributes it wants.
因此，如果您只想将一堆 JSON 数据放在一个字段中，然后按原样将其返回给应用程序，那就没问题了。您可以返回整个 JSON 数据并让应用程序选择它想要的 JSON 属性。
But if you have queries, as in your case, having criteria or field expressions looking into the details of the JSON data, then it's definitely a no-go. It will be a nightmare in query complexity and slow processing.
但是，如果您有查询，例如在您的情况下，使用条件或字段表达式查看 JSON 数据的详细信息，那么这绝对是行不通的。这将是查询复杂性和处理缓慢的噩梦。
You can of course further normalise your tables to fully replace the JSON data structure. However, if your application needs a flexible schema, perhaps the main reason to use NOSQL DBs, but you are stuck with MySQL, there are two solutions:
您当然可以进一步规范您的表以完全替换 JSON 数据结构。但是，如果您的应用程序需要一个灵活的架构，这可能是使用 NOSQL DB 的主要原因，但您仍然坚持使用 MySQL，则有两种解决方案：
a) use MySQL 5.6 (or MariaDB v. ??) for NoSQL support (I haven't looked into details) http://www.computerworld.com/s/article/9236511/MySQL_5.6_tackles_NoSQL_competitorsMariaDB's dynamic columns: https://kb.askmonty.org/en/dynamic-columns/
a) 使用 MySQL 5.6（或 MariaDB v. ??）进行 NoSQL 支持（我还没有研究详细信息）http://www.computerworld.com/s/article/9236511/MySQL_5.6_tackles_NoSQL_competitorsMariaDB 的动态列：https： //kb.askmonty.org/en/dynamic-columns/
b) use mysql without an explicit schema, see here for a very well done solution, which has no scalability problems: http://backchannel.org/blog/friendfeed-schemaless-mysql
b) 在没有显式架构的情况下使用 mysql，请参阅此处了解一个做得很好的解决方案，它没有可扩展性问题：http: //backchannel.org/blog/friendfeed-schemaless-mysql
回答by Ivan DCosta
SET @a = '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]' ;
SELECT *
FROM
JSON_TABLE(
@a,
"$[*]" COLUMNS(
xval VARCHAR(100) PATH "$.x",
yval VARCHAR(100) PATH "$.y"
)) AS jt1;
