数据类型：JSON

MySQL支持一种由RFC 7159定义的原生JSON数据类型，可以有效地访问JSON (JavaScript对象表记法)文档中的数据。与在字符串列中存储JSON格式的字符串相比，JSON数据类型提供了以下优势:

• 对文档元素的快速读取访问。
• 当服务器再次读取JSON文档时，不需要重新解析文本获取该值。
• 通过键或数组索引直接查找子对象或嵌套值，而不需要读取文档中的所有值。

创建表：

JSON列不能直接建立索引。要创建一个索引来间接引用这样的列，你可以定义一个生成的列来提取应该被索引的信息，然后在生成的列上创建一个索引

mysql> CREATE TABLE jemp (
-> c JSON,
-> g INT GENERATED ALWAYS AS (c->”$.id”),
-> INDEX i (g)
-> )engine=innodb;
Query OK, 0 rows affected (0.53 sec)

新增数据：

mysql> INSERT INTO jemp (c) VALUES (‘{“id”: “1”, “name”: “Fred”}’), (‘{“id”: “2”, “name”: “Wilma”}’), (‘{“id”: “3”, “name”: “Barney”}’), (‘{“id”: “4”, “name”: “Betty”}’);
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from jemp;
+——————————-+——+
| c | g |
+——————————-+——+
| {“id”: “1”, “name”: “Fred”} | 1 |
| {“id”: “2”, “name”: “Wilma”} | 2 |
| {“id”: “3”, “name”: “Barney”} | 3 |
| {“id”: “4”, “name”: “Betty”} | 4 |
+——————————-+——+
4 rows in set (0.00 sec)

查询数据：

可以使用-＞ 和-＞＞ 运算符检索JSON列的字段：

mysql> SELECT c->”$.name” AS name FROM jemp WHERE g > 2;
+———-+
| name |
+———-+
| “Barney” |
| “Betty” |
+———-+
2 rows in set (0.00 sec)

mysql> SELECT c->>”$.name” AS name FROM jemp WHERE g > 2;
+——–+
| name |
+——–+
| Barney |
| Betty |
+——–+
2 rows in set (0.00 sec)

mysql> explain SELECT c->>”$.name” AS name FROM jemp WHERE g > 2\G
* 1. row *
id: 1
select_type: SIMPLE
table: jemp
partitions: NULL
type: range
possible_keys: i
key: i
key_len: 5
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)

我们可以看到这其中用到了索引

编辑数据：

可以使用三种不同的函数来修改数据：JSON_SET（）、JSON_INSERT（）和JSON_REPLACE（）JSON_SET()， JSON_INSERT()和JSON_REPLACE()函数是相关的:

• JSON_SET()替换现有值并添加不存在的值。
• JSON_INSERT()插入值而不替换现有值。
• JSON_REPLACE()只替换现有值。

mysql> update jemp set c=JSON_SET(c,”$.id”,1,”$.name”,”zhangsan”) where g=1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from jemp where g=1;
+——————————-+——+
| c | g |
+——————————-+——+
| {“id”: 1, “name”: “zhangsan”} | 1 |
+——————————-+——+
1 row in set (0.00 sec)

mysql> update jemp set c=JSON_INSERT(C,”$.age”,20) where g=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from jemp where g=1;
+——————————————+——+
| c | g |
+——————————————+——+
| {“id”: 1, “age”: 20, “name”: “zhangsan”} | 1 |
+——————————————+——+
1 row in set (0.00 sec)

删除数据：

JSON_REMOVE能从JSON文档中删除数据

mysql> select JSON_REMOVE(c,’$.name’) from jemp where g=1;
+————————-+
| JSON_REMOVE(c,’$.name’) |
+————————-+
| {“id”: 1} |
+————————-+
1 row in set (0.00 sec)

mysql> select * from jemp;
+——————————-+——+
| c | g |
+——————————-+——+
| {“id”: 1, “name”: “zhangsan”} | 1 |
| {“id”: “2”, “name”: “Wilma”} | 2 |
| {“id”: “3”, “name”: “Barney”} | 3 |
| {“id”: “4”, “name”: “Betty”} | 4 |
+——————————-+——+
4 rows in set (0.00 sec)

参考资料：

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

https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-inline-path

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