杂七杂八的小小知识~~mysql中将JSON格式数据中某属性值作为查询条件以及mysql的字符串的截取、替换函数使用

最近做的这个账单模块数据列项较多，例如消费金额相关会有资源包抵扣，原始金额，应付金额，现金支付，优惠金额，储值卡抵扣，优惠券抵扣，代金券抵扣，未结清金额等多项数据，为了让数据段更加直观、简洁，我将这些消费相关的列项数据都以JSON格式存入了一个“消费详细”字段里。存进去的大致格式如下图：

但是我在完成功能需求的时候出现了问题

第一个问题：我需要汇总consume_details中paymentAmount（现金支付）的总金额。

按正常的逻辑，我应该用sum(字段名)直接获取总和，但是我看到我现在的数据，一脸懵B......Oh My God！！！

于是我尝试百度"sql语句查询json格式的数据"获取解决方案，没想到还挺顺利，直接看到了这个博客sql语句中对json数据的操作

于是我就通过以下SQL实现了数据获取 

select consume_details -> '$.paymentAmount' from cloud_bill;

但是查询出的数据有些问题，是varchar类型，汇总起来也会有问题，不是我想要的

到这，也就出现了我的第二个问题。

第二个问题：mysql转换查询出的数据类型

了解有CAST、CONVERT函数（函数详解）能够实现我想要的效果，我用了CONVERT

select CONVERT (consume_details -> '$.paymentAmount',DECIMAL (18, 6)) from cloud_bill;

第三个问题：mysql截取字符串

根据需求，我还需要单独查询出consume_details中consumeType（消费类型）并根据它分组。但是我单独从json数据中拿出来中文数据，在navicat运行正常，到java里是乱码的，后来我发现我通过查询消费类型SQL语句获取出来的数据是带双引号的，我猜测可能就是这个双引号搞的鬼。

select consume_details -> '$.consumeType' from cloud_bill;

于是通过mysql函数SUBSTRING完美解决了这个问题，SQL如下：

select SUBSTRING(consume_details -> '$.consumeType',2,3) from cloud_bill;

确实，双引去掉了，乱码问题自动解决。

 

今天还算有些小收获~~~~~开心♪(＾∀＾●)ﾉ