解鎖Mysql中的JSON數據類型，怎一個爽字了得

引言在實際業務開發中，隨著業務的變化，數據的複雜性和多樣性不斷增加。傳統的關係型資料庫模型在這種情況下會顯得受限，因為它們需要預先定義嚴格的數據模式，並且通常只能存儲具有相同結構的數據。而面對非結構化或半結構化數據的存儲和處理需求，選擇使用非關係型資料庫或者創建子表存儲這些變化的結構可能會變得複雜 ...

引言

在實際業務開發中，隨著業務的變化，數據的複雜性和多樣性不斷增加。傳統的關係型資料庫模型在這種情況下會顯得受限，因為它們需要預先定義嚴格的數據模式，並且通常只能存儲具有相同結構的數據。而面對非結構化或半結構化數據的存儲和處理需求，選擇使用非關係型資料庫或者創建子表存儲這些變化的結構可能會變得複雜。在這種情況下，我們可以利用MySQL的JSON欄位類型來解決這個問題。JSON欄位提供了靈活的數據存儲方式，能夠輕鬆應對數據結構的變化和動態性，從而更好地滿足業務需求。

MySQL5.7.8版本引入了JSON數據類型，允許在資料庫表中存儲和操作符合JSON格式的數據。這種原生支持JSON（JavaScript對象表示法）的數據類型遵循RFC 7159標準，提供了有效存儲複雜、半結構化數據的方式。MySQL8.0版本全面支持JSON數據類型，標志著MySQL在處理非結構化數據方面取得了顯著進展。除了能夠高效存儲JSON文檔外，MySQL8.0的JSON數據類型還引入了強大的內置函數，極大地提升了操作和查詢JSON數據的效率和便捷性。本文我們以MYSQL 8.0環境研究一下它的JSON類型。

JSON數據類型的特性

二進位存儲
MySQL 8.0採用了一種優化的二進位格式存儲JSON數據，相較於傳統的字元串存儲方式，這種新格式能夠更迅速地讀取和解析JSON文檔。該格式允許伺服器通過鍵或數組索引直接查找子對象或嵌套值，無需事先讀取或解析文檔中的所有值。這不僅降低了存儲空間占用，還提升了查詢性能。JSON列存儲的JSON文檔所需的空間大致與LONGBLOB或LONGTEXT相同。但要註意，存儲在JSON列中的JSON文檔大小受max_allowed_packet系統變數的限制。
自動驗證
當插入或更新包含JSON欄位的記錄時，MySQL會自動驗證所插入的內容是否符合JSON格式規範，確保數據的一致性和完整性。
索引支持
JSON列不直接索引，但可以在JSON欄位中特定路徑上創建索引，例如通過JSON_EXTRACT()函數提取出用於索引的值。此外，MySQL優化器還會尋找與JSON表達式匹配的虛擬列上的相容索引。
部分內容更新
從MySQL 8.0開始，優化器可以執行JSON列的部分、原地更新，而不是完全刪除舊文檔並將新文檔完全寫入列。這種優化可以通過使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()等函數進行更新。
豐富的JSON函數
MySQL提供了一組用於操作JSON值的SQL函數，包括創建、操作和搜索。此外，還有一組用於操作GeoJSON值的空間函數。

JSON操作函數

MySQL提供了一組用於操作JSON值的SQL函數，包括創建、操作和搜索。

1、創建JSON值

JSON_ARRAY
用於創建JSON數組。語法格式：

JSON_ARRAY([val[, val] ...])

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, NOW());
+------------------------------------------------------+
| JSON_ARRAY(1, "abc", NULL, TRUE, NOW())              |
+------------------------------------------------------+
| [1, "abc", null, true, "2024-02-05 03:29:56.000000"] |
+------------------------------------------------------+
1 row in set (0.00 sec)

JSON_OBJECT
用於創建JSON對象。語法格式：

JSON_OBJECT([key, val[, key, val] ...])

 SELECT JSON_OBJECT('name', 'CoderAcademy', 'age', 30) AS person;
+-----------------------------+
| person                      |
+-----------------------------+
| {"age": 30, "name": "CoderAcademy"} |
+-----------------------------+
1 row in set (0.00 sec)

註意，如果傳入的不是合法的JSON格式，則會報錯。

JSON_QUOTE
用於去除JSON字元串中的引號，將一個JSON格式化的字元串轉換為常規的資料庫內可直接使用的字元串。當從JSON文檔中提取出一個原本被雙引號包圍的字元串時，此函數會移除這些引號，從而便於後續對提取出的數據進行進一步的SQL操作或者與其他非JSON欄位進行比較。

