mysql教程：mysql解析json，mysql的json格式支持存储处理json数据

By adminmysql360On 2022年11月19日2022年11月18日

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)

也可以在不使用 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)

如果查询的结果不想要双引号，请使用 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

浏览量: 102