作者：杨涛涛
资深数据库专家，专研 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 数组的范畴遍历性能，心愿大家在开发过程中更加得心应手。