mysql> SELECT JSON_QUOTE('CoderAcademy'), JSON_QUOTE('"CoderAcademy"');
+----------------------------+------------------------------+
| JSON_QUOTE('CoderAcademy') | JSON_QUOTE('"CoderAcademy"') |
+----------------------------+------------------------------+
| "CoderAcademy"             | "\"CoderAcademy\""           |
+----------------------------+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_QUOTE('[1, 2, 3]') AS json1 ,JSON_QUOTE('["a", "b","c"]')  AS json2, JSON_QUOTE('{"name":"CoderAcademy", "age": 30}')  AS json3;
+-------------+------------------------+--------------------------------------------+
| json1       | json2                  | json3                                      |
+-------------+------------------------+--------------------------------------------+
| "[1, 2, 3]" | "[\"a\", \"b\",\"c\"]" | "{\"name\":\"CoderAcademy\", \"age\": 30}" |
+-------------+------------------------+--------------------------------------------+
1 row in set (0.00 sec)

CAST(value AS JSON)
在MySQL8.0中，CAST(value AS JSON)函數會嘗試將給定的值轉化為一個有效的JSON格式字元串。語法結構如下：

CAST(value AS JSON)

mysql> SELECT CAST('["apple", "banana", "cherry"]' AS JSON) AS json1, CAST('{"name":"CoderAcademy", "age": 30}' AS JSON ) AS json2;
+-------------------------------+-------------------------------------+
| json1                         | json2                               |
+-------------------------------+-------------------------------------+
| ["apple", "banana", "cherry"] | {"age": 30, "name": "CoderAcademy"} |
+-------------------------------+-------------------------------------+
1 row in set (0.00 sec)

需要註意的是，如果要轉換的值不符合JSON格式規範，則會拋出錯誤。例如：NULL、不合法的JSON字元串或其他非轉換類型的值。

mysql> SELECT cast('NULL' AS json);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.

mysql> SELECT CAST('{"name":"CoderAcademy", "age"}' AS JSON );
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Missing a colon after a name of object member." at position 29.

JSON_TYPE
用於查詢JSON值類型的內置函數。該函數返回一個表示給定JSON值基本類型的字元串。
語法結構：

JSON_TYPE(value)

"NULL"：如果路徑指向的值是JSON null。
"OBJECT"：如果路徑指向的是一個JSON對象（鍵值對集合）。
"ARRAY"：如果路徑指向的是一個JSON數組。
"STRING"：如果路徑指向的是一個JSON字元串。
"NUMBER"：如果路徑指向的是一個JSON數字（包括整數和浮點數）。
"TRUE" 或 "FALSE"：如果路徑指向的是布爾值 true 或 false。

mysql> SELECT JSON_TYPE(NULL) AS json_null, JSON_TYPE('["a", "b","c"]') AS json_array, JSON_TYPE('{"name":"CoderAcademy", "age": 30}') AS json_object;
+-----------+------------+-------------+
| json_null | json_array | json_object |
+-----------+------------+-------------+
| NULL      | ARRAY      | OBJECT      |
+-----------+------------+-------------+
1 row in set (0.00 sec)

2、合併JSON

JSON_MERGE_PRESERVE
用於合併兩個或多個JSON文檔的函數。並保留所有鍵值對。
語法結構：

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') AS json_value;
+----------------------------+
| json_value                 |
+----------------------------+
| ["a", 1, {"key": "value"}] |
+----------------------------+
1 row in set (0.00 sec)

在處理重覆鍵時，如果是合併對象(JOSN_OBJECT)，將value自動包裝為數組，並通過組合值的方式合併數組。

mysql> SELECT JSON_MERGE_PRESERVE('{"name":"CoderAcademy", "age": 30}', '{"name":"CoderAcademy", "age": 35}') AS json_value;
+-------------------------------------------------------------+
| json_value                                                  |
+-------------------------------------------------------------+
| {"age": [30, 35], "name": ["CoderAcademy", "CoderAcademy"]} |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

如果是合併數組(JSON_ARRAY)，將它們的值組合成一個數組，作為結果中該鍵的值。

mysql> SELECT JSON_MERGE_PRESERVE('{"hobbies":["Java", "Mysql"]}', '{"hobbies":["Python", "Mysql"]}') AS json_value;
+---------------------------------------------------+
| json_value                                        |
+---------------------------------------------------+
| {"hobbies": ["Java", "Mysql", "Python", "Mysql"]} |
+---------------------------------------------------+
1 row in set (0.00 sec)

MySQL 8.0.3以後支持

JSON_MERGE_PATCH
用於合併兩個或多個JSON文檔的函數。僅保留最後一個值。
語法格式如下：

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

mysql> SELECT JSON_MERGE_PATCH('["a", 1]', '{"key": "value"}') AS json_value;
+------------------+
| json_value       |
+------------------+
| {"key": "value"} |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_MERGE_PATCH('{"name":"CoderAcademy", "age": 30}', '{"name":"CoderAcademy", "age": 35}') AS json_value;
+-------------------------------------+
| json_value                          |
+-------------------------------------+
| {"age": 35, "name": "CoderAcademy"} |
+-------------------------------------+
1 row in set (0.00 sec)

MySQL 8.0.3以後支持

3、JSON搜索

JSON_CONTAINS
MySQL8.0中引入的用於檢查 JSON 數組或對象是否包含特定值或鍵值對的函數。語法格式如下：

JSON_CONTAINS(json_doc, candidate[, path])

其中json_doc是要檢查的JSON文檔，通常是一個列名或者JSON字元串表達式。candidate是要查找的值。而path（可選）指定在 JSON 文檔中的路徑，用於更精確地定位要檢查的元素，如果沒有指定路徑，則在整個JSON文檔中搜索給定的候選值。
JSON_CONTAINS函數返回1表示包含，返回0表示不包含。

mysql> SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '$.a') AS contains_value;
+----------------+
| contains_value |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_CONTAINS('[ "apple", "banana", "cherry" ]', '"apple"') AS contains_apple;
+----------------+
| contains_apple |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_CONTAINS('{"fruits": ["apple", "banana", "cherry"]}', '"apple"', '$.fruits') AS contains_apple_in_path;
+------------------------+
| contains_apple_in_path |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_json WHERE JSON_CONTAINS(json_data, '"apple"', '$.fruits');
+----+-------------------------------------------+---------------------+
| id | json_data                                 | create_time         |
+----+-------------------------------------------+---------------------+
|  3 | {"fruits": ["apple", "banana", "cherry"]} | 2024-02-05 07:28:40 |
+----+-------------------------------------------+---------------------+
1 row in set (0.00 sec)

特別需要註意的是：在使用JSON_CONTAINS時，註意數據類型的匹配，確保值的類型與JSON中的類型一致。 JSON_CONTAINS參數傳遞匹配值是''並不代表他是一個字元串，例如上述第一個例子：我們搜索1，使用'1'，搜索apple時，使用'"apple"'。

同時JSON_CONTAINS()不支持對嵌套JSON文檔進行遞歸搜索，它只針對JSON路徑指定的位置進行比較。

JSON_CONTAINS_PATH
用於檢查JSON文檔中是否存在指定路徑的函數。與JSON_CONTAINS()函數不同，JSON_CONTAINS_PATH() 不檢查路徑對應的值是否匹配某個特定值，而是僅判斷JSON文檔內是否存在指定的路徑結構。語法結構：

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

json_doc是待檢查的目標JSON文檔。one_or_all是一個字元串參數，可以是 'one' 或 'all'，'one'表示只要存在任意一個提供的路徑就返回1（真），'all' 表示所有提供的路徑都必須存在才返回1（真）。path：一個或多個JSON路徑表達式，用於在JSON文檔內部定位要檢查的路徑。

mysql> SET @json_data = '{"name":"John","age":30,"hobbies":["reading","coding"],"address":{"city":"New York","country":"USA"}}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_CONTAINS_PATH(@json_data, 'one', '$.name') AS has_name, JSON_CONTAINS_PATH(@json_data, 'one', '$.address.country') AS has_country, JSON_CONTAINS_PATH(@json_data, 'all', '$.hobbies[0]', '$.hobbies[1]') AS has_both_hobbies;
+----------+-------------+------------------+
| has_name | has_country | has_both_hobbies |
+----------+-------------+------------------+
|        1 |           1 |                1 |
+----------+-------------+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test_json WHERE JSON_CONTAINS_PATH(json_data, 'one', '$.address.country');
+----+------------------------------------------------------------------------------------------------------------------+---------------------+
| id | json_data                                                                                                        | create_time         |
+----+------------------------------------------------------------------------------------------------------------------+---------------------+
|  2 | {"age": 30, "name": "John", "address": {"city": "New York", "country": "USA"}, "hobbies": ["reading", "coding"]} | 2024-02-05 07:25:47 |
+----+------------------------------------------------------------------------------------------------------------------+---------------------+
1 row in set (0.00 sec)

JSON_EXTRACT
從JSON欄位中提取指定路徑的值。語法格式：

JSON_EXTRACT(json_doc, path[, path] ...)

json_doc是包含JSON數據的列名或者直接的JSON字元串。path是一個或多個以逗號分隔的JSON路徑表達式，用於指定要提取的值的位置。路徑可以使用點.和方括弧[]來表示對象屬性和數組索引。

mysql> SET @user_info = '{"name":"John Doe","age":30,"hobbies":["reading","coding"],"address":{"street":"123 Main St","city":"New York"}}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   JSON_EXTRACT(@user_info, '$.name') AS name,
    ->   JSON_EXTRACT(@user_info, '$.age') AS age,
    ->   JSON_EXTRACT(@user_info, '$.hobbies[0]') AS first_hobby,
    ->   JSON_EXTRACT(@user_info, '$.address.city') AS city;
