當前位置:網站首頁>【好文分享】MySQL JSON 數據類型解說

【好文分享】MySQL JSON 數據類型解說

2022-05-13 10:57:04Mr song song

JSON 數據類型是 MySQL 5.7.8 開始支持的。在此之前,只能通過字符類型(CHAR,VARCHAR 或 TEXT )來保存 JSON 文檔。

相對字符類型,原生的 JSON 類型具有以下優勢:

  1. 在插入時能自動校驗文檔是否滿足 JSON 格式的要求。
  2. 優化了存儲格式。無需讀取整個文檔就能快速訪問某個元素的值。

在 JSON 類型引入之前,如果我們想要獲取 JSON 文檔中的某個元素,必須首先讀取整個 JSON 文檔,然後在客戶端將其轉換為 JSON 對象,最後再通過對象獲取指定元素的值。

下面是 Python 中的獲取方式。

import json

# JSON 字符串:
x =  '{ "name":"John", "age":30, "city":"New York"}'

# 將 JSON 字符串轉換為 JSON 對象:
y = json.loads(x)

# 讀取 JSON 對象中指定元素的值:
print(y["age"])

這種方式有兩個弊端:一、消耗磁盤 IO,二、消耗網絡帶寬,如果 JSON 文檔比較大,在高並發場景,有可能會打爆網卡。

如果使用的是 JSON 類型,相同的需求,直接使用 SQL 命令就可搞定。不僅能節省網絡帶寬,結合後面提到的函數索引,還能降低磁盤 IO 消耗。

mysql> create table t(c1 json);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values('{ "name":"John", "age":30, "city":"New York"}');
Query OK, 1 row affected (0.01 sec)

mysql> select c1->"$.age" from t;
+-------------+
| c1->"$.age" |
+-------------+
| 30          |
+-------------+
1 row in set (0.00 sec)

本文將從以下幾個方面展開:

  1. 什麼是 JSON。
  2. JSON 字段的增删改查操作。
  3. 如何對 JSON 字段創建索引。
  4. 如何將存儲 JSON 字符串的字符字段昇級為 JSON 字段。
  5. 使用 JSON 時的注意事項。
  6. Partial Updates。
  7. 其它 JSON 函數。

一、什麼是 JSON

JSON 是 JavaScript Object Notation(JavaScript 對象錶示法)的縮寫,是一個輕量級的,基於文本的,跨語言的數據交換格式。易於閱讀和編寫。

JSON 的基本數據類型如下:

  • 數值:十進制數,不能有前導 0,可以為負數或小數,還可以為 e 或 E 錶示的指數。

  • 字符串:字符串必須用雙引號括起來。

  • 布爾值:true,false。

  • 數組:一個由零或多個值組成的有序序列。每個值可以為任意類型。數組使用方括號[] 括起來,元素之間用逗號,分隔。譬如,

    [1, "abc", null, true, "10:27:06.000000", {"id": 1}]
    
  • 對象:一個由零或者多個鍵值對組成的無序集合。其中鍵必須是字符串,值可以為任意類型。

    對象使用花括號{}括起來,鍵值對之間使用逗號,分隔,鍵與值之間用冒號:分隔。譬如,

    {"db": ["mysql", "oracle"], "id": 123, "info": {"age": 20}}
    
  • 空值:null。

二、JSON 字段的增删改查操作

下面我們看看 JSON 字段常見的增删改查操作:

2.1 插入操作

可直接插入 JSON 格式的字符串。

mysql> create table t(c1 json);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values('[1, "abc", null, true, "08:45:06.000000"]');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values('{"id": 87, "name": "carrot"}');
Query OK, 1 row affected (0.01 sec)

也可使用函數,常用的有 JSON_ARRAY() 和 JSON_OBJECT(),前者用於構造 JSON 數組,後者用於構造 JSON 對象。如,

mysql> select json_array(1, "abc", null, true,curtime());
+--------------------------------------------+
| json_array(1, "abc", null, true,curtime()) |
+--------------------------------------------+
| [1, "abc", null, true, "10:12:25.000000"]  |
+--------------------------------------------+
1 row in set (0.01 sec)

