mysql，二维json数组查询，并再次组装新表，存储过程，临时表，内存表，遍历JSON数组，遍历SELECT的结果

分享背景

• 一个二维数组，以json 的形式，存储在mysql数据库（5.7版本）中，需要模糊查询 该二维数组的数组元素 中 某个KEY的VALUE （相当于 示例中 模糊查询 name）
• 上面的二维数组，结构示例

[{"index":[{"name":"格式简述"},{"name":"格式简述"}]},
{"index":[{"name":"格式简述"},{"name":"格式简述"}]}]

• 目标：有几个命中的 index 元素，则返回几行数据

涉及的知识点

• 存储过程中，使用零时表，保存数据
• 存储过程中，遍历select 的结果集合
• 存储过程中，遍历json数组的元素
• JSON_SEARCH 的使用 （找不到，就是NULL）

JSON_SEARCH( dimension, 'one', '%度%', NULL, '$[*].index[*].name' ) IS NOT NULL

在dimension 字段中查询 所有 的  维度的  index 元素，的name属性  包含 度 字的数据  没有命中，则返回 null

初始数据准备

drop table if EXISTS per_package_employee;

CREATE TABLE `per_package_employee` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `site_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '公司id',
  `employee_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `package_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '所属绩效考核包id',
  `dimension` json NOT NULL COMMENT '维度配置数组列表',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `pe_site_package_employee` (`site_id`,`package_id`,`employee_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8268 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='绩效考核个人记录';