+------------+------+-------------+------------+
| name       | age  | first_hobby | city       |
+------------+------+-------------+------------+
| "John Doe" | 30   | "reading"   | "New York" |
+------------+------+-------------+------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT(json_data, '$.name')            AS name,
    ->        JSON_EXTRACT(json_data, '$.age')             AS age,
    ->        JSON_EXTRACT(json_data, '$.hobbies[0]')      AS first_hobby,
    ->        JSON_EXTRACT(json_data, '$.address.city') AS city
    -> FROM test_json
    -> WHERE JSON_CONTAINS_PATH(json_data, 'one', '$.name');
+------------+------+-------------+------------+
| name       | age  | first_hobby | city       |
+------------+------+-------------+------------+
| "John Doe" | 30   | "reading"   | "New York" |
| "John"     | 30   | "reading"   | "New York" |
+------------+------+-------------+------------+
2 rows in set (0.00 sec)

我們介紹一下path中的一些規則：

點.運算符
用於訪問嵌套的對象屬性。例如：$.name 表示訪問頂級對象的 "name" 屬性。
方括弧[]運算符
用於訪問數組元素。對於數組索引，使用數字表示位置，從0開始。例如：$.hobbies[0] 表示訪問頂級對象 "hobbies" 數組的第一個元素。
多路徑查詢
在一個函數調用中可以指定多個路徑，每個路徑之間用逗號分隔。例如：JSON_EXTRACT(json_column, '$.path1', '$.path2')。

JSON_KEYS
用於從JSON文檔中提取所有鍵（key）的一個函數，它返回一個包含JSON對象所有鍵名的數組。這對於需要獲取或操作JSON對象內部屬性名稱時非常有用。語法結構：

JSON_KEYS(json_doc[, path])

json_doc是包含JSON數據的列名或者直接的JSON字元串。path（可選參數）如果提供了路徑，則只返回該路徑指向的對象內的鍵。例如，若要提取嵌套在 JSON 文檔內某個對象的所有鍵，可以指定到該對象的路徑。

mysql> SELECT JSON_KEYS(json_data) AS top_level_keys FROM test_json;
+---------------------------------------+
| top_level_keys                        |
+---------------------------------------+
| ["age", "name", "address", "hobbies"] |
| ["age", "name", "address", "hobbies"] |
| ["fruits"]                            |
+---------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_KEYS(json_data, '$.address') AS address_keys FROM test_json WHERE JSON_CONTAINS_PATH(json_data, 'one', '$.address');
+---------------------+
| address_keys        |
+---------------------+
| ["city", "street"]  |
| ["city", "country"] |
+---------------------+
2 rows in set (0.00 sec)

JSON_OVERLAPS
該函數會檢查兩個JSON對象，並返回布爾值。如果至少有一個鍵存在於兩個對象中且對應值相等，則返回真（1），否則返回假（0）。這個函數並不關註兩個JSON對象的所有內容，僅針對有交集的鍵值對進行比較。語法結構如下：

JSON_OVERLAPS(json_doc1, json_doc2)

mysql> SET @doc1 = '{"name": "John", "age": 30}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @doc2 = '{"name": "John", "address": "New York"}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_OVERLAPS(@doc1, @doc2) AS do_overlap;
+------------+
| do_overlap |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

需要註意的是，如果兩個JSON對象沒有共用的鍵，或者共用的鍵對應的值不相等，則此函數返回0。此外，它只適用於JSON對象，對於JSON數組則不會進行這樣的比較。

MySQL 8.0.17以後才有這個函數

JSON_SEARCH
用於在JSON文檔中搜索指定的字元串值，並返回找到該值的第一個匹配路徑。這個函數對於從JSON數據結構中檢索特定值非常有用。語法結構如下：

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

json_doc包含要搜索的JSON數據的列名或JSON字元串。one_or_all表示搜索模式，可以是 'one' 或 'all',如果是'one'（預設）則只要找到一個匹配就返回第一個匹配的路徑，如果沒有找到則返回NULL，如果是'all'則返回所有匹配路徑的JSON數組，如果沒有找到則返回NULL。search_str是要在JSON文檔中查找的字元串值。escape_char（可選）代表如果搜索字元串中有特殊字元需要轉義，可以提供一個轉義字元。path（可選）可以指定一個或多個JSON路徑，限制搜索範圍到這些路徑所指向的對象或數組元素。

mysql> SELECT
    ->   JSON_SEARCH(json_data, 'one', 'John') AS name_path,
    ->   JSON_SEARCH(json_data, 'all', 'New York') AS main_street_path
    -> FROM test_json;
+-----------+------------------+
| name_path | main_street_path |
+-----------+------------------+
| NULL      | "$.address.city" |
| "$.name"  | "$.address.city" |
| NULL      | NULL             |
+-----------+------------------+
3 rows in set (0.00 sec)

