MySQL JSON

MySQL JSON

JSON的缩写是 JavaScript对象表示法 。它是一种 轻量级的数据交换格式 ,类似于其他数据类型,并且可以轻松地由人们阅读和编写。它也可以轻松地被机器解析和生成。

一般来说,JSON数据类型支持两种结构:

  • 一个名称/值对链的集合,作为数据数组。
  • 一个有序值列表。

由于它管理作为数据数组的名称/值对链中的单个值,我们可以使用单个命令检索整个字段。这个有用的特性允许我们快速检索大型系统中的数据。

MySQL5.7.8版本 开始支持本机JSON数据类型,它将JSON文档存储在内部格式中,从而实现了快速和高效的读访问文档对象。这种数据类型比我们过去使用的 JSON 文本格式可以更准确地存储JSON文档。

以下是JSON数据类型相对于存储JSON格式字符串的优势:

  • JSON列允许我们存储JSON文档的自动验证。否则,我们将会收到一个错误。
  • 优化/快速存储格式意味着当服务器读取存储在二进制格式中的JSON值时,它不需要从文本表示中解析。二进制格式允许直接使用键或数组索引搜索JSON文档中的值,而无需读取整个值。

存储JSON文档所需的存储空间大致与 LONGBLOBLONGTEXT 的存储要求相同。

我们可以使用以下索引在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;

我们将看到以下输出结果:

MySQL JSON

假设我们想从JSON列中检索任何特定值,比如浏览器名称。我们可以使用列 **路径运算符(- >) ** 过滤这些类型的结果。请参见下面的查询:

mysql> SELECT event_id, browser_name->'$.name' AS browser FROM events;

它将返回以下输出:

MySQL JSON

在这个图片中,你会注意到浏览器列的数据被双引号包围。如果你想要获取浏览器列的数据且不包含任何引号,我们需要使用内联路径运算符(->>)如下所示: **inline path operator (- >>) **

mysql> SELECT event_id, browser_name->>'$.name' AS browser FROM events;

在下面的输出中,我们可以看到引号已成功移除。

MySQL JSON

如果我们需要检索浏览器的使用情况,可以使用以下语句:

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

我们将获得以下结果:

MySQL JSON

如果我们想要 计算访客的总收入 ,我们可以使用以下查询:

mysql> SELECT visitors, SUM(properties->>'.amount') As total_revenue
FROM events WHERE properties->>'.amount' > 0 GROUP BY visitors;

我们将得到如下结果:

MySQL JSON

Camera课程

Python教程

Java教程

Web教程

数据库教程

图形图像教程

办公软件教程

Linux教程

计算机教程

大数据教程

开发工具教程