注意: 此用法仅支持 MySQL 8.0 以上的版本
我们假设一个场景,字段中存储一个日期的 JSON,这个 JSON 的 Key 为星期的英文名称,具体 JSON 格式如下:
{
"monday": 390,
"Tuesday": 240,
"Wednesday": 150,
"Thursday": 160,
"Friday": 150,
"Saturday": 270,
"Sunday": 180
}
我们需要做的是对这些值进行取和,而 MySQL自身并不支持 JSON 值的 聚合运算,但是我们可以通过 MySQL8 的新函数 JSON_TABLE
来的实现这个需求。
具体写法如下:
第一步,我们先拿到 JSON 的 VALUE 值
select * from JSON_TABLE('{"monday":390,"Tuesday":240,"Wednesday":150,"Thursday":160,"Friday":150,"Saturday":270,"Sunday":180}',"$.*" COLUMNS(
rowid FOR ORDINALITY,
price int PATH "$"
)) as temp
-- 查询结果如下,我们可以获取到所有的VALUE
+-------+
| price |
+-------+
| 150 |
| 180 |
| 390 |
| 240 |
| 270 |
| 160 |
| 150 |
+-------+
7 rows in set (0.03 sec)
第二步,上面 SQL 的基础上 使用聚合函数 SUM()
来获取所有值的和:
select SUM(price) from JSON_TABLE('{"monday":390,"Tuesday":240,"Wednesday":150,"Thursday":160,"Friday":150,"Saturday":270,"Sunday":180}',"$.*" COLUMNS(
rowid FOR ORDINALITY,
price int PATH "$"
)) as temp
-- 聚合值输出结果如下:
+------------+
| SUM(price) |
+------------+
| 1540 |
+------------+
这样我们就可以拿到一个 JSON 格式的聚合结果,实际应用我们可以结合以下方案:
- 将聚合JSON 的 SQL 写成一个 自定义函数,在MySQL 使用的时候直接传入 JSON 存储字段来简化查询
- 如果需要对 JSON 结果进行查询,那么 使用JSON 函数的效率是极低的,可以参考我博客的上一篇文章,自定义函数结合 MySQL 的虚拟列来提高查询速度
CC版权: 本篇博文采用《CC 协议》,转载必须注明作者和本文链接