註意，JSON_SEARCH()主要適用於搜索字元串類型的值，在MySQL 8.0及以前版本中，它不支持直接搜索數值型或其他非字元串類型的內容。此外，該函數可能無法處理嵌套的JSON對象或數組內的複雜搜索場景，因為它只能返回單個鍵值對路徑，而不是深度遍歷整個JSON結構以尋找匹配項。

JSON_VALUE
用於從JSON文檔中提取標量值的一個函數。它允許你從JSON數據結構中檢索出符合SQL數據類型的特定鍵的值，並將其轉換為一個可直接在SQL語句中使用的標準SQL數據類型（如字元串、數字或布爾值）。語法格式如下：

JSON_VALUE(json_doc, path)

JSON_VALUE() 返回的是位於給定路徑下的JSON文檔中的標量值（即字元串、數字或布爾值），而不是JSON格式的值。這與 JSON_EXTRACT() 函數不同，後者返回的是JSON格式的值，即使提取的是標量值也是如此。

mysql> SELECT
    ->   JSON_VALUE(json_data, '$.name') AS name,
    ->   JSON_VALUE(json_data, '$.age') AS age,
    ->   JSON_VALUE(json_data, '$.is_student') AS is_student
    -> FROM test_json;
+----------+------+------------+
| name     | age  | is_student |
+----------+------+------------+
| John Doe | 30   | NULL       |
| John     | 30   | NULL       |
| NULL     | NULL | NULL       |
| John Doe | 30   | true       |
+----------+------+------------+
4 rows in set (0.01 sec)

函數簡化了JSON數據在SQL查詢中的處理，特別是當你需要將JSON欄位的值作為普通SQL數據類型進行比較、聚合或其他操作時。

MySQL 8.0.21版本開始提供JSON_VALUE

4、JSON數據修改

JSON_ARRAY_APPEND
用於向JSON數組末尾追加元素的函數。這個函數允許你在現有的JSON數組中添加新的元素，無論是標量值還是嵌套的JSON對象或數組。語法格式：

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

json_doc指包含JSON數據的列名或者直接的JSON字元串。path代表一個JSON路徑表達式，指向要追加元素到其後的JSON數組。而value就是要追加到指定路徑下JSON數組中的值，可以是標量值（如字元串、數字、布爾值），也可以是另一個JSON對象或數組。

mysql> SELECT json_data FROM test_json WHERE id = 3;
+-------------------------------------------+
| json_data                                 |
+-------------------------------------------+
| {"fruits": ["apple", "banana", "cherry"]} |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE test_json
    -> SET json_data = JSON_ARRAY_APPEND(json_data, '$.fruits', 'cherry')
    -> WHERE id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 3;
+-----------------------------------------------------+
| json_data                                           |
+-----------------------------------------------------+
| {"fruits": ["apple", "banana", "cherry", "cherry"]} |
+-----------------------------------------------------+
1 row in set (0.00 sec)

這裡有一點需要註意，如果path所指的對象不是一個數組，那麼在進行追加操作之前，MySQL會將該對象轉換為一個只有一個元素的新數組。

mysql> SELECT json_data FROM test_json WHERE id = 5;
+--------------------------------------------------------------+
| json_data                                                    |
+--------------------------------------------------------------+
| {"fruits": ["apple", "banana", "cherry"], "hobbies": "Java"} |
+--------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> UPDATE test_json
    -> SET json_data = JSON_ARRAY_APPEND(json_data, '$.hobbies', 'Python')
    -> WHERE id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 5;
+--------------------------------------------------------------------------+
| json_data                                                                |
+--------------------------------------------------------------------------+
| {"fruits": ["apple", "banana", "cherry"], "hobbies": ["Java", "Python"]} |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

此函數適用於需要動態修改和擴展資料庫記憶體儲的JSON數組的情況，特別是在處理具有可變長度列表的數據時特別有用。

JSON_ARRAY_INSERT
用於向JSON數組的特定位置插入元素的函數。這個函數允許你在現有的JSON數組的指定索引處插入一個新元素，這個元素可以是單個元素值、JSON數組、JSON對象。語法格式：

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

json_doc代表包含JSON數據的列名或者直接的JSON字元串。而path是一個JSON路徑表達式，指向要插入元素的JSON數組，並且可以指定要插入的位置（通過數組索引）。而value就是要插入到指定路徑下JSON數組中的值，可以是普通值（如字元串、數字、布爾值），也可以是另一個JSON對象或數組。

mysql> SELECT json_data FROM test_json WHERE id = 6;
+----------------------------------------------------+
| json_data                                          |
+----------------------------------------------------+
| {"fruits": ["apple", "banana"], "hobbies": "Java"} |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE test_json
    -> SET json_data = JSON_ARRAY_INSERT(json_data, '$.fruits[0]', 'cherry')
    -> WHERE id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 6;
