关于mysql8:新特性解读-数组范围遍历功能

作者：杨涛涛
资深数据库专家，专研 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 的后果）：

# javascript
set @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 数组的范畴遍历性能，心愿大家在开发过程中更加得心应手。