MySQL反向查找、从字符串中截取非固定长度子串

背景：从一个比较规范的长字符串中截取想要的子串。对于不同的记录，该属性内容不完全相同，字段内容类似于以下。
目标：提取字段中村集体收益、农户方收益、合作社收益、托管方收益金额

[
+["贷款还款"+"XXXX专业合作社",+"2500000"]
+["村集体收益"+"村集体账户",+"0"],
+["农户方收益",+"XXXX专业合作社",+"-100.00"],
+["合作社收益"+"XXXX专业合作社",+"202134.00"],
+["垫付还款",+"XXXX专业合作社",+"0"],
+["托管方收益"+"XXXXX专业合作社",+"808536.00"],
+["垫付还款"+"XXXXX专业合作社",+"0"]
]

 字段内容分析与思路：
想要提取目标内容，只要找到对应描述之后的金额数据就可以。目标字段内容相对规范，每一项数据都用方括号[]括起来了，采用子串定位+截取的方式。
以合作社收益为例，在目标字段中，
①先找到子串“合作社收益”的起始位置，instr(items, '合作社收益')
②找到“合作社收益”位置后，从该位置往后查找定位'"]' 的位置，即该项目的金额的结束位置，使用 locate 函数，locate('"]', items, instr(items, '合作社收益'))
locate(substr, str) : 返回字符串substr在 字符串身str 中第一次出现的位置。
locate(substr, str, pos) : 返回字符串substr 在str中第一次出现的位置，从第pos位置开始。
③两个位置定位之后，截取中间的数据。
substr(items, instr(items, '合作社收益'), locate('"]', items, instr(items, '合作社收益')) - instr(items, '合作社收益')) as str_farmer
④由于金额长度不定，需要确定金额的长度，可以从 '"]' 的位置往前查找第一个 '"' 字符
⑤由于MySQL不支持从后往前查找，可以将字符串 翻转，从前往后查找第一个 '"' 字符，即金额的长度，使用 reverse 函数
⑥最后从后往前截取金额长度的数据即可 使用right 函数

-- 测试数据创建:
drop table if exists tmp_test_substr;
create table tmp_test_substr
(id      int
,items   varchar(255)
);

insert into tmp_test_substr values
(1,
'[
+["贷款还款"+"XXXX专业合作社",+"2500000"]
+["村集体收益"+"村集体账户",+"0"],
+["农户方收益",+"XXXX专业合作社",+"-100.00"],
+["合作社收益"+"XXXX专业合作社",+"202134.00"],
+["垫付还款",+"XXXX专业合作社",+"0"],
+["托管方收益"+"XXXXX专业合作社",+"808536.00"],
+["垫付还款"+"XXXXX专业合作社",+"0"]
]' )
;
commit;

-- sql 分析与查询结果：

 select id
        ,items
        ,items_instr_str
        ,items_instr_end
        ,str_farmer
        ,rev_farmer
        ,instr(rev_farmer,    '"')-1  as len_balance                 -- 查找'"'第一次出现的位置 -1 ( 效果等同反向查找'"'
        ,right(str_farmer, instr(rev_farmer, '"')-1  ) tag_str-- 从右侧截取金额

   from ( select id
                 ,items
                 ,instr(items, '合作社收益') as items_instr_str-- 查找字符串"合作社收益"起始位置
                 ,locate('"]', items, instr(items, '合作社收益')) as items_instr_end -- 从"合作社收益"位置之后查找 '"]',表示"合作社收益"数据的结束位置
                 ,substr(items, instr(items, '合作社收益'), locate('"]', items, instr(items, '合作社收益')) - instr(items, '合作社收益')) as str_farmer -- 截取"合作社收益"数据
                 ,reverse(substr(items, instr(items, '合作社收益'), locate('"]', items, instr(items, '合作社收益')) - instr(items, '合作社收益')) ) as rev_farmer -- 翻转目标字符串获取金额长度
            from tmp_test_substr
        ) a

目标结果: