author 曹扬进
createTime 2022-05-14
2022-05-19: 培训结束
# MySQL JSON类型
在我们的项目中经常会使用MYSQL 中的 JSON来定义一些不确定或可能扩展的属性，方便后续的改动而不涉及数据库字段及相关sql语句的变动。
# 简介
在MySQL5.7.8之后开始支持一种可高效获取JSON文本中数据的原生JSON类型，该类型具有以下优点:
- JSON数据有效性检查：BLOB等类型无法在数据库层做这样的约束性检查，
- 保证了JSON数据类型的强校验，JSON数据列会自动校验存入此列的内容是否符合JSON格式，非正常格式则报错，而varchar类型和text等类型本身是不存在这种机制的。
- 查询性能的提升：查询不需要遍历所有字符串才能找到数据
- 支持索引：通过虚拟列的功能可以对JSON中的部分数据进行索引
另外，系统对JSON格式做了一些限制：
JSON文本的最大长度取决有系统常量：max_allowed_packet。该值仅在服务器进行存储的时候进行限制，在内存中进行计算的时候是允许超过该值的。
JSON列不可有默认值（声明时"DEFAULT NULL"）。
JSON列与其他二进制类型列一样是无法创建索引。但是可以从JSON列中所存储的文本中某些表列值进行创建索引。MySQL最优控制器同样在通过JSON表达创建的索引中进行查询。
可以基于JSON格式的特征支持修改特定的键值。（即不需要把整条内容拿出来放到程序中遍历然后寻找替换再塞回去，MySQL内置的函数允许你通过一条SQL语句就能搞定）。
# 简单使用
# 1.创建表
CREATE TABLE tbl_json_table (
id INT ( 11 ) NOT NULL auto_increment,
content json, PRIMARY KEY ( id )
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = 'json 函数测试';
mysql> desc tbl_json_table;
+---------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| content | json | YES | | NULL | |
+---------+------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 2.数据插入
INSERT into tbl_json_table VALUES(1,'{"id":1,"name":"cyj"}');
INSERT into tbl_json_table VALUES(2,'{"id":2,"name":"cyj","hobby":{"play":["games","billiards"],"eat":["hamburg","rice","noddles"]}}');
INSERT into tbl_json_table VALUES(3,'{"id":3,"name":"cyj","hobby":{"play":["games","billiards"],"eat":["hamburg","soup","noddles"]}}');
INSERT into tbl_json_table VALUES(4,'{"id":4,"name":"cyj","hobby":{"play":[{"games":"billiards"},{"games":"hide and seek"}],"eat":["hamburg","soup","noddles"]}}');
INSERT into tbl_json_table VALUES(5,'{"id":5,"name":"cyj","hobby":{"play":[{"games":"billiards"},{"games":"hide and seek"}],"eat":["hamburg","soup","noddles"],"watch":{"video":"bilibili","movie":"iqiyi"}}}');
INSERT into tbl_json_table VALUES(6,'{"id":6,"name":"cyj","hobby":{"play":[{"games":"billiards"},{"games":"hide and seek"}],"eat":[{"hamburg":["English","Chinese","American"]},{"soup":["English","Chinese","American"]},{"noddles":["English","Chinese","American"]}],"watch":{"video":"bilibili","movie":"iqiyi"}}}');
mysql> select * from tbl_json_table;
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | content |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"id": 1, "name": "cyj"} |
| 2 | {"id": 2, "name": "cyj", "hobby": {"eat": ["hamburg", "rice", "noddles"], "play": ["games", "billiards"]}} |
| 3 | {"id": 3, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": ["games", "billiards"]}} |
| 4 | {"id": 4, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}]}} |
| 5 | {"id": 5, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} |
| 6 | {"id": 6, "name": "cyj", "hobby": {"eat": [{"hamburg": ["English", "Chinese", "American"]}, {"soup": ["English", "Chinese", "American"]}, {"noddles": ["English", "Chinese", "American"]}], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
插入JSON列存储数据必须是JSON格式，否则会报错。
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'test.content'.
# MYSQL 中部分JSON查询函数的使用
# JSON_CONTAINS ( target, candidate [, path ] )
用来查找 tartget 中是否存在 candidate元素 或在 target 的指定 path 下是否存在 candidate元素，存在返回1，不存在返回0。
以上面的插入的数据来演示 JSON_CONTAINS 的使用，下同。
mysql> SELECT * FROM tbl_json_table WHERE JSON_CONTAINS(content, '1','$.id');
+----+--------------------------+
| id | content |
+----+--------------------------+
| 1 | {"id": 1, "name": "cyj"} |
+----+--------------------------+
1 row in set (0.00 sec)
寻找 content 列中 id 包含 1的行
mysql> SELECT * FROM tbl_json_table WHERE JSON_CONTAINS(content, '"cyj"','$.name');
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | content |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"id": 1, "name": "cyj"} |
| 2 | {"id": 2, "name": "cyj", "hobby": {"eat": ["hamburg", "rice", "noddles"], "play": ["games", "billiards"]}} |
| 3 | {"id": 3, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": ["games", "billiards"]}} |
| 4 | {"id": 4, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}]}} |
| 5 | {"id": 5, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} |
| 6 | {"id": 6, "name": "cyj", "hobby": {"eat": [{"hamburg": ["English", "Chinese", "American"]}, {"soup": ["English", "Chinese", "American"]}, {"noddles": ["English", "Chinese", "American"]}], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
寻找 content 列中 name 存在 "cyj" 的行
mysql> SELECT * FROM tbl_json_table WHERE JSON_CONTAINS(content, '"rice"','$.hobby.eat');
+----+------------------------------------------------------------------------------------------------------------+
| id | content |
+----+------------------------------------------------------------------------------------------------------------+
| 2 | {"id": 2, "name": "cyj", "hobby": {"eat": ["hamburg", "rice", "noddles"], "play": ["games", "billiards"]}} |
+----+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
寻找多层路径下的 数组中包含 指定字符 的行
mysql> SELECT * FROM tbl_json_table WHERE JSON_CONTAINS(content, '{"games":"billiards"}','$.hobby.play');
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | content |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 4 | {"id": 4, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}]}} |
| 5 | {"id": 5, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} |
| 6 | {"id": 6, "name": "cyj", "hobby": {"eat": [{"hamburg": ["English", "Chinese", "American"]}, {"soup": ["English", "Chinese", "American"]}, {"noddles": ["English", "Chinese", "American"]}], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
寻找指定路径下是存在对象指定对象的行
# JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)]
判断JSON中是否存在指定路径，返回0或1。如果任何参数为NULL则返回NULL。如果json_doc参数不是一个有效的JSON文档，任何路径参数不是一个有效的路径表达式，或者one_or_all不是'one'或'all'，则会发生错误。
如果文档中不存在指定的路径，则返回值为0。否则，返回值依赖于one_or_all参数:
'one':如果文档中至少存在一个路径，则为1，否则为0。
'all':如果所有路径都不存在，则为1，否则为0。
以上面的插入的数据来演示 JSON_CONTAINS_PATH 的使用，下同。
mysql> SELECT * FROM tbl_json_table WHERE JSON_CONTAINS_PATH(content, 'one', '$.id');
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | content |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"id": 1, "name": "cyj"} |
| 2 | {"id": 2, "name": "cyj", "hobby": {"eat": ["hamburg", "rice", "noddles"], "play": ["games", "billiards"]}} |
| 3 | {"id": 3, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": ["games", "billiards"]}} |
| 4 | {"id": 4, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}]}} |
| 5 | {"id": 5, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} |
| 6 | {"id": 6, "name": "cyj", "hobby": {"eat": [{"hamburg": ["English", "Chinese", "American"]}, {"soup": ["English", "Chinese", "American"]}, {"noddles": ["English", "Chinese", "American"]}], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
寻找含有指定路径的行
mysql> SELECT * FROM tbl_json_table WHERE JSON_CONTAINS_PATH(content, 'all', '$.hobby','$.id');
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | content |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2 | {"id": 2, "name": "cyj", "hobby": {"eat": ["hamburg", "rice", "noddles"], "play": ["games", "billiards"]}} |
| 3 | {"id": 3, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": ["games", "billiards"]}} |
| 4 | {"id": 4, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}]}} |
| 5 | {"id": 5, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} |
| 6 | {"id": 6, "name": "cyj", "hobby": {"eat": [{"hamburg": ["English", "Chinese", "American"]}, {"soup": ["English", "Chinese", "American"]}, {"noddles": ["English", "Chinese", "American"]}], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
寻找含有多个指定路径的行
# JSON_EXTRACT( json_doc, path [, path] ...)
从JSON文档中返回数据，从文档中与path参数匹配的部分中选择。如果任何参数为NULL或文档中没有找到某个值的路径，则返回NULL。如果json_doc参数不是有效的JSON文档或任何路径参数不是有效的路径表达式，就会发生错误。
返回值由路径参数匹配的所有值组成。如果这些参数可能返回多个值，则匹配的值将自动包装为一个数组，顺序与产生它们的路径相对应。否则，返回值为单个值。
以上面的插入的数据来演示 JSON_EXTRACT的使用，下同。
mysql> SELECT JSON_EXTRACT(content, '$.id'),JSON_EXTRACT(content, '$.hobby') FROM tbl_json_table WHERE JSON_EXTRACT(content, '$.id')>1 ORDER BY JSON_EXTRACT(content, '$.id') desc;
+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_EXTRACT(content, '$.id') | JSON_EXTRACT(content, '$.hobby') |
+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 6 | {"eat": [{"hamburg": ["English", "Chinese", "American"]}, {"soup": ["English", "Chinese", "American"]}, {"noddles": ["English", "Chinese", "American"]}], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}} |
| 5 | {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}} |
| 4 | {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}]} |
| 3 | {"eat": ["hamburg", "soup", "noddles"], "play": ["games", "billiards"]} |
| 2 | {"eat": ["hamburg", "rice", "noddles"], "play": ["games", "billiards"]} |
+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
JSON_EXTRACT函数可以用在语句的各个位置
# column->path
当与两个参数一起使用时，->操作符用作JSON_EXTRACT()函数的别名，左边的列标识符和右边的JSON路径(字符串字面量)，该路径根据JSON文档(列值)计算。可以在SQL语句中出现列引用的任何地方使用这些表达式来代替列引用。
-> 操作符 与JSON_EXTRACT()的功能相同
mysql> SELECT content->'$.id',content->'$.hobby' FROM tbl_json_table WHERE content->'$.id'>1 ORDER BY content->'$.id' desc;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| content->'$.id' | content->'$.hobby' |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 6 | {"eat": [{"hamburg": ["English", "Chinese", "American"]}, {"soup": ["English", "Chinese", "American"]}, {"noddles": ["English", "Chinese", "American"]}], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}} |
| 5 | {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}} |
| 4 | {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}]} |
| 3 | {"eat": ["hamburg", "soup", "noddles"], "play": ["games", "billiards"]} |
| 2 | {"eat": ["hamburg", "rice", "noddles"], "play": ["games", "billiards"]} |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
可以看到这段sql的返回结果与上面的例子相同
# JSON虚拟列的使用
对于索引，JSON字段无法对其中的一个key值进行索引，但是虚拟列可以，我们可以建立一个虚拟列和JSON中key值建立联系。
添加虚拟列
ALTER TABLE tbl_json_table ADD COLUMN v_name varchar(20) AS (content->'$.name');
在执行完表结构修改后
mysql> SELECT * FROM tbl_json_table;
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| id | content | v_name |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| 1 | {"id": 1, "name": "cyj"} | "cyj" |
| 2 | {"id": 2, "name": "cyj", "hobby": {"eat": ["hamburg", "rice", "noddles"], "play": ["games", "billiards"]}} | "cyj" |
| 3 | {"id": 3, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": ["games", "billiards"]}} | "cyj" |
| 4 | {"id": 4, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}]}} | "cyj" |
| 5 | {"id": 5, "name": "cyj", "hobby": {"eat": ["hamburg", "soup", "noddles"], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} | "cyj" |
| 6 | {"id": 6, "name": "cyj", "hobby": {"eat": [{"hamburg": ["English", "Chinese", "American"]}, {"soup": ["English", "Chinese", "American"]}, {"noddles": ["English", "Chinese", "American"]}], "play": [{"games": "billiards"}, {"games": "hide and seek"}], "watch": {"movie": "iqiyi", "video": "bilibili"}}} | "cyj" |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
6 rows in set (0.00 sec)
可以看到查询返回多了v_name列
对虚拟列建立索引
ALTER TABLE tbl_json_table ADD INDEX virtual_index(v_name);
执行完建索引的语句后
mysql> EXPLAIN SELECT * FROM tbl_json_table WHERE v_name = 'cyj';
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tbl_json_table | NULL | ref | virtual_index | virtual_index | 63 | const | 1 | 100.00 | NULL |
+----+-------------+----------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
可以看到查询已经走索引了
虚拟列需要注意，在更新和插入数据时，不要给虚拟列设定值，否则会引发错误。
ERROR 3105 (HY000): The value specified for generated column 'v_name' in table 'test' is not allowed.