现在很多应用环境中都能看到JSON灵活的影子。各阶段数据层次的递归层次,能很好的分辨。一直对MySQL的JSON很期待的,最近才有时间研究一下。
JSON就是一串字符串,只不过元素会使用特定的符号标注。比如:
关系型数据库实现JSON难度在于,关系型数据库需要定义数据库和表结构。为了应对这一点,从MySQL 5.7开始,MySQL支持了JavaScript对象表示(JavaScriptObject Notation,JSON) 数据类型。之前,这类数据不是单独的数据类型,会被存储为字符串。新的JSON数据类型提供了自动验证的JSON文档以及优化的存储格式。
MySQL里JSON文档以二进制格式存储,它提供以下功能:
数据保存到MySQL,操作方面都提供哪些支持?目前MySQL 8.0版本的的JSON总共支持32个普通函数和2个空间函数:
MySQL在JSON值和其他类型值之间转换时遵循的规则:
CAST(other type AS JSON)
结果为JSON类型的NULL值。
mysql>SET @j5 = '{"id":123, "name":"kevin","age":20, "time":"2021-06-01 01:00:00"}';
Query OK, 0 rows affected (0.00 sec)
mysql>SELECT CAST(JSON_EXTRACT(@j5, '$.age') AS UNSIGNED);+----------------------------------------------+
| CAST(JSON_EXTRACT(@j5, '$.age') AS UNSIGNED) |
+----------------------------------------------+
| 20 |
+----------------------------------------------+
1 row in set (0.00 sec)
对于JSON值的聚合,NULL值和其他数据类型一样被忽略。除MIN()、MAX()和GROUP_CONCAT()外,非NULL值被转换为数字类型并聚合。对于数字标量的JSON值,(取决于值)可能会出现截断和精度损失。
MySQL JSON列上无法创建索引,是通过从JSON列中提取标量值,创建索引。这样能更有效的结合MySQL优势。
col_name data_type [GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
VIRTUAL或STORED关键字表示列值是如何存储的,这对列的使用影响非常大:
mysql> DROP TABLE IF EXISTS `jemp`;mysql> CREATE TABLE `jemp` (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c JSON,
d JSON, g INT GENERATED ALWAYS AS (c->"$.id") STORED,
INDEX i (g)
);
Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO jemp (c,d) VALUES
('{"id": "1", "name": "Fred"}' , '{"user":"Fred", "user_id":1, "zipcode":"[14471,14531]"}'),
('{"id": "2", "name": "Wilma"}', '{"user":"Wilma", "user_id":2, "zipcode":[24472,24532]}' ),
('{"id": "3", "name": "Jack"}' , '{"user":"Jack", "user_id":3, "zipcode":[34473,34533]}' ),
('{"id": "4", "name": "Betty"}', '{"user":"Betty", "user_id":4, "zipcode":[44474,44534]}' );
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
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)
ERROR:
No query specified
mysql> SHOW WARNINGS\G*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`db1`.`jemp`.`c`,'$.name')) AS `name` from `db1`.`jemp` where (`db1`.`jemp`.`g` > 2)
1 row in set (0.00 sec)
直接接口:MEMBER OF(),JSON_CONTAINS(),JSON_OVERLAPS()
mysql> ALTER TABLE jemp ADD INDEX zips( (CAST(d->'$.zipcode' AS UNSIGNED ARRAY)) );
#MEMBER OF
mysql> EXPLAIN SELECT * FROM jemp WHERE 24472 MEMBER OF(d->'$.zipcode')\G*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jemp
partitions: NULL
type: ref
possible_keys: zips
key: zips
key_len: 9 ref: const
rows: 1
filtered: 100.00 Extra: Using where
1 row in set, 1 warning (0.00 sec)
#JSON_CONTAINS
mysql> EXPLAIN SELECT * FROM jemp WHERE JSON_CONTAINS(d->'$.zipcode', CAST('[14471,14531]' AS JSON))\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jemp
partitions: NULL
type: range
possible_keys: zips
key: zips
key_len: 9
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
#JSON_OVERLAPS
mysql> EXPLAIN SELECT * FROM jemp WHERE JSON_OVERLAPS(d->'$.zipcode', CAST('[44474,94582]' AS JSON))\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jemp
partitions: NULL
type: range
possible_keys: zips
key: zips
key_len: 9
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
从上面例子里,数据的查询还是基于MySQL B+tree上,JSON只是一种数据保存的机制。通过对虚拟列方式,提供快速的访问,非常好的解决了JSON支持问题。
关于作者
崔虎龙,云和恩墨MySQL技术顾问,长期服务于金融、游戏、物流等行业的数据中心,设计数据存储架构,并熟悉数据中心运营管理的流程及规范,自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验,10年数据库运维工作经验,其中专职做MySQL工作8年;曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务;涉及行业:金融(银行、理财)、物流、游戏、医疗、重工业等。
墨天轮原文链接:https://www.modb.pro/db/68425(复制到浏览器或者点击“阅读原文”立即查看)