mysql json处理

 2016-11-18

由于我们的爬虫开发偷懒，爬取回来的数据还是保持为json格式，所以需要进一步处理，从json格式的info字段中，提取出需要的信息作为新字段。

MySQL 从5.7版本开始，已经对原生json格式提供支持，由于目前线上主流的版本还是停留在5.6，所以这时需要人工去处理json格式的数据。

原始表示例数据如下：

取info字段中的一条记录为例：

{"title":"你不知道就out了，奥迪Coupe车型的第一次出现！","url":"http://mp.weixin.qq.com/s?__biz=MjM5NjM4NTAwMQ==&mid=2651782672&idx=1&sn=154dae8ec1c02403bab41c6f2c1ad679&chksm=bd1047968a67ce8086b9d8e2111ca33f49e74a44a24c9f05afac48c75e51147268573957eef9&scene=4#wechat_redirect","datetime":"2016-10-20 17:36"}

需要将其中的title、url、datetime提取出来。以下是处理所用到的sql:

SELECT
CURDATE() AS sys_date,
b.b_id AS id,
c.brand AS brand,
b.b_name AS brand_cn,
a.wxid AS wechat_id,
b.`name` AS wechat_cn,
SUBSTRING_INDEX(SUBSTRING_INDEX(a.info,'"',12),'"',-1)  AS post_date,
SUBSTRING_INDEX(SUBSTRING_INDEX(a.info,'"',4),'"',-1) AS post_title,
SUBSTRING_INDEX(SUBSTRING_INDEX(a.info,'&mid=',-1),'&idx',1) AS post_id,
a.sn AS post_sn,
SUBSTRING_INDEX(SUBSTRING_INDEX(a.info,'"',8),'"',-1) AS post_url,
SUBSTRING_INDEX(SUBSTRING_INDEX(a.ext,':',-2),',',1)  AS page_views,
SUBSTRING_INDEX(SUBSTRING_INDEX(a.ext,':',-1),'}',1)  AS likes,
1 / 0 AS notes
FROM
    11_cmnc.message a JOIN 11_cmnc.subscription b JOIN 11_cmnc.wechat_conf c
ON a.wxid = b.wxid
AND b.b_id = c.id
GROUP BY b.b_id,a.sn
ORDER BY id,post_date;

生成的数据如下：

到这里，title、url、datetime已经从message表的info字段中提取出来了