mysql中json欄位查詢詳解

MySQL提供了一種新特性——JSON類型。相比於傳統的關係型資料庫設計，使用JSON更加靈活、方便，是Web和移動應用程序的理想數據存儲格式。本文將從多個方面對mysqljson欄位查詢做詳細的闡述。

一、mysqljson欄位查詢基礎

JSON類型是MySQL 5.7版本中引入的，主要是為了解決傳統關係型資料庫難以存儲半結構化數據的問題。JSON欄位類型支持讀取和修改JSON文檔中的數據，如果JSON格式不正確，MySQL會自動檢測並返回錯誤。

JSON類型的欄位可以使用標準的MySQL查詢語句進行處理，包括SELECT、UPDATE、DELETE、JOIN和WHERE子句等等。JSON類型數據中的每一個鍵值對都可以作為查詢條件。

下面舉個例子，假設有個表名為users，其中有個名為info_json的JSON欄位。我們需要根據JSON欄位中的age屬性查詢：

SELECT * FROM users WHERE info_json->'$.age'=25;

通過 -> 操作符可以獲取json對象中某個key的值，’$’表示查詢整個json

如果想根據JSON中的數組查詢，則可以使用 JSON_SEARCH() 函數：

SELECT JSON_SEARCH(info_json, 'one', 'green') FROM users;

JSON_SEARCH() 函數會返回數組中滿足條件的鍵的路徑。

二、mysqljson欄位的查詢操作

1、插入JSON對象

在mysql中，可以使用 JSON_OBJECT() 函數創建json對象，並將其插入到表中：

INSERT INTO users (info_json) VALUES (
    JSON_OBJECT(
        'name', 'Tom', 
        'age', 20, 
        'hometown', JSON_OBJECT('province', 'Guangdong', 'city', 'Shenzhen')
    )
);

還可以通過 JSON_ARRAY()函數直接插入JSON數組值：

INSERT INTO employees (info_json) VALUES (JSON_ARRAY('John', 'Doe', '20/01/1980'));

2、查詢JSON對象里的屬性

查詢JSON對象里的屬性可以使用 -> 操作符。例如，我們想要查詢info_json中name屬性的值，可以使用以下命令：

SELECT info_json->'$.name' FROM users WHERE id=10;

如果屬性是嵌套在另一個屬性中，則可以使用 ->> 操作符：

SELECT info_json->'$.hometown->>$.city' FROM users WHERE id=10;

它會將屬性的值作為結果返回。

3、查詢JSON數組裡的元素

查詢JSON數組裡的元素可以使用 JSON_EXTRACT() 函數。例如，我們要查詢 employees 表中第二個元素：

SELECT JSON_EXTRACT(info_json, '$[1]') FROM employees;

它將會返回 JSON 數組中的第二個元素

4、使用WHERE子句過濾JSON屬性或數組元素

可以使用WHERE子句過濾JSON屬性或數組元素，比如:

SELECT info_json->"$.type" as type, info_json->"$.value" as value 
FROM mytable 
WHERE info_json->"$.type" = "email"

MySQL 還提供了一些內置函數可以方便使用 JSON 數據類型：

• JSON_CONTAINS(): 判斷是否包含指定的 JSON 數據
• JSON_MERGE(): 合併多個 JSON 數據
• JSON_OBJECTAGG(): 聚合組成 JSON 對象

三、mysqljson欄位的高級應用

1、JSON搜索引擎

MySQL還有一項高級功能——JSON搜索引擎（JSON Search Engine）

為了加快搜索速度，MySQL使用了基於搜索樹的快速查找方式。這種方式可以根據給定的約束條件查找符合條件的JSON對象，然後保留JSON對象中的符合條件的搜索結果。

這種搜索引擎使用了一種結合了B樹和B+樹的演算法來存儲索引文件。這種演算法既可以使用二分查找獲取數據，又可以使用B樹來獲取數據。

SELECT * FROM user_info 
  WHERE MATCH(userInfoCmt)
  AGAINST('+java +php' IN BOOLEAN MODE);

2、JSON數據轉換為MySQL的行

有時候，我們希望JSON數據可以轉換成MySQL的行，這樣就可以使用標準的SQL查詢。MySQL提供了一個叫做generated的列類型，使得這樣的轉化變得簡單。

CREATE TABLE mytable (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                       json_col JSON NOT NULL,
                       generated_col VARCHAR(255)
                       GENERATED ALWAYS AS (JSON_EXTRACT(json_col, '$.name')) VIRTUAL);

因此，在查詢mytable時，就可以使用SELECT來查詢數據，並且可以像標準列一樣使用下面的WHERE子句：

SELECT * FROM mytable WHERE generated_col = 'test_value';

3、使用JSON數組可重複的key

JSON數組是一種靈活的數據類型，可以包含重複的鍵。當需要在JSON數組中存儲一組鍵值對時，可以使用下面的語法：

[
   {"key1": value1, key2: value2},
   {"key1": anotherValue1, key2: anotherValue2}
]

這種表示方法有時候可以在爬蟲中非常有用，因為可以使用JSON_STORAGE_FREE_FORM或者VIRTUAL列來處理JSON中的重複鍵：

CREATE TABLE my_table ( 
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data JSON NOT NULL,
  PRIMARY KEY(id)
);

SELECT data->"$.[*].key" AS key,
       data->"$.[*].value" AS value
FROM my_table
WHERE JSON_CONTAINS(JSON_KEYS(data), 'key');

四、總結

本文對mysqljson欄位查詢進行了詳細的闡述，包括基礎操作、高級使用，以及使用JSON搜索引擎、使用JSON數組可重複的key等。JSON類型是mysql資料庫一個強大的新特性，在存儲非結構化數據方面表現十分優異，我們希望這篇文章對你有所幫助。