+--------------------------------------------------------------+
| json_data                                                    |
+--------------------------------------------------------------+
| {"fruits": ["cherry", "apple", "banana"], "hobbies": "Java"} |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

-- 插入一個數組
mysql> UPDATE test_json
    -> SET json_data = JSON_ARRAY_INSERT(json_data, '$.fruits[0]', CAST('["cherry", "orange"]' AS JSON))
    -> WHERE id = 7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 7;
+--------------------------------------------------------------------------+
| json_data                                                                |
+--------------------------------------------------------------------------+
| {"fruits": [["cherry", "orange"], "apple", "banana"], "hobbies": "Java"} |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

JSON_INSERT
用於向JSON文檔插入新鍵值對或替換已存在鍵的值的一個函數。語法格式如下：

JSON_INSERT(json_doc, path, val[, path, val] ...)

如果在JSON文檔中，路徑已存在，則不會覆蓋現有的文檔值。

mysql> SELECT json_data FROM test_json WHERE id = 8;
+----------------------------------------------------+
| json_data                                          |
+----------------------------------------------------+
| {"fruits": ["apple", "banana"], "hobbies": "Java"} |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE test_json
    -> SET json_data = JSON_INSERT(json_data, '$.hobbies', 'Python')
    -> WHERE id = 8;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 8;
+----------------------------------------------------+
| json_data                                          |
+----------------------------------------------------+
| {"fruits": ["apple", "banana"], "hobbies": "Java"} |
+----------------------------------------------------+
1 row in set (0.00 sec)

如果指定的路徑不存在於原始JSON文檔中，則會在該路徑處創建新的鍵值對。

mysql> SELECT json_data FROM test_json WHERE id = 9;
+---------------------------------+
| json_data                       |
+---------------------------------+
| {"fruits": ["apple", "banana"]} |
+---------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE test_json
    -> SET json_data = JSON_INSERT(json_data, '$.hobbies', CAST('["Java", "Python"]' AS JSON),
    ->     '$.name', 'CoderAcademy',
    ->     '$.address', cast('{"city": "New York", "street": "123 Main St"}' AS JSON))
    -> WHERE id = 9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 9;
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| json_data                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| {"name": "CoderAcademy", "fruits": ["apple", "banana"], "address": {"city": "New York", "street": "123 Main St"}, "hobbies": ["Java", "Python"]} |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

JSON_REMOVE
用於從 JSON 文檔中刪除數據。語法結構如下：

JSON_REMOVE(json_doc, path[, path] ...)

json_doc代表包含JSON數據的列名或者直接的JSON字元串。path代表刪除數據的路徑。

如果路徑存在，則會刪除路徑對應的元素，否則對數據沒有影響，即不會刪除，也不會報錯。

mysql> SELECT json_data FROM test_json WHERE id = 10;
+----------------------------------------------------+
| json_data                                          |
+----------------------------------------------------+
| {"fruits": ["apple", "banana"], "hobbies": "Java"} |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE test_json
    -> SET json_data = JSON_REMOVE(json_data, '$.hobbies')
    -> WHERE id = 10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 10;
+---------------------------------+
| json_data                       |
+---------------------------------+
| {"fruits": ["apple", "banana"]} |
+---------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE test_json
    -> SET json_data = JSON_REMOVE(json_data, '$.fruits[1]')
    -> WHERE id = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 10;
+-----------------------+
| json_data             |
+-----------------------+
| {"fruits": ["apple"]} |
+-----------------------+
1 row in set (0.00 sec)

mysql> UPDATE test_json
    -> SET json_data = JSON_REMOVE(json_data, '$.fruits[1]')
    -> WHERE id = 10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 10;
+-----------------------+
| json_data             |
+-----------------------+
| {"fruits": ["apple"]} |
+-----------------------+
1 row in set (0.00 sec)

JSON_REPLACE
函數用於替換 JSON 文檔中的現有值。語法結構如下：

JSON_REPLACE(json_doc, path, val[, path, val] ...)

json_doc代表包含JSON數據的列名或者直接的JSON字元串。path代表替換數據的路徑。val代表要替換的值。

如果替換路徑在文檔中存在，則就用新值覆蓋文檔中原值，否則不會替換，也不會報錯。

mysql> SELECT json_data FROM test_json WHERE id = 11;
+----------------------------------------------------+
| json_data                                          |
+----------------------------------------------------+
| {"fruits": ["apple", "banana"], "hobbies": "Java"} |
+----------------------------------------------------+
1 row in set (0.01 sec)

mysql> UPDATE test_json
    -> SET json_data = JSON_REPLACE(json_data, '$.name', 'CoderAcademy')
    -> WHERE id = 11;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 11;
