如何从MySQL检索JSON数据? [英] How to retrieve JSON data from MySQL?

问题描述

我有下表和它们之间的关系.我将JSON数据存储在client_services表中.它们是使用MySQL查询来检索JSON值的任何方法，如下所示:

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:

SELECT getJson("quota") as quota,
       client_id
FROM client_services
WHERE service_id = 1;     

还是可以进一步规范化client_services表?

Or can I normalize client_services table further?

表Services:

+----+-----------------------+--------------------------------------------------------+
| id | name                  | description                                            |
+----+-----------------------+--------------------------------------------------------+
|  1 | MailBox               |                                                        |
|  2 | SMS                   |                                                        |
|  3 | FTP                   |                                                        |
+----+-----------------------+--------------------------------------------------------+

表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                   |
+----+------------+----------------------------------+------------------------+

表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 }                                                                           |
+-----+-----------+------------+-------------------------------------------------------------------------------------------+

表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|
+-----+-------------------+--------------------+-----------+

推荐答案

由于许多人亲自向我提出了这个问题，所以我认为我将对此答案进行第二次修订.这是要点，该要点具有带有SELECT，迁移和视图创建的完整SQL 和实时SQL小提琴 (小提琴不保证可用性).

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 Creation and a live sql fiddle (availability not guaranteed for fiddle).

假设您有这样的表(名为:JSON_TABLE):

Let's say you have table (named: JSON_TABLE) like this:

 ID   CITY        POPULATION_JSON_DATA
-----------------------------------------------------------------------
 1    LONDON      {"male" : 2000, "female" : 3000, "other" : 600}
 2    NEW YORK    {"male" : 4000, "female" : 5000, "other" : 500}

要选择每个json字段，您可以执行以下操作:

To Select each json fields, you may do:

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;

结果:

ID  CITY      POPL_MALE  POPL_FEMALE   POPL_OTHER 
-----------------------------------------------------------------
1   LONDON    2000       3000          600
2   NEW YORK  4000       5000          500

根据您的数据大小和json复杂度，这可能是一项昂贵的操作.我建议将其用于

This might be an expensive operation to run based on your data size and json complexity. I suggest using it for

1. 将表迁移到拆分数据库(请参见附录2-B)
2. 至少创建一个视图(要点请参见附录2-C)

1. Migration of table to split database (See Appendix 2-B in gist)
2. At least create a view (See Appendix 2-C in gist)

当心:您可能会有json 以双引号开头 (字符串化):

Watch out for: You may have json starting with double quotes (stringified):

"{"male" : 2000, "female" : 3000, "other" : 600}"

在Ubuntu和Mac OSX Sierra上使用Mysql 5.7进行了测试.

Tested with Mysql 5.7 on Ubuntu and Mac OSX Sierra.

这篇关于如何从MySQL检索JSON数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！