mysql> select json_object('id', 87, 'name', 'carrot');
+-----------------------------------------+
| json_object('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+
1 row in set (0.00 sec)

對於 JSON 文檔,KEY 名不能重複。

如果插入的值中存在重複 KEY,在 MySQL 8.0.3 之前,遵循 first duplicate key wins 原則,會保留第一個 KEY,後面的將被丟弃掉。

從 MySQL 8.0.3 開始,遵循的是 last duplicate key wins 原則,只會保留最後一個 KEY。

下面通過一個具體的示例來看看兩者的區別。

MySQL 5.7.36

mysql> select json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
| json_object('key1',10,'key2',20,'key1',30) |
+--------------------------------------------+
| {"key1": 10, "key2": 20}                   |
+--------------------------------------------+
1 row in set (0.02 sec)

MySQL 8.0.27

mysql> select json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
| json_object('key1',10,'key2',20,'key1',30) |
+--------------------------------------------+
| {"key1": 30, "key2": 20}                   |
+--------------------------------------------+
1 row in set (0.00 sec)

2.2 查詢操作

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

其中,json_doc 是 JSON 文檔,path 是路徑。該函數會從 JSON 文檔提取指定路徑(path)的元素。如果指定 path 不存在,會返回 NULL。可指定多個 path,匹配到的多個值會以數組形式返回。

下面我們結合一些具體的示例來看看 path 及 JSON_EXTRACT 的用法。

首先我們看看數組。

數組的路徑是通過下標來錶示的。第一個元素的下標是 0。

mysql> select json_extract('[10, 20, [30, 40]]', '$[0]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]') |
+--------------------------------------------+
| 10                                         |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract('[10, 20, [30, 40]]', '$[0]', '$[1]','$[2][0]');
+--------------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]', '$[1]','$[2][0]') |
+--------------------------------------------------------------+
| [10, 20, 30]                                                 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

除此之外,還可通過 [M to N] 獲取數組的子集。

mysql> select json_extract('[10, 20, [30, 40]]', '$[0 to 1]');
+-------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0 to 1]') |
+-------------------------------------------------+
| [10, 20]                                        |
+-------------------------------------------------+
1 row in set (0.00 sec)

# 這裏的 last 代錶最後一個元素的下標
mysql> select json_extract('[10, 20, [30, 40]]', '$[last-1 to last]');
+---------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[last-1 to last]') |
+---------------------------------------------------------+
| [20, [30, 40]]                                          |
+---------------------------------------------------------+
1 row in set (0.00 sec)

也可通過 [*] 獲取數組中的所有元素。

mysql> select json_extract('[10, 20, [30, 40]]', '$[*]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[*]') |
+--------------------------------------------+
| [10, 20, [30, 40]]                         |
+--------------------------------------------+
1 row in set (0.00 sec)

接下來,我們看看對象。

對象的路徑是通過 KEY 來錶示的。

mysql> set @j='{"a": 1, "b": [2, 3], "a c": 4}';
Query OK, 0 rows affected (0.00 sec)

# 如果 KEY 在路徑錶達式中不合法(譬如存在空格),則在引用這個 KEY 時,需用雙引號括起來。
mysql> select json_extract(@j, '$.a'), json_extract(@j, '$."a c"'), json_extract(@j, '$.b[1]');
+-------------------------+-----------------------------+----------------------------+
| json_extract(@j, '$.a') | json_extract(@j, '$."a c"') | json_extract(@j, '$.b[1]') |
+-------------------------+-----------------------------+----------------------------+
| 1                       | 4                           | 3                          |
+-------------------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)

除此之外,還可通過 .* 獲取對象中的所有元素。

mysql> select json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*');
+--------------------------------------------------------+
| json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*') |
+--------------------------------------------------------+
| [1, [2, 3], 4]                                         |
+--------------------------------------------------------+
1 row in set (0.00 sec)

# 這裏的 $**.b 匹配 $.a.b 和 $.c.b
mysql> select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

column->path

column->path,包括後面講到的 column->>path,都是語法糖,在實際使用的時候都會轉化為 JSON_EXTRACT。

column->path 等同於 JSON_EXTRACT(column, path) ,只能指定一個path。

create table t(c2 json);

insert into t values('{"empno": 1001, "ename": "jack"}'), ('{"empno": 1002, "ename": "mark"}');

