我有以下表格及其关系。我将JSON数据存储在client_services表中。它们是使用MySQL查询来检索JSON值的任何方式，如下所示：
SELECT getJson("quota") as quota, client_id FROM client_services WHERE service_id = 1;
还是可以进一步规范化client_services表？
表Services：
Services
+----+-----------------------+--------------------------------------------------------+ | id | name | description | +----+-----------------------+--------------------------------------------------------+ | 1 | MailBox | | | 2 | SMS | | | 3 | FTP | | +----+-----------------------+--------------------------------------------------------+
表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 | +----+------------+----------------------------------+------------------------+
表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 } | +-----+-----------+------------+-------------------------------------------------------------------------------------------+
表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| +-----+-------------------+--------------------+-----------+
由于很多人都亲自问过我这个问题，所以我想我会再作一次修改。这是一个具有SELECT，Migration和View Creation的完整SQL的要点，以及一个实时sql提琴 （ 提琴 不保证可用性） 。
假设您有这样的表（名为：JSON_TABLE）：
ID CITY POPULATION_JSON_DATA ----------------------------------------------------------------------- 1 LONDON {"male" : 2000, "female" : 3000, "other" : 600} 2 NEW YORK {"male" : 4000, "female" : 5000, "other" : 500}
要选择每个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;
结果：
ID CITY POPL_MALE POPL_FEMALE POPL_OTHER ----------------------------------------------------------------- 1 LONDON 2000 3000 600 2 NEW YORK 4000 5000 500
根据您的数据大小和json复杂度，这可能是一项昂贵的操作。我建议将其用于
请注意：您可能拥有 以双引号 （stringified） 开头的 json ：
"{"male" : 2000, "female" : 3000, "other" : 600}"
在Ubuntu和Mac OSX Sierra上使用Mysql 5.7进行了测试。
