新特性解读 | 数组范围遍历功能

发布时间:2020-10-09 浏览次数:193

作者:杨涛涛

资深数据库专家,专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生,为各大运营商及银行金融企业提供 MySQL 相关技术支持、MySQL 相关课程培训等工作。

本文来源:原创投稿 *爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


大家都知道,MySQL 数据库从 5.7 开始,增加了新的数据类型:JSON。

JSON 类型的出现,弥补了 MySQL 长期以来相比其他关系型数据库的非标准化特性略势,比如可以用 JSON 来实现类似字典、数组等类型。以及之后大量针对 JSON 类型字串的相关遍历方法与函数。

比如对数组来说,简单的遍历路径为 "$[数组小标]" 或者 "$[*]" 来一次性遍历所有元素。MySQL 8.0 又新增了对于数组元素的范围遍历,比如 "$[m to n]",表示遍历下标为 m 到 n 的元素。

举个例子,以下 JSON 字串(随便找张表 explain format=json 的结果):

# javascriptset @json_str1 = '{  "query_block": {    "table": {      "filtered": "100.00",      "cost_info": {        "eval_cost": "898504.10",        "read_cost": "17457.25",        "prefix_cost": "915961.35",        "data_read_per_join": "137M"      },      "table_name": "t1",      "access_type": "ALL",      "used_columns": [        "id",        "r1",        "r2",        "r3",        "r4",        "r5",        "r6",        "r7",        "r8"      ],      "rows_examined_per_scan": 8985041,      "rows_produced_per_join": 8985041    },    "cost_info": {      "query_cost": "915961.35"    },    "select_id": 1  }}';

其中 key 为 used_columns 对应的值就是一个数组,现在我把这个数据遍历出来,并且生成以下结果:

# javascript["id", "r1", "r2", "r3", "r4", "r5", "r6", "r7", "r8"]
["id", "r1", "r2", "r3", "r4", "r5", "r6", "r7"]
["id", "r1", "r2", "r3", "r4", "r5", "r6"]
["id", "r1", "r2", "r3", "r4", "r5"]
["id", "r1", "r2", "r3", "r4"]
["id", "r1", "r2", "r3"]
["id", "r1", "r2"]
["id", "r1"]
["id"]

先把这个键对应的值赋给一个变量:

mysql> select json_extract(@json_str1,"$.query_block.table.used_columns") into @json_array1;Query OK, 1 row affected (0.00 sec)

接下来写个传统 PATH 遍历数据的存储过程来实现以上这个结果:

DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_print_json_array_sample`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_print_json_array_sample`(    IN f_str1 JSON    )BEGIN    DECLARE v_tmp_length TINYINT UNSIGNED DEFAULT 0;    DECLARE i TINYINT UNSIGNED DEFAULT 0;
   SET v_tmp_length = JSON_LENGTH(f_str1);    SET i = v_tmp_length;    SET @v_tmp_result = f_str1;
   DROP TEMPORARY TABLE IF EXISTS tmp;    CREATE TEMPORARY TABLE IF NOT EXISTS tmp(str1 JSON);
   WHILE i > 0    DO      SET i = i - 1;      SET @stmt = CONCAT('select json_remove(@v_tmp_result,"$[',i,']") into @v_tmp_result');      INSERT INTO tmp VALUES (@v_tmp_result);      PREPARE s1 FROM @stmt;      EXECUTE s1;      END WHILE;      DROP PREPARE s1;
     SET @v_stmt = NULL;      SET @v_tmp_result = NULL;      SELECT * FROM tmp;END$$
DELIMITER ;

调用下这个存储过程:

mysql> call sp_print_json_array_sample(@json_array1);+--------------------------------------------------------+| str1                                                   |+--------------------------------------------------------+| ["id", "r1", "r2", "r3", "r4", "r5", "r6", "r7", "r8"] || ["id", "r1", "r2", "r3", "r4", "r5", "r6", "r7"]       || ["id", "r1", "r2", "r3", "r4", "r5", "r6"]             || ["id", "r1", "r2", "r3", "r4", "r5"]                   || ["id", "r1", "r2", "r3", "r4"]                         || ["id", "r1", "r2", "r3"]                               || ["id", "r1", "r2"]                                     || ["id", "r1"]                                           || ["id"]                                                 |+--------------------------------------------------------+9 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

那在 MySQL 8.0 下,可以直接把这个存储过程给优化掉,简化的写法如下:

DELIMITER $$
USE `ytt`$$
DROP PROCEDURE IF EXISTS `sp_print_json_array_sample`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_print_json_array_sample`(    IN f_str1 JSON    )BEGIN    DECLARE v_tmp_length TINYINT UNSIGNED DEFAULT 0;    DECLARE i TINYINT UNSIGNED DEFAULT 0;
   SET v_tmp_length = JSON_LENGTH(f_str1);    SET i = v_tmp_length;    DROP TEMPORARY TABLE IF EXISTS tmp;    CREATE TEMPORARY TABLE IF NOT EXISTS tmp(str1 JSON);
   WHILE i > 0    DO      SET i = i - 1;      SET @stmt = CONCAT(' insert into tmp select json_extract(''',f_str1,''','' $[ 0 to     ',i,']'') ');      PREPARE s1 FROM @stmt;      EXECUTE s1;    END WHILE;
   DROP PREPARE s1;    SET @v_stmt = NULL;
   SELECT str1 AS array_result FROM tmp;
END$$
DELIMITER ;

调用结果一样:

mysql> call sp_print_json_array_sample(@json_array1);+--------------------------------------------------------+| array_result                                           |+--------------------------------------------------------+| ["id", "r1", "r2", "r3", "r4", "r5", "r6", "r7", "r8"] || ["id", "r1", "r2", "r3", "r4", "r5", "r6", "r7"]       || ["id", "r1", "r2", "r3", "r4", "r5", "r6"]             || ["id", "r1", "r2", "r3", "r4", "r5"]                   || ["id", "r1", "r2", "r3", "r4"]                         || ["id", "r1", "r2", "r3"]                               || ["id", "r1", "r2"]                                     || ["id", "r1"]                                           || ["id"]                                                 |+--------------------------------------------------------+9 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

其中路径 "$[m to n]" , n 也可以是保留字 last,代表最后一个下标,比如:


mysql> select json_extract(@json_array1,'$[0 to last-7]') as result;+--------------+| result       |+--------------+| ["id", "r1"] |+--------------+1 row in set (0.00 sec)


总结

这里简单介绍了 MySQL 8.0 对于 JSON 数组的范围遍历功能,希望大家在开发过程以及数据库性能优化中更加得心应手。

上一篇: 新特性解读 | 组复制成员的自动保护模式

下一篇: 技术分享 | MySQL 内存管理初探

产品试用 产品试用
400-820-6580 免费电话