mysql> select c2, c2->"$.ename" from t;
+----------------------------------+---------------+
| c2                               | c2->"$.ename" |
+----------------------------------+---------------+
| {"empno": 1001, "ename": "jack"} | "jack"        |
| {"empno": 1002, "ename": "mark"} | "mark"        |
+----------------------------------+---------------+
2 rows in set (0.00 sec)

mysql> select * from t where c2->"$.empno" = 1001;
+------+----------------------------------+
| c1   | c2                               |
+------+----------------------------------+
|    1 | {"empno": 1001, "ename": "jack"} |
+------+----------------------------------+
1 row in set (0.00 sec)

column->>path

同 column->path 類似,只不過其返回的是字符串。以下三者是等價的。

  • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
  • JSON_UNQUOTE(column -> path)
  • column->>path
mysql> select c2->'$.ename',json_extract(c2, "$.ename"),json_unquote(c2->'$.ename'),c2->>'$.ename' from t;
+---------------+-----------------------------+-----------------------------+----------------+
| c2->'$.ename' | json_extract(c2, "$.ename") | json_unquote(c2->'$.ename') | c2->>'$.ename' |
+---------------+-----------------------------+-----------------------------+----------------+
| "jack"        | "jack"                      | jack                        | jack           |
| "mark"        | "mark"                      | mark                        | mark           |
+---------------+-----------------------------+-----------------------------+----------------+
2 rows in set (0.00 sec)

2.3 修改操作

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

插入新值。

僅當指定比特置或指定 KEY 的值不存在時,才執行插入操作。另外,如果指定的 path 是數組下標,且 json_doc 不是數組,該函數首先會將 json_doc 轉化為數組,然後再插入新值。

下面我們看幾個示例。

mysql> select json_insert('1','$[0]',"10");
+------------------------------+
| json_insert('1','$[0]',"10") |
+------------------------------+
| 1                            |
+------------------------------+
1 row in set (0.00 sec)

mysql> select json_insert('1','$[1]',"10");
+------------------------------+
| json_insert('1','$[1]',"10") |
+------------------------------+
| [1, "10"]                    |
+------------------------------+
1 row in set (0.01 sec)

mysql> select json_insert('["1","2"]','$[2]',"10");
+--------------------------------------+
| json_insert('["1","2"]','$[2]',"10") |
+--------------------------------------+
| ["1", "2", "10"]                     |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_insert(@j, '$.a', 10, '$.c', '[true, false]');
+----------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
+----------------------------------------------------+
1 row in set (0.00 sec)

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

插入新值,並替換已經存在的值。

換言之,如果指定比特置或指定 KEY 的值不存在,會執行插入操作,如果存在,則執行更新操作。

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_set(@j, '$.a', 10, '$.c', '[true, false]');
+-------------------------------------------------+
| json_set(@j, '$.a', 10, '$.c', '[true, false]') |
+-------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"}    |
+-------------------------------------------------+
1 row in set (0.00 sec)

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

替換已經存在的值。

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_replace(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| json_replace(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]}                              |
+-----------------------------------------------------+
1 row in set (0.00 sec)

2.4 删除操作

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

删除 JSON 文檔指定比特置的元素。

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_remove(@j, '$.a');
+------------------------+
| JSON_REMOVE(@j, '$.a') |
+------------------------+
| {"b": [2, 3]}          |
+------------------------+
1 row in set (0.00 sec)

