MySQL JSON
JSON的缩写是 JavaScript对象表示法 。它是一种 轻量级的数据交换格式 ,类似于其他数据类型,并且可以轻松地由人们阅读和编写。它也可以轻松地被机器解析和生成。
一般来说,JSON数据类型支持两种结构:
- 一个名称/值对链的集合,作为数据数组。
- 一个有序值列表。
由于它管理作为数据数组的名称/值对链中的单个值,我们可以使用单个命令检索整个字段。这个有用的特性允许我们快速检索大型系统中的数据。
MySQL 从 5.7.8版本 开始支持本机JSON数据类型,它将JSON文档存储在内部格式中,从而实现了快速和高效的读访问文档对象。这种数据类型比我们过去使用的 JSON 文本格式可以更准确地存储JSON文档。
以下是JSON数据类型相对于存储JSON格式字符串的优势:
- JSON列允许我们存储JSON文档的自动验证。否则,我们将会收到一个错误。
- 优化/快速存储格式意味着当服务器读取存储在二进制格式中的JSON值时,它不需要从文本表示中解析。二进制格式允许直接使用键或数组索引搜索JSON文档中的值,而无需读取整个值。
存储JSON文档所需的存储空间大致与 LONGBLOB 和 LONGTEXT 的存储要求相同。
我们可以使用以下索引在MySQL表中定义JSON数据类型列:
CREATE TABLE table_name (
...
json_column_name JSON,
...
);
注意:需要注意的是,我们不能在JSON列中存储非空默认值。此外,JSON列不能直接进行索引,因为它通过从JSON列中提取标量值来创建索引。如果我们想从JSON列中检索数据,MySQL优化器将搜索与JSON表达式匹配的兼容索引。
为什么我们使用JSON?
我们将在MySQL中使用JSON数据类型是因为它的 用例 ,在这些用例中,我们可以使用一种临时方法。让我们通过一个例子来理解它。
假设我们正在创建一个Web应用程序,并希望将用户的配置或偏好保存在表中。通常,我们习惯创建一个包含user_id、key和value字段的单独表,或者将其保存为格式化字符串,以便在运行时进行解析。这种方法对于有限的用户来说是不错的。如果用户列表很大并且有更多的配置/偏好键,这种方法就不好了。
为了解决这些问题,MySQL允许我们使用JSON数据类型字段来存储用户的配置或偏好,这样可以节省表的空间并将记录分开存储,与访问网站的访问者数量相同。
MySQL JSON数据类型示例
假设我们想跟踪访问我们网站的用户及其操作,例如某些用户只查看页面,其他用户会访问页面并购买产品。让我们使用以下语句创建一个名为“ 事件 ”的新表来存储这些信息:
CREATE TABLE events(
event_id INT AUTO_INCREMENT PRIMARY KEY,
event_name varchar(75),
visitors varchar(25),
properties json,
browser_name json
);
Event id用于在事件表中唯一标识每个事件。事件名存储了事件的名称,例如页面查看、购买等等。访客列存储了访问网站的用户信息,而属性和浏览器名称列用于保存JSON值。浏览器名称列存储了访问者用于浏览网站的浏览器规格,例如浏览器名称、操作系统、分辨率等等。
接下来,我们将使用下面给出的INSERT语句将数据插入到该表中:
INSERT INTO events (event_name, visitors, properties, browser_name)
VALUES (
'page-view',
'2',
'{"page": "/"}',
'{ "name": "Google Chrome", "OS": "Windows", "resolution": {"x": 1920, "y": 1080} }'
),
('page-view',
'3',
'{"page": "/products"}',
'{ "name": "Safari", "OS": "UNIX", "resolution": {"x": 2560, "y": 1600} }'
),
(
'page-view',
'1',
'{"page": "/contacts"}',
'{ "name": "Mozilla Firefox", "OS": "Mac", "resolution": {"x": 1920, "y": 1080} }'
),
(
'purchase',
'4',
'{"amount": 250}',
'{ "name": "Google Chrome", "OS": "Windows", "resolution": {"x": 1280, "y": 800} }'
),
(
'purchase',
'3',
'{"amount": 350}',
'{ "name": "Safari", "OS": "Mac", "resolution": {"x": 1600, "y": 900} }'
),
(
'purchase',
'4',
'{"amount": 400}',
'{ "name": "Mozilla Firefox", "OS": "Windows", "resolution": {"x": 1280, "y": 800} }'
);
现在,我们可以验证表格,执行如下所示的 SELECT语句
mysql> SELECT * FROM events;
我们将看到以下输出结果:
假设我们想从JSON列中检索任何特定值,比如浏览器名称。我们可以使用列 **路径运算符(- >) ** 过滤这些类型的结果。请参见下面的查询:
mysql> SELECT event_id, browser_name->'$.name' AS browser FROM events;
它将返回以下输出:
在这个图片中,你会注意到浏览器列的数据被双引号包围。如果你想要获取浏览器列的数据且不包含任何引号,我们需要使用内联路径运算符(->>)如下所示: **inline path operator (- >>) **
mysql> SELECT event_id, browser_name->>'$.name' AS browser FROM events;
在下面的输出中,我们可以看到引号已成功移除。
如果我们需要检索浏览器的使用情况,可以使用以下语句:
mysql> SELECT browser_name->>'.name' AS browser, count(browser_name)
FROM events GROUP BY browser_name->>'.name';
我们将获得以下结果:
如果我们想要 计算访客的总收入 ,我们可以使用以下查询:
mysql> SELECT visitors, SUM(properties->>'.amount') As total_revenue
FROM events WHERE properties->>'.amount' > 0 GROUP BY visitors;
我们将得到如下结果: