作者：杨涛涛
资深数据库专家，专研 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 对应的值就是一个数组，现在我把这个数据遍历出来，并且生成以下结果：
[][][][][][][][][]
先把这个键对应的值赋给一个变量：
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)BEGINDECLARE 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 > 0DOSET 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();+--------------------------------------------------------+| 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)BEGINDECLARE 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 > 0DOSET 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();+--------------------------------------------------------+| 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 数组的范围遍历功能，希望大家在开发过程中更加得心应手。