MySQL json型別 - tw511教學網

在本教學中，您將學習如何使用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文件。