体验 MySQL 8.0 JSON聚合函数

MySQL 最近的动作很快,已经计划推出 8.0 版本,会新增很多新特性

在 5.7 中,JSON 已经被正式支持,但在 SQL 中对 JSON 的处理能力较弱,8.0 中这部分能力会加强,例如新增了这两个JSON聚合函数

JSON_ARRAYAGG()
JSON_OBJECTAGG()

通过JSON聚合函数,可以在 SQL 中直接把数据整合为JSON结构,非常简单

基础用法

创建测试表

CREATE TABLE `t1` (
   `key`  varchar(8) DEFAULT NULL,
   `grp` varchar(8) DEFAULT NULL,
   `val` varchar(8)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

添加测试数据

INSERT INTO t1(`key`, `grp`, `val`) VALUES
("key1", "g1", "v1"),
("key2", "g1", "v2"),
("key3", "g2", "v3");

在查询中使用聚合函数

把字段 ‘key’ 的所有值整合为一个JSON数组

mysql> SELECT JSON_ARRAYAGG(`key`) AS `keys` FROM t1;
+--------------------------+
| keys                     |
+--------------------------+
| ["key1", "key2", "key3"] |
+--------------------------+
1 row in set (0.00 sec)

分组聚合

mysql> SELECT grp, JSON_ARRAYAGG(`key`) AS `keys_grouped` FROM t1  GROUP BY grp;
+------+------------------+
| grp  | keys_grouped     |
+------+------------------+
| g1   | ["key1", "key2"] |
| g2   | ["key3"]         |
+------+------------------+
2 rows in set (0.00 sec)

mysql> SELECT grp, JSON_OBJECTAGG(`key`, val) AS `key_val_grouped` FROM t1 GROUP BY grp;
+------+------------------------------+
| grp  | key_val_grouped              |
+------+------------------------------+
| g1   | {"key1": "v1", "key2": "v2"} |
| g2   | {"key3": "v3"}               |
+------+------------------------------+
2 rows in set (0.00 sec)

把某两列的值整合为JSON对象

mysql> SELECT JSON_OBJECTAGG(`key`, val) AS `key_val` FROM t1;
+------------------------+
| key_val                |
+------------------------+
| {
"key1": "v1", 
"key2": "v2", 
"key3": "v3"
} |
+------------------------+
1 row in set (0.00 sec)

场景示例

描述

例如一个产品表,其中包含产品的通用属性(名称、价格...)

产品还有自己的单独属性,例如 电脑会包含 cpu/内存 等型号、衣服会包含 颜色、材质 等

引申出另外两张表:扩展属性表、属性值表

需求

查询出产品的所有信息,包括所有属性及其值,并整合为JSON结构

实现

建表

// 产品表
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) DEFAULT NULL,
  `manufacturer` varchar(120) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

// 扩展属性表
CREATE TABLE `attribute` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) DEFAULT NULL,
  `description` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