+----------------------------------------------------+
| json_data                                          |
+----------------------------------------------------+
| {"fruits": ["apple", "banana"], "hobbies": "Java"} |
+----------------------------------------------------+
1 row in set (0.00 sec)


mysql> UPDATE test_json
    -> SET json_data = JSON_REPLACE(json_data, '$.fruits[1]', 'orange', '$.hobbies',  CAST('["Java", "Python"]' AS JSON))
    -> WHERE id = 11;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 11;
+----------------------------------------------------------------+
| json_data                                                      |
+----------------------------------------------------------------+
| {"fruits": ["apple", "orange"], "hobbies": ["Java", "Python"]} |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

JSON_SET
用於在 JSON 文檔中插入或更新數據。語法格式如下：

JSON_SET(json_doc, path, val[, path, val] ...)

json_doc代表包含JSON數據的列名或者直接的JSON字元串。path代表替換數據的路徑。val代表要插入或更新的新值。

如果路徑在文檔中已存在，則會覆蓋原文檔中值，如果不存在，則插入新值。

mysql> SELECT json_data FROM test_json WHERE id = 12;
+----------------------------------------------------+
| json_data                                          |
+----------------------------------------------------+
| {"fruits": ["apple", "banana"], "hobbies": "Java"} |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE test_json
    -> SET json_data = JSON_SET(json_data, '$.fruits[1]', 'orange',
    ->     '$.hobbies', CAST('["Java", "Python"]' AS JSON),
    ->     '$.name', 'CoderAcademy',
    ->     '$.address', cast('{"city": "New York", "street": "123 Main St"}' AS JSON))
    -> WHERE id = 12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT json_data FROM test_json WHERE id = 12;
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| json_data                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| {"name": "CoderAcademy", "fruits": ["apple", "orange"], "address": {"city": "New York", "street": "123 Main St"}, "hobbies": ["Java", "Python"]} |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

JSON類型列的索引

在 MySQL 8.0 中，JSON 類型的索引有一些重要的改進和新特性。JSON 類型的索引使得在處理包含 JSON 數據的列時，查詢的性能得到了顯著提升。

JSON路徑索引

MySQL 8.0 引入了 JSON 路徑索引，允許在 JSON 對象的特定路徑上創建索引，以便更高效地執行 JSON 路徑查詢。路徑索引允許在 JSON 對象中的特定路徑上進行範圍查詢、排序和過濾。我們以查詢地址信息中country等於"US"為例，他有這幾中寫法：

-- 使用JSON_CONTAINS
SELECT * FROM user_info WHERE JSON_CONTAINS(address, '"US"', '$.country');

-- 使用JSON_VALUE
SELECT * FROM user_info WHERE JSON_VALUE(address, '$.country') = "US";

-- 使用JSON_EXTRACT
SELECT * FROM user_info WHERE JSON_EXTRACT(address, '$.country') = 'US';

-- 使用 ->> 運算符
SELECT * FROM user_info WHERE address->>"$.country" = "US";
-- 或者
SELECT * FROM user_info WHERE CAST(address->>"$.country" AS CHAR(30)) = "US";

在JSON類型欄位上創建索引時，要遵守的規則是要確保索引表達式與查詢時的條件表達式匹配，這樣MySQL才能正確地使用索引進行優化查詢。

所以針對不同的sql查詢，我們提供不同的索引。例如：

使用JSON_EXTRACT
我們可以採取新增一個虛擬列的方式去使用索引，比如我們新增一個country的虛擬列，然後在虛擬列上增加索引

-- 添加生成的虛擬列  
ALTER TABLE user_info  
ADD COLUMN country VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(address, '$.country'))) STORED;  
  
-- 在生成的列上創建索引  
CREATE INDEX idx_json_country ON user_info(country);

我們執行一下計劃：

mysql> EXPLAIN SELECT * FROM user_info WHERE country = 'US';
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | ref  | idx_json_country | idx_json_country | 1023    | const |    2 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

可以看出使用了索引idx_json_country。

使用 ->> 運算符
在 JSON 類型的索引中，使用 ->> 運算符，會轉換為 JSON_UNQUOTE(JSON_EXTRACT(...))，而 JSON_UNQUOTE() 返回的值具有LONGTEXT 數據類型。MySQL不能對沒有鍵部分上的首碼長度指定的 LONGTEXT 列建立索引，而在功能性鍵部分中又不允許指定首碼長度。

我們可以這樣創建索引：

CREATE INDEX idx_json_country_cast ON user_info((CAST(address->>"$.country" AS CHAR(30)) COLLATE utf8mb4_bin));

然後看一下執行計劃：

mysql> EXPLAIN SELECT * FROM user_info WHERE address->>"$.country" = "US";
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | ref  | idx_json_country | idx_json_country_cast | 123    | const |    2 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

