mysql教程，在 MySQL 中使用 JSON 类型，mysql解析json，mysql的json格式支持存储处理json数据。
介绍
从 MySQL 5.7 版本开始，您可以使用“JSON 类型”来进行存储操作json数据。
在本文中，我想总结一下如何使用 JSON 类型。
定义一张表STORE_TBL，里面的STORE列定义为json类型。
CREATE TABLE STORE_TBL (
ID INT,
STORE JSON,
PRIMARY KEY (ID)
);
插入测试数据
INSERT INTO STORE_TBL VALUES (1, ‘{“store_cd”:”001″, “store_nm”:”東店”, “staff”:{“fulltime”:[“sato”, “hayashi”], “parttime”:[“shimizu”, “ogawa”]}}’);
INSERT INTO STORE_TBL VALUES (2, ‘{“store_cd”:”002″, “store_nm”:”北店”, “staff”:{“fulltime”:[“suzuki”, “kimura”], “parttime”:[“nakamura”]}}’);
INSERT INTO STORE_TBL VALUES (3, ‘{“store_cd”:”003″, “store_nm”:”西店”, “staff”:{“fulltime”:[“tanaka”, “sasaki”]}}’);
查询mysql JSON 数据
使用 JSON_EXTRACT 函数获取json里特定键的值。
SELECT JSON_EXTRACT(column, ‘$.key’) FROM TABLE;
mysql> SELECT JSON_EXTRACT(STORE, ‘$.store_nm’) FROM STORE_TBL;
+———————————–+
| JSON_EXTRACT(STORE, ‘$.store_nm’) |
+———————————–+
| “東店” |
| “北店” |
| “西店” |
+———————————–+
3 rows in set (0.00 sec)
mysql> SELECT JSON_EXTRACT(STORE, ‘$.staff.fulltime’) FROM STORE_TBL;
+—————————————–+
| JSON_EXTRACT(STORE, ‘$.staff.fulltime’) |
+—————————————–+
| [“sato”, “hayashi”] |
| [“suzuki”, “kimura”] |
| [“tanaka”, “sasaki”] |
+—————————————–+
3 rows in set (0.00 sec)
mysql> SELECT JSON_EXTRACT(STORE, ‘$.staff.fulltime[0]’) FROM STORE_TBL;
+——————————————–+
| JSON_EXTRACT(STORE, ‘$.staff.fulltime[0]’) |
+——————————————–+
| “sato” |
| “suzuki” |
| “tanaka” |
+——————————————–+
3 rows in set (0.00 sec)
也可以在不使用 JSON_EXTRACT 函数的情况下以 ->”$.key” 格式获取它。
SELECT column->”$.key” FROM TABLE;
mysql> SELECT STORE->”$.store_nm” FROM STORE_TBL;
+———————+
| STORE->”$.store_nm” |
+———————+
| “東店” |
| “北店” |
| “西店” |
+———————+
3 rows in set (0.00 sec)
mysql> SELECT STORE->”$.staff.fulltime” FROM STORE_TBL;
+—————————+
| STORE->”$.staff.fulltime” |
+—————————+
| [“sato”, “hayashi”] |
| [“suzuki”, “kimura”] |
| [“tanaka”, “sasaki”] |
+—————————+
3 rows in set (0.00 sec)
mysql> SELECT STORE->”$.staff.fulltime[0]” FROM STORE_TBL;
+——————————+
| STORE->”$.staff.fulltime[0]” |
+——————————+
| “sato” |
| “suzuki” |
| “tanaka” |
+——————————+
3 rows in set (0.00 sec)
如果查询的结果不想要双引号，请使用 JSON_UNQUOTE 函数。
mysql> SELECT JSON_UNQUOTE(STORE->”$.store_nm”) FROM STORE_TBL;
+———————————–+
| JSON_UNQUOTE(STORE->”$.store_nm”) |
+———————————–+
| 東店 |
| 北店 |
| 西店 |
+———————————–+
3 rows in set (0.00 sec)
如果要获取格式化的 JSON，请使用 JSON_PRETTY 函数。
mysql> SELECT JSON_PRETTY(STORE) FROM STORE_TBL WHERE JSON_EXTRACT(STORE, ‘$.store_cd’) = ‘001’;
+———————————————-+
| JSON_PRETTY(STORE) |
+———————————————-+
| {
“staff”: {
“fulltime”: [
“sato”,
“hayashi”
],
“parttime”: [
“shimizu”,
“ogawa”
]
},
“store_cd”: “001”,
“store_nm”: “東店”
} |
+———————————————-+
1 row in set (0.00 sec)
如果要检索特定键和值的数据，请使用 JSON_CONTAINS 函数。
JSON_CONTAINS(column, ‘値’, ‘$.key’) ※有指定的Key和值时返回1，没有时返回0
— 查询STORE这个json字段里staff.fulltime键对应的value包含”kimura”的数据的store_nm
mysql> SELECT JSON_EXTRACT(STORE, ‘$.store_nm’) FROM STORE_TBL WHERE JSON_CONTAINS(STORE, ‘”kimura”‘, ‘$.staff.fulltime’);
+———————————–+
| JSON_EXTRACT(STORE, ‘$.store_nm’) |
+———————————–+
| “北店” |
+———————————–+
1 row in set (0.00 sec)
如果要检索包含特定路径的数据，请使用 JSON_CONTAINS_PATH 函数。
JSON_CONTAINS_PATH(column, one_or_all, ‘$.key’) ※有指定路径时返回1，没有路径时返回0
— 查询STORE这个json字段里staff.parttime键对应的value存在值的store_nm
mysql> SELECT JSON_EXTRACT(STORE, ‘$.store_nm’) FROM STORE_TBL WHERE JSON_CONTAINS_PATH(STORE, ‘one’, ‘$.staff.parttime’);
+———————————–+
| JSON_EXTRACT(STORE, ‘$.store_nm’) |
+———————————–+
| “東店” |
| “北店” |
+———————————–+
2 rows in set (0.00 sec)
更新 JSON 数据，使用 JSON_SET 函数更新特定键的值。
— 更新前的数据
mysql> select JSON_EXTRACT(STORE, ‘$.staff’) FROM STORE_TBL WHERE JSON_EXTRACT(STORE, ‘$.store_cd’) = ‘002’;
+————————————————————–+
| JSON_EXTRACT(STORE, ‘$.staff’) |
+————————————————————–+
| {“fulltime”: [“suzuki”, “kimura”], “parttime”: [“nakamura”]} |
+————————————————————–+
1 row in set (0.01 sec)
— 更新
mysql> UPDATE STORE_TBL SET STORE = JSON_SET(STORE, ‘$.staff.parttime[0]’, ‘saito’) WHERE JSON_EXTRACT(STORE, ‘$.store_cd’) = “002”;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
— 更新后的数据
mysql> select JSON_EXTRACT(STORE, ‘$.staff’) FROM STORE_TBL WHERE JSON_EXTRACT(STORE, ‘$.store_cd’) = ‘002’;
+———————————————————–+
| JSON_EXTRACT(STORE, ‘$.staff’) |
+———————————————————–+
| {“fulltime”: [“suzuki”, “kimura”], “parttime”: [“saito”]} |
+———————————————————–+
1 row in set (0.00 sec)
使用 JSON_ARRAY_APPEND 函数将值附加到数组的末尾。
— 追加前的数据
mysql> select JSON_EXTRACT(STORE, ‘$.staff’) FROM STORE_TBL WHERE JSON_EXTRACT(STORE, ‘$.store_cd’) = ‘002’;
+———————————————————–+
| JSON_EXTRACT(STORE, ‘$.staff’) |
+———————————————————–+
| {“fulltime”: [“suzuki”, “kimura”], “parttime”: [“saito”]} |
+———————————————————–+
1 row in set (0.00 sec)
— 追加
mysql> UPDATE STORE_TBL SET STORE = JSON_ARRAY_APPEND(STORE, ‘$.staff.parttime’, ‘nakano’) WHERE JSON_EXTRACT(STORE, ‘$.store_cd’) = “002”;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
— 追加后的数据
mysql> select JSON_EXTRACT(STORE, ‘$.staff’) FROM STORE_TBL WHERE JSON_EXTRACT(STORE, ‘$.store_cd’) = ‘002’;
+———————————————————————+
| JSON_EXTRACT(STORE, ‘$.staff’) |
+———————————————————————+
| {“fulltime”: [“suzuki”, “kimura”], “parttime”: [“saito”, “nakano”]} |
+———————————————————————+
1 row in set (0.00 sec)
mysql教程，mysql解析json，mysql的json格式支持存储处理json数据的总结
MySQL 有很多使用 JSON 的函数，通过使用这些函数，可以非常灵活地操作数据。除了这里介绍的功能外，还有很多功能，感兴趣的可以参考MySQL官方文档。
更多文章，请持续关注《MySql教程网》https://mysql360.com