// 属性值表
CREATE TABLE `value` (
  `prod_id` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL,
  `value` text,
  PRIMARY KEY (`prod_id`,`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

添加测试数据

// 插入一些属性
INSERT INTO attribute(id, name) VALUES
(1, "color"),
(2, "material"),
(3, "style"),
(4, "bulb_type"),
(5, "usage"),
(6, "cpu_type"),
(7, "cpu_speed"),
(8, "weight"),
(9, "battery_life"),
(10, "fuel_type");
// 插入一个产品:台灯
INSERT INTO product(id, name, manufacturer, price) VALUES
(1, "LED Desk Lamp", "X", 26);
// 插入台灯对应的属性值
INSERT INTO value VALUES
(1, 1, "black"),
(1, 2, "plastic"),
(1, 3, "classic"),
(1, 4, "LED"),
(1, 5, "Indoor use only");
// 插入一个产品:电脑
INSERT INTO product(id, name, manufacturer, price) VALUES
(2, "Laptop", "Y", 800);
// 插入电脑对应的属性值
INSERT INTO value VALUES
(2, 1, "blue"),
(2, 6, "quad core"),
(2, 7, "3400 mhz"),
(2, 8, "2,1 kg"),
(2, 9, "9h");
// 插入一个产品:烧烤架
INSERT INTO product(id, name, manufacturer, price) VALUES
(3, "Grill", "Z", 300);
// 插入对应属性值
INSERT INTO value VALUES
(3, 1, "black"),
(3, 8, "5 kg"),
(3, 10, "gas");

查询

// 关联3张表,按产品ID分组
// 把查询结果聚合为JSON对象
SELECT
JSON_OBJECT("key", p.id,
            "title", p.name,
            "manufacturer", p.manufacturer,
            "price", p.price,
            "specifications", JSON_OBJECTAGG(a.name, v.value)) as product
FROM 
product as p JOIN value as v 
    ON p.id=v.prod_id 
JOIN attribute as a 
    ON a.id=v.attribute_id
GROUP BY v.prod_id;

结果示例

{
  "key": 1,
  "price": 26,
  "title": "LED Desk Lamp",
  "manufacturer": "X",
  "specifications": {
    "color": "black",
    "style": "classic",
    "usage": "Indoor use only",
    "material": "plastic",
    "bulb_type": "LED"
  }
} 
......

小结

本文整理自:

http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions

MySQL 8 实验版本下载地址:

https://labs.mysql.com/

(选择 MySQL Server 8.0.0 Optimizer)

原文发布于微信公众号 - 性能与架构(yogoup)

原文发表时间:2016-10-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Netkiller

数据库记录安全解决方案

目录 1. 什么是防删除,防撰改 2. 为什么要做防删除,防撰改限制 3. 何时做防删除,防撰改限制 4. 在哪里做防删除,防撰改限制 5. 谁去做防删除,防撰...

3005
来自专栏Java帮帮-微信公众号-技术文章全总结

MySQL全部知识点(2)

6 聚合函数 聚合函数是用来做纵向运算的函数: l COUNT():统计指定列不为NULL的记录行数; l MAX():计算指定列的最大值,如果指定列是字符串类...

3517
来自专栏撸码那些事

MySQL——索引基础

本篇文章,我们将从索引基础开始,介绍什么是索引以及索引的几种类型,然后学习如何创建索引以及索引设计的基本原则。

893
来自专栏一个爱吃西瓜的程序员

学习SQL【5】-数据更新

数据的更新处理大体可以分为插入(INSERT)、删除(DELETE)和更新(UPDATE)三类,此外,还会给大家介绍数据库中用来管理数据更新的重要概念—事务。 ...

3707
来自专栏吴生的专栏

MySQL的索引是什么?怎么优化?

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,...

39713
来自专栏Spark学习技巧

MySQL的索引是什么?怎么优化?

索引对大数据的查询速度的提升是非常大的,Explain可以帮你分析SQL语句是否用到相关索引。 索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库...

3886
来自专栏程序员历小冰

MySQL探秘(七):InnoDB行锁算法

 在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

1841
来自专栏杨建荣的学习笔记

MySQL和Oracle中唯一性索引的差别(r12笔记第83天)

今天在修复MySQL数据的时候,发现一个看起来“奇怪”的问题。 有一个表里存在一个唯一性索引,这个索引包含3个列,这个唯一性索引的意义就是通过这3个列...

3586
来自专栏林欣哲

MySQL执行计划(explain)分析

这里的索引有auditstatus和productid,可以建立联合索引。但是哪个放左边就要计算区分度。

1544
来自专栏Pythonista

mysql索引提高查询速度

  在web开发中,业务模版,业务逻辑(包括缓存、连接池)和数据库这三个部分,数据库在其中负责执行SQL查询并返回查询结果,是影响网站速度最重要的性能瓶颈。本文...

1373

扫码关注云+社区

领取腾讯云代金券