DELETE FROM per_package_employee where id >0;
INSERT INTO `per_package_employee`(`id`, `site_id`, `employee_id`, `package_id`, `dimension`) VALUES (1, 569, 8059, 1, '[{\"name\": \"维度A\", \"index\": [{\"id\": \"569_60949a9a863de\", \"name\": \"维度A-1\", \"need\": 0, \"type\": 1, \"unit\": \"V\", \"custom\": 1, \"remark\": \"的风帆股份人头付费\", \"target\": \"50\", \"weight\": \"20\", \"schedule\": [], \"per_remark\": \"打发打发发放\", \"score_info\": [{\"id\": 3, \"weight\": 100, \"employees\": [{\"point\": null, \"title\": \"直接主管评分：王五(100%)\", \"remark\": \"\", \"employee_id\": 4004}], \"multi_executor\": 1}], \"point_limit\": 0, \"result_type\": \"none\", \"reviewer_id\": \"\", \"mamage_record\": [], \"result_employee_id\": 0}], \"index_num\": 0, \"index_type\": 1, \"weight_type\": 0, \"target_index\": 1, \"total_weight\": 0, \"dimension_weight\": 100, \"all_dimension_index_weight\": 0}, {\"name\": \"维度B\", \"index\": [{\"id\": 6, \"name\": \"非量化指标1\", \"need\": 0, \"type\": 2, \"unit\": \"\", \"custom\": 0, \"remark\": \"电放费GV各回各家发给\", \"target\": \"0\", \"weight\": 1, \"schedule\": [], \"per_remark\": \"发给回个话更换\", \"score_info\": [{\"id\": 3, \"weight\": 100, \"employees\": [{\"point\": null, \"title\": \"直接主管评分：王五(100%)\", \"remark\": \"\", \"employee_id\": 4004}], \"multi_executor\": 1}], \"point_limit\": 0, \"result_type\": \"none\", \"reviewer_id\": 0, \"mamage_record\": [], \"result_employee_id\": 0}], \"index_num\": 0, \"index_type\": 2, \"weight_type\": 0, \"target_index\": 1, \"total_weight\": 0, \"dimension_weight\": 100, \"all_dimension_index_weight\": 0}, {\"name\": \"维度C\", \"index\": [{\"id\": 7, \"name\": \"加分项1\", \"need\": 0, \"type\": 3, \"unit\": \"\", \"custom\": 0, \"remark\": \"\", \"target\": \"0\", \"weight\": 0, \"schedule\": [], \"per_remark\": \"的发广告和和个\", \"score_info\": [{\"id\": 3, \"weight\": 100, \"employees\": [{\"point\": null, \"title\": \"直接主管评分：王五(100%)\", \"remark\": \"\", \"employee_id\": 4004}], \"multi_executor\": 1}], \"point_limit\": \"\", \"result_type\": \"none\", \"reviewer_id\": \"\", \"mamage_record\": [], \"result_employee_id\": 0}], \"index_num\": 0, \"index_type\": 3, \"weight_type\": 0, \"target_index\": 1, \"total_weight\": 0, \"dimension_weight\": 100, \"all_dimension_index_weight\": 0}, {\"name\": \"维度D\", \"index\": [{\"id\": 8, \"name\": \"扣分项1\", \"need\": 0, \"type\": 4, \"unit\": \"\", \"custom\": 0, \"remark\": \"\", \"target\": \"0\", \"weight\": 0, \"schedule\": [], \"per_remark\": \"大大方方要统一\", \"score_info\": [{\"id\": 3, \"weight\": 100, \"employees\": [{\"point\": null, \"title\": \"直接主管评分：王五(100%)\", \"remark\": \"\", \"employee_id\": 4004}], \"multi_executor\": 1}], \"point_limit\": \"\", \"result_type\": \"none\", \"reviewer_id\": 0, \"mamage_record\": [], \"result_employee_id\": 0}], \"index_num\": 0, \"index_type\": 4, \"weight_type\": 0, \"target_index\": 1, \"total_weight\": 0, \"dimension_weight\": 100, \"all_dimension_index_weight\": 0}]');
INSERT INTO `per_package_employee`(`id`, `site_id`, `employee_id`, `package_id`, `dimension`) VALUES (2, 569, 8059, 1, '[{\"name\": \"维度A\", \"index\": [{\"id\": \"569_60949a9a863de\", \"name\": \"维度A-1\", \"need\": 0, \"type\": 1, \"unit\": \"V\", \"custom\": 1, \"remark\": \"的风帆股份人头付费\", \"target\": \"50\", \"weight\": \"100\", \"schedule\": [], \"per_remark\": \"打发打发发放\", \"score_info\": [{\"id\": 3, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"直接主管评分：5号(20%)\", \"remark\": \"\", \"employee_id\": 8064}, {\"point\": null, \"title\": \"直接主管评分：王五(20%)\", \"remark\": \"\", \"employee_id\": 4004}], \"multi_executor\": 1}, {\"id\": 4, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"二级主管评分：6号(20%)\", \"remark\": \"\", \"employee_id\": 8065}, {\"point\": null, \"title\": \"二级主管评分：5号(20%)\", \"remark\": \"\", \"employee_id\": 8064}], \"multi_executor\": 1}, {\"id\": 5, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"三级主管评分：张三(20%)\", \"remark\": \"\", \"employee_id\": 4000}, {\"point\": null, \"title\": \"三级主管评分：6号(20%)\", \"remark\": \"\", \"employee_id\": 8065}], \"multi_executor\": 1}, {\"id\": 6, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"四级主管评分：赵六(20%)\", \"remark\": \"\", \"employee_id\": 4006}, {\"point\": null, \"title\": \"四级主管评分：张三(20%)\", \"remark\": \"\", \"employee_id\": 4000}], \"multi_executor\": 1}, {\"id\": 7, \"weight\": \"10\", \"employees\": [{\"point\": null, \"title\": \"五级主管评分：李四(10%)\", \"remark\": \"\", \"employee_id\": 4002}, {\"point\": null, \"title\": \"五级主管评分：赵六(10%)\", \"remark\": \"\", \"employee_id\": 4006}], \"multi_executor\": 1}, {\"id\": 8, \"weight\": \"10\", \"employees\": [{\"point\": null, \"title\": \"六级主管评分：8号(10%)\", \"remark\": \"\", \"employee_id\": 8067}, {\"point\": null, \"title\": \"六级主管评分：李四(10%)\", \"remark\": \"\", \"employee_id\": 4002}], \"multi_executor\": 1}], \"point_limit\": 0, \"result_type\": \"none\", \"reviewer_id\": \"\", \"mamage_record\": [], \"result_employee_id\": 0}], \"index_num\": 0, \"index_type\": 1, \"weight_type\": 0, \"target_index\": 1, \"total_weight\": 0, \"dimension_weight\": 100, \"all_dimension_index_weight\": 0}, {\"name\": \"维度B\", \"index\": [{\"id\": 6, \"name\": \"非量化指标1\", \"need\": 0, \"type\": 2, \"unit\": \"\", \"custom\": 0, \"remark\": \"电放费GV各回各家发给\", \"target\": \"0\", \"weight\": \"100\", \"schedule\": [], \"per_remark\": \"发给回个话更换\", \"score_info\": [{\"id\": 3, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"直接主管评分：5号(20%)\", \"remark\": \"\", \"employee_id\": 8064}, {\"point\": null, \"title\": \"直接主管评分：王五(20%)\", \"remark\": \"\", \"employee_id\": 4004}], \"multi_executor\": 1}, {\"id\": 4, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"二级主管评分：6号(20%)\", \"remark\": \"\", \"employee_id\": 8065}, {\"point\": null, \"title\": \"二级主管评分：5号(20%)\", \"remark\": \"\", \"employee_id\": 8064}], \"multi_executor\": 1}, {\"id\": 5, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"三级主管评分：张三(20%)\", \"remark\": \"\", \"employee_id\": 4000}, {\"point\": null, \"title\": \"三级主管评分：6号(20%)\", \"remark\": \"\", \"employee_id\": 8065}], \"multi_executor\": 1}, {\"id\": 6, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"四级主管评分：赵六(20%)\", \"remark\": \"\", \"employee_id\": 4006}, {\"point\": null, \"title\": \"四级主管评分：张三(20%)\", \"remark\": \"\", \"employee_id\": 4000}], \"multi_executor\": 1}, {\"id\": 7, \"weight\": \"10\", \"employees\": [{\"point\": null, \"title\": \"五级主管评分：李四(10%)\", \"remark\": \"\", \"employee_id\": 4002}, {\"point\": null, \"title\": \"五级主管评分：赵六(10%)\", \"remark\": \"\", \"employee_id\": 4006}], \"multi_executor\": 1}, {\"id\": 8, \"weight\": \"10\", \"employees\": [{\"point\": null, \"title\": \"六级主管评分：8号(10%)\", \"remark\": \"\", \"employee_id\": 8067}, {\"point\": null, \"title\": \"六级主管评分：李四(10%)\", \"remark\": \"\", \"employee_id\": 4002}], \"multi_executor\": 1}], \"point_limit\": 0, \"result_type\": \"none\", \"reviewer_id\": \"\", \"mamage_record\": [], \"result_employee_id\": 0}], \"index_num\": 0, \"index_type\": 2, \"weight_type\": 0, \"target_index\": 1, \"total_weight\": 0, \"dimension_weight\": 100, \"all_dimension_index_weight\": 0}, {\"name\": \"维度C\", \"index\": [{\"id\": 7, \"name\": \"加分项1\", \"need\": 0, \"type\": 3, \"unit\": \"\", \"custom\": 0, \"remark\": \"\", \"target\": \"0\", \"weight\": 0, \"schedule\": [], \"per_remark\": \"的发广告和和个\", \"score_info\": [{\"id\": 3, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"直接主管评分：5号(20%)\", \"remark\": \"\", \"employee_id\": 8064}, {\"point\": null, \"title\": \"直接主管评分：王五(20%)\", \"remark\": \"\", \"employee_id\": 4004}], \"multi_executor\": 1}, {\"id\": 4, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"二级主管评分：6号(20%)\", \"remark\": \"\", \"employee_id\": 8065}, {\"point\": null, \"title\": \"二级主管评分：5号(20%)\", \"remark\": \"\", \"employee_id\": 8064}], \"multi_executor\": 1}, {\"id\": 5, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"三级主管评分：张三(20%)\", \"remark\": \"\", \"employee_id\": 4000}, {\"point\": null, \"title\": \"三级主管评分：6号(20%)\", \"remark\": \"\", \"employee_id\": 8065}], \"multi_executor\": 1}, {\"id\": 6, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"四级主管评分：赵六(20%)\", \"remark\": \"\", \"employee_id\": 4006}, {\"point\": null, \"title\": \"四级主管评分：张三(20%)\", \"remark\": \"\", \"employee_id\": 4000}], \"multi_executor\": 1}, {\"id\": 7, \"weight\": \"10\", \"employees\": [{\"point\": null, \"title\": \"五级主管评分：李四(10%)\", \"remark\": \"\", \"employee_id\": 4002}, {\"point\": null, \"title\": \"五级主管评分：赵六(10%)\", \"remark\": \"\", \"employee_id\": 4006}], \"multi_executor\": 1}, {\"id\": 8, \"weight\": \"10\", \"employees\": [{\"point\": null, \"title\": \"六级主管评分：8号(10%)\", \"remark\": \"\", \"employee_id\": 8067}, {\"point\": null, \"title\": \"六级主管评分：李四(10%)\", \"remark\": \"\", \"employee_id\": 4002}], \"multi_executor\": 1}], \"point_limit\": \"\", \"result_type\": \"none\", \"reviewer_id\": \"\", \"mamage_record\": [], \"result_employee_id\": 0}], \"index_num\": 0, \"index_type\": 3, \"weight_type\": 0, \"target_index\": 1, \"total_weight\": 0, \"dimension_weight\": 100, \"all_dimension_index_weight\": 0}, {\"name\": \"维度D\", \"index\": [{\"id\": 8, \"name\": \"扣分项1\", \"need\": 0, \"type\": 4, \"unit\": \"\", \"custom\": 0, \"remark\": \"\", \"target\": \"0\", \"weight\": 0, \"schedule\": [], \"per_remark\": \"大大方方要统一\", \"score_info\": [{\"id\": 3, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"直接主管评分：5号(20%)\", \"remark\": \"\", \"employee_id\": 8064}, {\"point\": null, \"title\": \"直接主管评分：王五(20%)\", \"remark\": \"\", \"employee_id\": 4004}], \"multi_executor\": 1}, {\"id\": 4, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"二级主管评分：6号(20%)\", \"remark\": \"\", \"employee_id\": 8065}, {\"point\": null, \"title\": \"二级主管评分：5号(20%)\", \"remark\": \"\", \"employee_id\": 8064}], \"multi_executor\": 1}, {\"id\": 5, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"三级主管评分：张三(20%)\", \"remark\": \"\", \"employee_id\": 4000}, {\"point\": null, \"title\": \"三级主管评分：6号(20%)\", \"remark\": \"\", \"employee_id\": 8065}], \"multi_executor\": 1}, {\"id\": 6, \"weight\": \"20\", \"employees\": [{\"point\": null, \"title\": \"四级主管评分：赵六(20%)\", \"remark\": \"\", \"employee_id\": 4006}, {\"point\": null, \"title\": \"四级主管评分：张三(20%)\", \"remark\": \"\", \"employee_id\": 4000}], \"multi_executor\": 1}, {\"id\": 7, \"weight\": \"10\", \"employees\": [{\"point\": null, \"title\": \"五级主管评分：李四(10%)\", \"remark\": \"\", \"employee_id\": 4002}, {\"point\": null, \"title\": \"五级主管评分：赵六(10%)\", \"remark\": \"\", \"employee_id\": 4006}], \"multi_executor\": 1}, {\"id\": 8, \"weight\": \"10\", \"employees\": [{\"point\": null, \"title\": \"六级主管评分：8号(10%)\", \"remark\": \"\", \"employee_id\": 8067}, {\"point\": null, \"title\": \"六级主管评分：李四(10%)\", \"remark\": \"\", \"employee_id\": 4002}], \"multi_executor\": 1}], \"point_limit\": \"\", \"result_type\": \"none\", \"reviewer_id\": 0, \"mamage_record\": [], \"result_employee_id\": 0}], \"index_num\": 0, \"index_type\": 4, \"weight_type\": 0, \"target_index\": 1, \"total_weight\": 0, \"dimension_weight\": 100, \"all_dimension_index_weight\": 0}]');


select * from per_package_employee

查询的存储过程

DROP PROCEDURE IF EXISTS test4;

DELIMITER $$

CREATE PROCEDURE test4()
BEGIN

-- pe_id 个人考核记录ID  pe_eid  个人考核记录用户ID temp_length  指标数组的元素个数  i  指标数组的第N个元素 pei_type 遍历过程中的指标类型

declare pe_id,pe_eid,temp_length,i,pei_type int default 0;

-- 个人考核记录 指标json数组
declare pe_index_info text;
-- pei_name, pei_target,pei_unit  遍历过程中，保存指标ID 名称，目标值，单位
declare pei_id,pei_name,pei_target,pei_unit VARCHAR(255);
-- 遍历标识
declare flag int default 0;

-- 声明游标 (游标中的select 查的是dimension 中 的二维数组 index   [{"index":[{"name":"格式简述"},{"name":"格式简述"}]},{"index":[{"name":"格式简述"},{"name":"格式简述"}]}] )
declare mc cursor for (SELECT
		id,employee_id,JSON_EXTRACT(dimension, '$[*].index[*]') as index_info
	FROM
		per_package_employee 
	WHERE
		site_id = 569 
		AND package_id = 1
		AND JSON_SEARCH( dimension, 'one', '%度%', NULL, '$[*].index[*].name' ) IS NOT NULL);
declare continue handler for not found set flag = 1;



-- 创建零时表 MEMORY  速度快
Drop table if exists t_table_index;
CREATE TEMPORARY TABLE t_table_index
(id int not null,
employee_id int not null,
index_id varchar(255),
index_name varchar(255),
index_type int,
index_target varchar(255),
index_unit varchar(200)) ENGINE = MEMORY;


-- 打开游标
open mc;
-- 获取结果
    l:loop  -- 循环，将表的内容都转移到class2中
    fetch mc into pe_id,pe_eid,pe_index_info;
    if flag=1 THEN
       leave l;
    end if;
    
		set temp_length = JSON_LENGTH(pe_index_info);
		set i =0;
		while i<temp_length
		do
			set pei_name = JSON_EXTRACT(pe_index_info, concat("$[",i,"].name"));
			-- 二次正则匹配
			if pei_name REGEXP '度' then
			
			set pei_id = JSON_EXTRACT(pe_index_info, concat("$[",i,"].id"));
			
			set pei_type = JSON_EXTRACT(pe_index_info, concat("$[",i,"].type"));
			set pei_target = JSON_EXTRACT(pe_index_info, concat("$[",i,"].target"));
			set pei_unit = JSON_EXTRACT(pe_index_info, concat("$[",i,"].unit"));
			
			insert into t_table_index (id,employee_id,index_id,index_type,index_name,index_target,index_unit) VALUES (pe_id,pe_eid,pei_id,pei_type,pei_name,pei_target,pei_unit);
			
			end if;
			set i = i+1;
		end while;
    
-- 关闭游标
end loop;
close mc;
select * from t_table_index;

END $$

DELIMITER;


CALL test4()

再度优化方向，最多只查100条数据，超过100条，退出游标遍历 或者将单条插入，改成批量插入