1. 首页
  2. 文章中心
  3. SQL语句基础

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

72 阅读 0 评论
我是靠谱客的博主 曾经热狗，最近开发中收集的这篇文章主要介绍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条，退出游标遍历 或者将单条插入，改成批量插入

最后

以上就是曾经热狗为你收集整理的mysql，二维json数组查询，并再次组装新表，存储过程，临时表，内存表，遍历JSON数组，遍历SELECT的结果的全部内容，希望文章能够帮你解决mysql，二维json数组查询，并再次组装新表，存储过程，临时表，内存表，遍历JSON数组，遍历SELECT的结果所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(48)

相关文章

mysql 存储过程 数组参数_mysql 存储过程 实现参数没有数组
mysql 存储过程 数组参数_mysql 存储过程 实现参数没有数组
MySql存储过程拆分JSON数组插入子表2、思路考处理方式及方式的优缺点3、最终解决方案
MySql存储过程拆分JSON数组插入子表2、思路考处理方式及方式的优缺点3、最终解决方案
c++ substr函数出现错误
c++ substr函数出现错误
mysql存储过程中遍历数组字符串的两种方式
mysql存储过程中遍历数组字符串的两种方式
mysql，二维json数组查询，并再次组装新表，存储过程，临时表，内存表，遍历JSON数组，遍历SELECT的结果
mysql，二维json数组查询，并再次组装新表，存储过程，临时表，内存表，遍历JSON数组，遍历SELECT的结果
mysql json 数据解析
mysql json 数据解析
mysql创建存储过程及遍历查询结果,存储过程创建及使用 & 遍历查询结果
mysql创建存储过程及遍历查询结果,存储过程创建及使用 & 遍历查询结果
mysql 存储过程参数数组的处理方式
mysql 存储过程参数数组的处理方式

评论列表共有 0 条评论

立即
投稿
返回
顶部