登录
首页 >  数据库 >  MySQL

MySQL JSON数据类型实用指南

来源:SegmentFault

时间:2023-01-20 18:14:44 493浏览 收藏

积累知识,胜过积蓄金银!毕竟在##column_title##开发的过程中,会遇到各种各样的问题,往往都是一些细节知识点还没有掌握好而导致的,因此基础知识点的积累是很重要的。下面本文《MySQL JSON数据类型实用指南》,就带大家讲解一下MySQL、JSON知识点,若是你对本文感兴趣,或者是想搞懂其中某个知识点,就请你继续往下看吧~

简介:在本教程中,您将学习如何使用MySQL JSON数据类型, 以及数据库中存储JSON文档。

MySQL JSON数据类型简介

从版本5.7.8开始,MySQL支持本机JSON数据类型。本地JSON数据类型使您比以前版本中的JSON文本格式更有效地存储JSON文档。

MySQL 以内部格式存储 JSON 文档,该格式允许快速读取文档元素。JSON 二进制格式的结构允许服务器直接通过键或数组索引在 JSON 文档中搜索值。

JSON文档的存储与 LONGBLOB 或 LONGTEXT 数据的存储大致相同。

要定义数据类型为JSON的列,请使用以下语法:

CREATE TABLE table_name (
    ...
    json_column_name JSON,
    ... 
);
注意: JSON 列不能具有默认值。此外,JSON 列无法直接建立索引。您可以在生成的虚拟列(Generated Column)上创建索引,该索引包含从 JSON 列提取的值。当您从 JSON 列查询数据时,MySQL 优化器将在与 JSON 表达式匹配的虚拟列上查找兼容索引。

在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。

生成列索引创建方式如下:

CREATE TABLE table_name (
    ...
`names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`json_column_name` ->> '$.name') NOT NULL, 
    ... 
);

MySQL JSON数据类型示例

假设我们必须在网站上跟踪访问者及其行为。一些访问者可能只是查看页面,而其他访问者可能会查看页面并购买产品。为了存储此信息,我们将创建一个名为的新表 events。

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);

事件表中的每个事件都有一个唯一标识事件的 id 标识。事件还具有例如浏览量,购买等字段。visitor 列用于存储访客信息。

properties 与 browser 列是JSON列。它们用于存储事件的属性和访问者用来浏览网站的浏览器属性。

让我们将一些数据插入events表中:

INSERT INTO events(event_name, visitor,properties, browser) 
VALUES (
  'pageview', 
   '1',
   '{ "page": "/" }',
   '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
('pageview', 
  '2',
  '{ "page": "/contact" }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 2560, "y": 1600 } }'
),
(
  'pageview', 
  '1',
  '{ "page": "/products" }',
  '{ "name": "Safari", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'
),
(
  'purchase', 
   '3',
  '{ "amount": 200 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1600, "y": 900 } }'
),
(
  'purchase', 
   '4',
  '{ "amount": 150 }',
  '{ "name": "Firefox", "os": "Windows", "resolution": { "x": 1280, "y": 800 } }'
),
(
  'purchase', 
  '4',
  '{ "amount": 500 }',
  '{ "name": "Chrome", "os": "Windows", "resolution": { "x": 1680, "y": 1050 } }'
);

要从 JSON 列中提取值,请使用列路径运算符(->)。

SELECT id, browser->'$.name' browser
FROM events;

该查询返回以下输出:

+----+---------+
| id | browser   |
+----+---------+
|  1 | "Safari"  |
|  2 | "Firefox" |
|  3 | "Safari"  |
|  4 | "Firefox" |
|  5 | "Firefox" |
|  6 | "Chrome"  |
+----+---------+
6 rows in set (0.00 sec)

请注意,该browser列中的数据用引号引起来。要去除引号,请使用内联路径运算符(->>),如下所示:

SELECT id, browser->>'$.name' browser
FROM events;

如下面的输出所示,引号已删除:

+----+---------+
| id | browser |
+----+---------+
|  1 | Safari  |
|  2 | Firefox |
|  3 | Safari  |
|  4 | Firefox |
|  5 | Firefox |
|  6 | Chrome  |
+----+---------+
6 rows in set (0.00 sec)

要获得浏览器的使用率,可以使用以下语句:

 
SELECT browser->>'$.name' browser, 
      count(browser)
FROM events
GROUP BY browser->>'$.name';

查询的输出如下:

+---------+----------------+
| browser | count(browser) |
+---------+----------------+
| Safari  |              2 |
| Firefox |              3 |
| Chrome  |              1 |
+---------+----------------+
3 rozws in set (0.02 sec)

要计算访问者的总收入,请使用以下查询:

SELECT visitor, SUM(properties->>'$.amount') revenue
FROM events
WHERE properties->>'$.amount' > 0
GROUP BY visitor;

这是输出:

+---------+---------+
| visitor | revenue |
+---------+---------+
| 3       |     200 |
| 4       |     650 |
+---------+---------+
2 rows in set (0.00 sec)

在本教程中,您了解了MySQL JSON数据类型以及如何使用它在数据库中存储JSON文档。

今天关于《MySQL JSON数据类型实用指南》的内容就介绍到这里了,是不是学起来一目了然!想要了解更多关于mysql的内容请关注golang学习网公众号!

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>