mysql解析json字符串相关问题
很多时候，我们需要在sql里面直接解析json字符串。这里针对mysql5.7版本的分水岭进行区分。
1.对于mysql5.7以上版本
使用mysql的内置函数JSON_EXTRACT(column, '$.key')，这个函数有两个参数，第一个参数column代表json列的列名；第二个参数key代表json字符串中的某一个key。
SELECT JSON_EXTRACT('{"priceTag":"员工/合作关键人","priceDiscount":"90"}', '$.priceDiscount') AS '定价折扣';
对于简单的json字符串肯定是可以解析成功，但是对于嵌套数组的没试过。
2.对于mysql5.7以下版本
只能充分发挥已有函数的功能去截取实现，无论实现方式是存储过程还是简单的sql语句，其原理都是一样的。
第一步，将所有的花括号的闭括号'}'替换成英文逗号','；第二步，获取key的坐标keyIndex和长度keyLength；第三步，获取以key为起点，第一个英文逗号','的坐标symbolIndex；第四步，使用substring截取字符串SUBSTRING(targetJsonStr, keyIndex + keyLength, symbolIndex - keyIndex - keyLength)；第五步，使用replace将双引号'"'替换成空字符串''，完工。
示例：从{"priceTag": "员工/合作关键人","priceDiscount": "90"}中获取priceDiscount的值。
SELECT
REPLACE(
-- SUBSTRING(s，n，len)
-- 带有 len 参数的格式，从字符串 s 返回一个长度同 len 字符相同的子字符串，起始于位置 n。
SUBSTRING(
REPLACE(
'{"priceTag":"员工/合作关键人","priceDiscount":"90"}' ,
'}' ,
','
) , -- s 将初始字段中的有括号替换成','号
-- LOCATE(substr,str)
-- 返回字符串substr在字符串str中第一次出现的位置从1开始计数 。
LOCATE(
'priceDiscount":' ,
REPLACE(
'{"priceTag":"员工/合作关键人","priceDiscount":"90"}' ,
'}' ,
','
)
) + CHAR_LENGTH('priceDiscount":') ,-- n 起始位置
LOCATE(
',' ,
REPLACE(
'{"priceTag":"员工/合作关键人","priceDiscount":"90"}' ,
'}' ,
','
) ,
LOCATE(
'priceDiscount":' ,
REPLACE(
'{"priceTag":"员工/合作关键人","priceDiscount":"90"}' ,
'}' ,
','
)
) + CHAR_LENGTH('priceDiscount":') -- n后的第一个','号在 s 中所在的位置
) -(
LOCATE(
'priceDiscount":' ,
REPLACE(
'{"priceTag":"员工/合作关键人","priceDiscount":"90"}' ,
'}' ,
','
)
) + CHAR_LENGTH('priceDiscount":')
) -- 计算出了key值对应的value值的长度
) ,
'"' ,
''
) AS '定价折扣';
文字描述：
1.先将原始字符串的右'}' 替换成 ','
2.计算出key值在步骤1中的位置，当做字段截取的起始位置
3.计算出key值右边最近的一个','号在步骤1中的位置 - 步骤2的位置 = key值对应的value值即字段截取长度
4.截取value值两边的双引号，即得出value值
参考：https://www.jianshu.com/p/513acedf436d
https://blog.csdn.net/helloxiaozhe/article/details/86571387
相关函数介绍
LOCATE函数
语法 一：
LOCATE(substr,str)
返回字符串substr在字符串str中第一次出现的位置从1开始计数 。
如：
SELECT LOCATE("a","abca")
查询结果：
语法二：
LOCATE(substr,str,pos)
返回字符串substr从pos往后数在字符串str中第一次出现的位置从1开始计数 。
如：
SELECT LOCATE("a","abca",2)
查询结果：
注：如果str、substr中任意一个字段为null则查询结果为null
如果substr在str中不存在则返回0
SUBSTRING函数
MYSQL中获取子串函数 SUBSTRING(s，n，len) 带有 len 参数的格式，从字符串 s 返回一个长度同 len 字符相同的子字符串，起始于位置 n。
也可能对 n 使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的第 n 个字符，即倒数第 n 个字符，而不是字符串的开头位置。
参考：http://c.biancheng.net/mysql/substring.html
CHAR_LENGTH函数
返回函数内字符串的长度
如：
CHAR_LENGTH('priceDiscount":')
查询结果：
踩坑背景：公司使用了MongoDB数据库存放数据，但是我所做的通用统计服务数据又存放在mysql内，所以需要MongoDB的数据往mysql导入，如果是简单的数据倒是没啥，但是这个json数据就有点难搞了。
比如上面的例子：他的key值是固定的，顺序可能也一致。MongoDB里面的数据就不一样了
举几个例子：
{"lightspot":"问题解决效率高","scotoma":"1.未查客户逾期情况2.未核实客户身份"}
{"scotoma":"未咨询汇款人"}
{"lightspot":"核实客户身份；问题处理快"}
{"scotoma":"后台操作不应说出（我看下是哪个客户）","lightspot":"1.规范用语2.问题解决思路清晰"}
顺序不一致，key值不一定全
SELECT
REPLACE(
substr(
REPLACE(ifnull(`b`.`comment`, ''),'}', ','), -- 原始字段
locate('scotoma":', REPLACE(ifnull(`b`.`comment`, ''), '}', ','))+ char_length('scotoma":'), -- 起始位置
IF(locate('scotoma":',
REPLACE(ifnull(`b`.`comment`, ''),
'}',
',')
) <> 0, -- 判定key值是否存在
locate(',',
REPLACE(ifnull(`b`.`comment`, ''), '}', ','),
locate('scotoma":', REPLACE(ifnull(`b`.`comment`, ''), '}', ','))
+ char_length('scotoma":')
) - (locate('scotoma":', REPLACE(ifnull(`b`.`comment`, ''), '}', ',')) + char_length('scotoma":')) -- 有就正常取值获取取值长度
, 0) -- 没有就取长度0
), -- 获取value内容
'"',
''
)
FROM ctr_new_db.cti_quality_judgement b
注：如果要运行我的这段sql只需要将sql中的`b`.`comment`替换成我上面举例的四个字符串中的任意一个即可
文字描述：
1.先将原始字符串的右'}' 替换成 ','
2.计算出key值在步骤1中的位置，当做字段截取的起始位置
3.判定key值在这个json中是否存在，存在则按照之前的计算逻辑获取value值的大小，不存在则取0
4.截取value值两边的双引号，即得出value值