mysql> set @j = '["a", ["b", "c"], "d", "e"]';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_remove(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d", "e"]         |
+-------------------------+
1 row in set (0.00 sec)

mysql> select json_remove(@j, '$[1]','$[2]');
+--------------------------------+
| JSON_REMOVE(@j, '$[1]','$[2]') |
+--------------------------------+
| ["a", "d"]                     |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select json_remove(@j, '$[1]','$[1]');
+--------------------------------+
| JSON_REMOVE(@j, '$[1]','$[1]') |
+--------------------------------+
| ["a", "e"]                     |
+--------------------------------+
1 row in set (0.00 sec)

最後一個查詢,雖然兩個 path 都是 '$[1]' ,但作用對象不一樣,第一個 path 的作用對象是 '["a", ["b", "c"], "d", "e"]' ,第二個 path 的作用對象是删除了 '$[1]' 後的數組,即 '["a", "d", "e"]' 。

三、如何對 JSON 字段創建索引

同 TEXT,BLOB 字段一樣,JSON 字段不允許直接創建索引。

mysql> create table t(c1 json, index (c1));
ERROR 3152 (42000): JSON column 'c1' supports indexing only via generated columns on a specified JSON path.

即使支持,實際意義也不大,因為我們一般是基於文檔中的元素進行查詢,很少會基於整個  JSON 文檔。

對文檔中的元素進行查詢,就需要用到 MySQL 5.7 引入的虛擬列及函數索引。

下面我們來看一個具體的示例。

# C2 即虛擬列
# index (c2) 對虛擬列添加索引。
create table t ( c1 json, c2 varchar(10) as (JSON_UNQUOTE(c1 -> "$.name")), index (c2) );

insert into t (c1) values  ('{"id": 1, "name": "a"}'), ('{"id": 2, "name": "b"}'), ('{"id": 3, "name": "c"}'), ('{"id": 4, "name": "d"}');

mysql> explain select * from t where c2 = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref  | c2            | c2   | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t where c1->'$.name' = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref  | c2            | c2   | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可以看到,無論是使用虛擬列,還是文檔中的元素來查詢,都可以利用上索引。

注意,在創建虛擬列時需指定  JSON_UNQUOTE,將 c1 -> "$.name" 的返回值轉換為字符串。

四、如何將存儲 JSON 字符串的字符字段昇級為 JSON 字段

在 MySQL 支持 JSON 類型之前,對於 JSON 文檔,一般是以字符串的形式存儲在字符類型(VARCHAR 或 TEXT)中。

在 JSON 類型出來之後,如何將這些字符字段昇級為 JSON 字段呢?

為方便演示,這裏首先構建測試數據。

create table t (id int auto_increment primary key, c1 text);

insert into t (c1) values ('{"id": "1", "name": "a"}'), ('{"id": "2", "name": "b"}'), ('{"id": "3", "name": "c"}'), ('{"id", "name": "d"}');

注意,最後一個文檔有問題,不是合格的 JSON 文檔。

如果使用 DDL 直接修改字段的數據類型,會報錯。

mysql> alter table t modify c1 json;
ERROR 3140 (22032): Invalid JSON text: "Missing a colon after a name of object member." at position 5 in value for column '#sql-7e1c_1f6.c1'.

下面,我們看看具體的昇級步驟。

(1)使用 json_valid 函數找出不滿足 JSON 格式要求的文檔。

mysql> select * from t where json_valid(c1) = 0;
+----+---------------------+
| id | c1                  |
+----+---------------------+
|  4 | {"id", "name": "d"} |
+----+---------------------+
1 row in set (0.00 sec)

(2)處理不滿足 JSON 格式要求的文檔。

mysql> update t set c1='{"id": "4", "name": "d"}' where id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(3)將 TEXT 字段修改為 JSON 字段。

mysql> select * from t where json_valid(c1) = 0;
Empty set (0.00 sec)

mysql> alter table t modify c1 json;
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

SQL 複制 全屏

五、使用 JSON 時的注意事項

對於 JSON 類型,有以下幾點需要注意:

  1. 在 MySQL 8.0.13 之前,不允許對 BLOB,TEXT,GEOMETRY,JSON 字段設置默認值。從 MySQL 8.0.13 開始,取消了這個限制。

    設置時,注意默認值需通過小括號()括起來,否則的話,還是會提示 JSON 字段不允許設置默認值。

    mysql> create table t(c1 json not null default (''));
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> create table t(c1 json not null default '');
    ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'c1' can't have a default value
    
  2. 不允許直接創建索引,可創建函數索引。

  3. JSON 列的最大大小和 LONGBLOB(LONGTEXT)一樣,都是 4G。

  4. 插入時,單個文檔的大小受到 max_allowed_packet 的限制,該參數最大是 1G。

轉發自:一文說透 MySQL JSON 數據類型(收藏) - iVictor - 博客園

版權聲明
本文為[Mr song song]所創,轉載請帶上原文鏈接,感謝
https://cht.chowdera.com/2022/133/202205131050303009.html

隨機推薦