最近剛好要撈出某一台 DB 特別的值
但那個欄位都是 JSON 格式
剛好那台 DB 是 MySQL 5.7.x
可以用 JSON，就趁個這個機會做筆記
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
-- example
SELECT log->'$.uid' AS uid FROM xxx where log->'$.uid' IN (
xxxx
)and time BETWEEN '2019-07-09 00:00:00' and '2019-07-09 23:59:59'
-- 暫時撈資料跑報表
SELECT type,pl_id,count(1)
FROM (
SELECT log->'$.uid' as uid,'2' as type,log->'$.playtrackId' AS pl_id, time FROM log_table where log->'$.uid' = 'xxxxxx' and time between '2019-07-09 00:00:00' and '2019-07-09 23:59:59'
UNION ALL
SELECT log->'$.uid' AS uid,'2' as type,log->'$.playtrackId' AS pl_id, time FROM log_table where log->'$.uid' = 'oooooo' and time between '2019-07-09 00:00:00' and '2019-07-09 23:59:59'
UNION ALL
SELECT log->'$.uid' AS uid,'2' as type,log->'$.playtrackId' AS pl_id, time FROM log_table where log->'$.uid' = 'aaaaaa' and time between '2019-07-09 00:00:00' and '2019-07-09 23:59:59'
UNION ALL
SELECT log->'$.uid' AS uid,'2' as type,log->'$.playtrackId' AS pl_id, time FROM log_table where log->'$.uid' = 'zzzzzz' and time between '2019-07-09 00:00:00' and '2019-07-09 23:59:59'
) aa
group by type,pl_id
|
最後發現 JSON 字串前後會帶"
可以用JSON_UNQUOTE
MariaDB 也可以使用，不過我目前沒有用到
下方聯結有更詳細介紹
MySQL 5.7 新特性 JSON 的创建，插入，查询，更新 備份圖
MariaDB 10.0.X中，动态列支持 JSON 格式来获取数据。 -贺春旸的技术博客-51CTO博客 備份圖
之後還有用到實用的方法再補充
