MySQL JSON 数据类型实用指南（例子）

摘要：在本教程中，您将学习如何使用 MySQL JSON 数据类型在数据库中存储 JSON 文档。

MySQL JSON数据类型简介

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

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

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

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

CREATE TABLE table_name (
    ...
    json_column_name JSON,
    ... 
);
Code language: SQL (Structured Query Language) (sql)

请注意，JSON 列不能有默认值。此外，JSON 列无法直接索引。相反，您可以在包含从 JSON 列提取的值的生成列上创建索引。当您从 JSON 列查询数据时，MySQL 优化器将在与 JSON 表达式匹配的虚拟列上查找兼容的索引。

MySQL JSON 数据类型示例

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

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
Code language: SQL (Structured Query Language) (sql)

事件表中的每个事件都有一个唯一标识该事件的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 } }'
);
Code language: SQL (Structured Query Language) (sql)

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

SELECT id, browser->'$.name' browser
FROM events;
Code language: SQL (Structured Query Language) (sql)

此查询返回以下输出：

+----+-----------+
| id | browser   |
+----+-----------+
|  1 | "Safari"  |
|  2 | "Firefox" |
|  3 | "Safari"  |
|  4 | "Firefox" |
|  5 | "Firefox" |
|  6 | "Chrome"  |
+----+-----------+
6 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

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

SELECT id, browser->>'$.name' browser
FROM events;
Code language: SQL (Structured Query Language) (sql)

正如您在以下输出中看到的，引号已被删除：

+----+---------+
| id | browser |
+----+---------+
|  1 | Safari  |
|  2 | Firefox |
|  3 | Safari  |
|  4 | Firefox |
|  5 | Firefox |
|  6 | Chrome  |
+----+---------+
6 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

要获取浏览器的使用情况，可以使用以下语句：

SELECT browser->>'$.name' browser, 
      count(browser)
FROM events
GROUP BY browser->>'$.name';
Code language: SQL (Structured Query Language) (sql)

查询的输出如下：

+---------+----------------+
| browser | count(browser) |
+---------+----------------+
| Safari  |              2 |
| Firefox |              3 |
| Chrome  |              1 |
+---------+----------------+
3 rows in set (0.02 sec)
Code language: SQL (Structured Query Language) (sql)

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

SELECT visitor, SUM(properties->>'$.amount') revenue
FROM events
WHERE properties->>'$.amount' > 0
GROUP BY visitor;
Code language: SQL (Structured Query Language) (sql)

这是输出：

+---------+---------+
| visitor | revenue |
+---------+---------+
| 3       |     200 |
| 4       |     650 |
+---------+---------+
2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

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

本教程有帮助吗？