简介：在本教程中，您将学习如何使用 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 文档。