MySQL JSON支持

一、MySQL JSON支持 介绍

JSON 缩写为JavaScript Object Notation。它是一种类似于其他数据类型的轻量级数据交换格式，可以很容易地被人类读写。它也可以很容易地被机器解析和生成。

一般情况下，JSON数据类型支持两种结构：

• 名称/值对链的集合，充当数据数组。
• 值的有序列表。

由于它管理充当数据数组的名称-值对链中的各个值，因此我们可以使用单个命令检索整个字段。这个有用的功能使我们能够快速检索大型系统中的数据。

MySQL从5.7.8 版本开始提供对原生 JSON 数据类型的支持，该数据类型以内部格式存储 JSON 文档，从而可以快速高效地读取文档对象。这种数据类型可以比JSON更准确地存储 JSON 文档

我们在过去的MySQL 版本中使用的文本格式。

以下是 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 数据类型示例

假设我们想要跟踪访问我们网站的用户及其行为，例如一些用户只看到页面，而其他用户会访问页面并购买产品。让我们创建一个名为“ events ”的新表，它将使用以下语句存储此信息：

CREATE TABLE events(   
  event_id INT AUTO_INCREMENT PRIMARY KEY,   
  event_name varchar(75),   
  visitors varchar(25),   
  properties json,   
  browser_name json  
);  

事件 ID 用于唯一标识事件表中的每个事件。事件名称存储页面浏览、购买等事件的名称。访问者列存储访问网站的用户信息，属性和浏览器名称列用于保存 JSON 值。Browser_name 列存储访问者用来浏览网站的浏览器的规格，例如浏览器名称、操作系统、分辨率等。

接下来，我们将使用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 列中检索任何特定值，例如浏览器名称。我们可以使用列路径运算符 (->) 过滤这些类型的结果。请参阅以下SQL：

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

它将返回以下输出：

在此图中，您会注意到双引号将browser列的数据括起来。如果要获取browser列的数据不带引号，我们需要使用内联路径运算符（->>），如下：

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';  

我们将得到以下结果：

如果我们要计算访问者的总收入，我们可以使用如下SQL：

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

我们将得到如下结果：