用 MySQL 实现 JSON 格式的数据存储

在绝大多数业务场景中，分布式数据库并非必需。事实上，约80%的应用程序都可以在单机环境下高效运行。

对于绝大多数企业而言，数据库的数据量通常不会达到 TB 级别。

在现代应用开发中，json 已成为数据交换和存储的常见格式。尽管 MongoDB 因其天然支持 json 而备受推崇，但 MySQL 也提供了强大的 json 数据类型支持，能够高效地处理 json 数据。

在本指南中，我们将探讨如何使用MySQL 来存储和管理json数据。通过这种方式，您可以利用单一 MySQL 数据库来满足从小规模到大规模的业务需求，从而降低学习成本。

json 数据存储演示

1.创建表结构

CREATE TABLE t3 (
  id bigint unsigned NOT NULL AUTO_INCREMENT ,
  jdoc json DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入 json 格式数据

INSERT INTO t3(jdoc) VALUES('{"name": "张三", "age": 21}');
INSERT INTO t3(jdoc) VALUES(JSON_OBJECT('name', '李四', 'age', 22));
INSERT INTO t3(jdoc) VALUES(JSON_OBJECT('name', '中年大叔', 'age', 45, 'hobby', 
                                        JSON_ARRAY('美食', '摩旅', '游泳', '电影', '实现50岁退休')));

mysql> select * from t3;
+----+----------------------------------------------------------+
| id | jdoc                                                     |
+----+----------------------------------------------------------+
|  1 | {"age": 21, "name": "张三"}                              |
|  2 | {"age": 22, "name": "李四"}                              |
|  3 | {"age": 45, "name": "中年大叔", "address": "北京"}       |
+----+----------------------------------------------------------+
3 rows in set (0.00 sec)

3.查询 json 格式数据

(1) 获取键(Key)name和age的值(Value)

mysql> select JSON_EXTRACT(jdoc,'$.age') age,JSON_EXTRACT(jdoc,'$.name
') name from t3;+------+----------------+
| age  | name           |
+------+----------------+
| 21   | "张三"         |
| 22   | "李四"         |
| 45   | "中年大叔"     |
+------+----------------+
3 rows in set (0.00 sec)

(2).获取全部键(Key)

mysql> select JSON_KEYS(jdoc) from t3;
+----------------------------+
| JSON_KEYS(jdoc)            |
+----------------------------+
| ["age", "name"]            |
| ["age", "name"]            |
| ["age", "name", "address"] |
+----------------------------+
3 rows in set (0.01 sec)

(3).增加一个键-值对

mysql> update t3 set jdoc=JSON_INSERT(jdoc,'$.address','北京') where JSON_EXTRACT(jdoc,'$.name'
)='中年大叔';Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t3;
+----+-----------------------------------------------------------------------------------------
-----------------------------------------+| id | jdoc                                                                                    
                                         |+----+-----------------------------------------------------------------------------------------
-----------------------------------------+|  1 | {"age": 21, "name": "张三"}                                                             
                                         ||  2 | {"age": 22, "name": "李四"}                                                             
                                         ||  3 | {"age": 45, "name": "中年大叔", "hobby": ["美食", "摩旅", "游泳", "电影", "实现50岁退休"
], "address": "北京"}                    |+----+-----------------------------------------------------------------------------------------
-----------------------------------------+3 rows in set (0.01 sec)

(4).更改一个键-值对

mysql> UPDATE t3
    -> SET jdoc = JSON_SET(jdoc, '$.hobby[4]', '实现55岁退休')
    -> WHERE JSON_EXTRACT(jdoc,'$.name')='中年大叔';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t3;
+----+-----------------------------------------------------------------------------------------
-----------------------------------------+| id | jdoc                                                                                    
                                         |+----+-----------------------------------------------------------------------------------------
-----------------------------------------+|  1 | {"age": 21, "name": "张三"}                                                             
                                         ||  2 | {"age": 22, "name": "李四"}                                                             
                                         ||  3 | {"age": 45, "name": "中年大叔", "hobby": ["美食", "摩旅", "游泳", "电影", "实现55岁退休"
], "address": "北京"}                    |+----+-----------------------------------------------------------------------------------------
-----------------------------------------+3 rows in set (0.00 sec)

(5).删除一个键-值对

mysql> update t3 set jdoc=JSON_REMOVE(jdoc,'$.hobby') where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t3;
+----+----------------------------------------------------------+
| id | jdoc                                                     |
+----+----------------------------------------------------------+
|  1 | {"age": 21, "name": "张三"}                              |
|  2 | {"age": 22, "name": "李四"}                              |
|  3 | {"age": 45, "name": "中年大叔", "address": "北京"}       |
+----+----------------------------------------------------------+
3 rows in set (0.00 sec)

json 函数的完整列表

分类	函数	描述
创建JSON	json_array	创建JSON数组
	json_object	创建JSON对象
	json_quote	将JSON对象转换成JSON字符串类型

查询JSON	json_contains	判断是否包含某个JSON值
	json_contains_path	判断某个路径下是否包JSON值
	json_extract	提取JSON值
	json_keys	提取JSON中的键值为JSON数组
	json_search	按给定字符串关键字搜索JSON，返回匹配的路径

修改JSON	json_array_append	末尾添加数组元素，如果原有值是数值或JSON对象，则转成数组后，再添加元素
	json_array_insert	插入数组元素
	json_insert	插入值(插入新值，但不替换已经存在的旧值)
	json_merge	合并JSON数组或对象
	json_remove	删除JSON数据
	json_replace	替换值(只替换已经存在的旧值)
	json_set	设置值(替换旧值，并插入不存在的新值)
	json_unquote	去除JSON字符串的引号，将值转换成字符串类型

返回JSON属性	json_depth	返回JSON文档的最大深度
	json_length	返回JSON文档的长度
	json_type	返回JSON值的类型
	json_valid	判断是否为合法的JSON文档

参考

https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html