mysql中某个字段存在json,且json数据过大,严重影响查询效率。
处理办法:将此字段提出来,新建一个字段,将此json中的字段除大文件(类似于图片和音视频的base64格式)都转移,base64字段数据置为空。
将执行结果转移到新的字段,去掉base64字段的冗余。
原数据格式:
(新建字段)
ALTER table `patrol_item_result_copy1` ADD result MEDIUMTEXT COMMENT '执行结果-新';
-- 将patrol_item_result_copy1表的execute_result字段复制到表中的result字段
(先测试100条数据)
update patrol_item_result_copy1 a set a.result = a.execute_result limit 100;
(因为存在base64,json的格式也不规范,先规范下)
UPDATE patrol_item_result_copy1 SET result = REPLACE (result,'\\','');
UPDATE patrol_item_result_copy1 SET result = REPLACE (result,'/','');
UPDATE patrol_item_result_copy1 SET result = REPLACE (result,'"{','{');
UPDATE patrol_item_result_copy1 SET result = REPLACE (result,'}"','}');
这一步处理之后的数据:
(查看json中的转义后的数据,是否正确)
SELECT json_extract(result,'$.result.mes.base64Code') from patrol_item_result_copy1 limit 100;
-- UPDATE patrol_item_result_copy1 SET result = json_remove(result, '$.result.mes.base64Code'); -- 删除base64
UPDATE patrol_item_result_copy1 SET result = json_set(result, '$.result.mes.base64Code',''); -- 将base64内容置为空
最后的处理结果: