问题描述
我有以下表格及其关系.我将 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?
表服务:
+----+-----------------------+--------------------------------------------------------+
| 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|
+-----+-------------------+--------------------+-----------+
推荐答案
由于很多人私下问过我这个问题，所以我想我会再次修改这个答案.这是一个要点，其中包含带有选择、迁移和视图创建的完整 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).
假设您有这样的表(名为:TBL_JSON):
Let's say you have table (named: TBL_JSON) 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 TBL_JSON;
结果:
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
- 将表迁移到拆分数据库(要点请参见附录 2-B)
- 至少创建一个视图(请参阅附录 2-C 的要点)
注意:您可能有 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 数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！