最近做的这个账单模块数据列项较多,例如消费金额相关会有资源包抵扣,原始金额,应付金额,现金支付,优惠金额,储值卡抵扣,优惠券抵扣,代金券抵扣,未结清金额等多项数据,为了让数据段更加直观、简洁,我将这些消费相关的列项数据都以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;
确实,双引去掉了,乱码问题自动解决。
今天还算有些小收获~~~~~开心♪(^∀^●)ノ