多值索引

多值索引是 MySQL 8.0.17 版本引入的新功能，它允許在 InnoDB 存儲引擎中創建索引來支持對存儲數組值的列進行高效查詢。傳統的索引是一對一的，而多值索引允許在一個數據記錄上擁有多個索引記錄。多值索引主要用於索引 JSON 數組。

要創建多值索引，可以在 CREATE TABLE、ALTER TABLE 或 CREATE INDEX 語句中使用 CAST(... AS ... ARRAY) 函數來定義。這將把 JSON 數組中的同類型標量值轉換為 SQL 數據類型數組。然後，MySQL 會在這個 SQL 數據類型數組上創建一個虛擬列，併在虛擬列上創建一個功能性索引。最終，這個功能性索引構成了多值索引。

例如，我們在address中增加一個zipcode列用於存儲地址郵編，每個地址包含若幹個郵編。我們對這個zipcode就可以使用多值索引。

mysql> ALTER TABLE user_info ADD INDEX idx_json_zipcode((CAST(address->'$.zipcode' AS SIGNED ARRAY)));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

我們分別執行以下sql進行驗證：

mysql> SELECT * FROM user_info WHERE 94507 MEMBER OF(address->'$.zipcode');
+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+
| id | user_name | address                                                                                          | create_time         |
+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+
|  2 | lisi      | {"city": "shanghai", "street": "123 Main St", "country": "CN", "zipcode": [94568, 94507, 94582]} | 2024-02-05 11:08:22 |
|  3 | wangwu    | {"city": "guangzhou", "street": "123 Main St", "country": "CN", "zipcode": [94477, 94507]}       | 2024-02-05 11:08:22 |
|  4 | qianliu   | {"city": "New York", "street": "123 Main St", "country": "US", "zipcode": [94507, 94582]}        | 2024-02-05 11:08:22 |
+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+
3 rows in set (0.01 sec)

mysql> EXPLAIN
    -> SELECT * FROM user_info WHERE 94507 MEMBER OF(address->'$.zipcode');
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | ref  | idx_json_zipcode | idx_json_zipcode | 9       | const |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看出使用了索引。

mysql> SELECT * FROM user_info WHERE JSON_CONTAINS(address->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+
| id | user_name | address                                                                                          | create_time         |
+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+
|  2 | lisi      | {"city": "shanghai", "street": "123 Main St", "country": "CN", "zipcode": [94568, 94507, 94582]} | 2024-02-05 11:08:22 |
|  4 | qianliu   | {"city": "New York", "street": "123 Main St", "country": "US", "zipcode": [94507, 94582]}        | 2024-02-05 11:08:22 |
+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+
2 rows in set (0.01 sec)

mysql> EXPLAIN
    -> SELECT * FROM user_info WHERE JSON_CONTAINS(address->'$.zipcode', CAST('[94507,94582]' AS JSON));
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | range | idx_json_zipcode | idx_json_zipcode | 9       | NULL |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

多值索引還可以作為複合索引的一部分進行定義。在複合索引中，只能有一個多值鍵部分，並且可以與其他單值部分一起使用。多值鍵部分可以按任意順序使用。

mysql> ALTER TABLE user_info ADD INDEX idx_name_zipcode(user_name, (CAST(address->'$.zipcode' AS SIGNED ARRAY)));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

總結

MySQL提供了豐富的內置函數支持JSON數據類型的存儲和操作，如檢查、提取、插入、更新及搜索JSON文檔中的內容。此外，為了提高含有JSON欄位的查詢效率，用戶可以在滿足條件的情況下為JSON特定路徑創建索引，如使用虛擬生成列或者MySQL 8.0以上的原生JSON路徑索引功能。多值索引特別適用於JSON數組元素的檢索優化，使得資料庫能針對數組內的每個獨立值建立索引記錄，提升複雜查詢場景下的性能表現。

我們可以瞭解到 JSON 數據類型的靈活性、易用性以及適用性，以及如何在實際項目中充分利用它來應對動態數據模型、半結構化數據存儲和查詢等方面的挑戰。

文中示例表結構與數據

CREATE TABLE `test_json`  
(  
    `id`          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',  
    `json_data`     JSON COMMENT 'json值',  
    `create_time` datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',  
    PRIMARY KEY ( `id` )  
) ENGINE = INNODB  
  AUTO_INCREMENT = 1  
  DEFAULT CHARSET = utf8mb4 COMMENT = 'json測試表';


CREATE TABLE `user_info`  
(  
    `id`          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',  
    `user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '名稱',  
    `address`     JSON COMMENT '地址信息',  
    `create_time` datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',  
    PRIMARY KEY ( `id` )  
) ENGINE = INNODB  
  AUTO_INCREMENT = 1  
  DEFAULT CHARSET = utf8mb4 COMMENT = '用戶信息';