MySQL json類型

在本教學中，您將學習如何使用MySQL JSON數據類型將JSON文檔存儲在資料庫中。

MySQL JSON數據類型簡介

自MySQL5.7.8版本以來，MySQL支持原生JSON數據類型。允許使用原生JSON數據類型比以前MySQL版本中所使用JSON文本格式更能有效地存儲JSON文檔。

MySQL以內部格式存儲JSON文檔，允許對文檔元素的快速讀取訪問。JSON二進位格式的結構是允許伺服器通過鍵或數組索引直接搜索JSON文檔中的值，這非常快。

JSON文檔的存儲大約與存儲LONGBLOB或LONGTEXT數據量相同。

要定義數據類型為JSON的列，請使用以下語法：

CREATE TABLE table_name (
    ...
    json_column_name JSON,
    ...
);

請注意，JSON列不能有默認值。 此外，JSON列不能直接編入索引。可以在包含從JSON列中提取的值的生成列上創建索引。當從JSON列查詢數據時，MySQL優化器將在匹配JSON運算式的虛擬列上查找相容的索引。

MySQL JSON數據類型示例

假設跟蹤訪客在網站上的行為。 一些訪問者可能只是查看頁面，而其他訪問者可能會查看頁面並購買產品。 要存儲這些資訊，我們將創建一個名為events的新表。

USE testdb;
CREATE TABLE events(
  id int auto_increment primary key,
  event_name varchar(255),
  visitor varchar(255),
  properties json,
  browser json
);

事件表中的每個事件都有一個唯一標識事件的id。事件還有一個event_name列，例如流覽量，購買等。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;

此查詢返回以下輸出：

請注意，上面查詢語句要在命令中連接到MySQL伺服器(mysql -hlocalhost -uroot -p)。可以看到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 rows 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文檔。