mysql json 文档_MySQL JSON数据类型实用指南

最新推荐文章于 2024-05-15 10:46:25 发布

滕誊縢

最新推荐文章于 2024-05-15 10:46:25 发布

阅读量239

点赞数

文章标签： mysql json 文档

本文链接：https://blog.csdn.net/weixin_33895652/article/details/113430690

版权

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