一、前言

最近做的一個 填報項目，由于填報的字段比較多于是便在數(shù)據(jù)庫當(dāng)中使用了longtext類型的字段直接存儲json數(shù)據(jù)。

為什么選擇直接存儲json數(shù)據(jù)？

業(yè)務(wù)比較復(fù)雜，涉及到很多 一對多 的多表關(guān)聯(lián)，假如拆分存到各個業(yè)務(wù)表，那么每次查詢修改是非常繁瑣的，存儲json一張表即可解決。字段全部由前端來定，減少了前后端聯(lián)調(diào)時間。避免填報內(nèi)容需求頻繁調(diào)整而不斷修改數(shù)據(jù)庫結(jié)構(gòu)，比如加字段，減字段這些都是經(jīng)常有的事，完全可以讓前端全權(quán)負責(zé)，你存什么我就給你返回什么。

但是這里我也是有一點好奇，既然存儲為json，為什么不直接將字段類型設(shè)置為json？由于最近經(jīng)常會用到數(shù)據(jù)庫存json串，也是下定決心徹底把這塊相關(guān)的知識給梳理一遍。

從5.7開始，MySQL開始支持json類型，用于存儲JSON數(shù)據(jù)。關(guān)于json類型，mysql官網(wǎng)介紹以及使用，本篇文章也是重點整理的官網(wǎng)相關(guān)知識：https://dev.mysql.com/doc/refman/8.0/en/json.html#json-values

json數(shù)據(jù)類型提供了以下優(yōu)勢:

插入數(shù)據(jù)的時候自動驗證數(shù)據(jù)是否是標準的json數(shù)據(jù)格式，如果不是會報異常！假如使用longtext存儲json并不會有這種校驗。

優(yōu)化存儲格式。存儲在json列中的JSON文檔被轉(zhuǎn)換為內(nèi)部格式，允許對文檔元素進行快速讀取訪問?？梢灾苯油ㄟ^鍵或數(shù)組索引查找子對象或嵌套值，而無需讀取json中所有值。

注意：

• 存儲在JSON列中的任何JSON文檔的大小都受限于最大允許的數(shù)據(jù)包系統(tǒng)變量的值，可以使用JSON_STORAGE_SIZE()獲取到j(luò)son長度
• 在MySQL 8.0.13之前，JSON列不能有非null的默認值。
• 存儲JSON文檔所需的空間與LONGBLOB或LONGTEXT大致相同
• Json類型不支持索引，但是可以在Json當(dāng)中的列上使用索引！在MySQL 8.0.17及以后的版本中，InnoDB存儲引擎支持JSON數(shù)組上的多值索引。
• MySQL優(yōu)化器還會在匹配JSON表達式的虛擬列上尋找兼容的索引。
• 在MySQL 8.0中，MySQL 優(yōu)化器可以對JSON列執(zhí)行局部就地更新，而不是刪除舊文檔并將整個新文檔寫入該列。（在后面會重點講解這一部分）

二、什么是 JSON

JSON 是 JavaScript Object Notation（JavaScript 對象表示法）的縮寫，是一個輕量級的，基于文本的，跨語言的數(shù)據(jù)交換格式。易于閱讀和編寫。

JSON 的基本數(shù)據(jù)類型如下：

• 數(shù)值：十進制數(shù)，可以為負數(shù)或小數(shù)。
• 字符串：字符串是由雙引號""包圍的任意數(shù)量Unicode字符的集合，特殊符號使用反斜線轉(zhuǎn)義。
• 布爾值：true，false。
• 數(shù)組：一個由零或多個值組成的有序序列。每個值可以為任意類型。數(shù)組使用方括號[] 括起來，元素之間用逗號,分隔。譬如：

 [1, "abc", null, true, "10:27:06.000000", {"id": 1}]

對象：一個由零或者多個鍵值對組成的無序集合。其中鍵必須是字符串，值可以是對象、數(shù)組、數(shù)字、字符串或者三個字面值(false、null、true)中的一個。值中的字面值中的英文必須使用小寫。
對象使用花括號{}括起來，鍵值對之間使用逗號 , 分隔，鍵與值之間用冒號 : 分隔。譬如：

 {"name": "John Doe", "age": 18, "address": {"country" : "china", "zip-code": "10000"}}

空值：null。

一些合法的JSON的實例：

{"a": 1, "b": [1, 2, 3]}
[1, 2, "3", {"a": 4}]
3.14
"plain_text"

JSON 與 JS 對象的關(guān)系
很多人搞不清楚 JSON 和 JS 對象的關(guān)系，甚至連誰是誰都不清楚。其實，可以這么理解：
JSON 是 JS 對象的字符串表示法，它使用文本表示一個 JS 對象的信息，本質(zhì)是一個字符串。如

var obj = {a: 'Hello', b: 'World'}; //這是一個對象，注意鍵名也是可以使用引號包裹的
var json = '{"a": "Hello", "b": "World"}'; //這是一個 JSON 字符串，本質(zhì)是一個字符串

JSON 和 JS 對象互轉(zhuǎn)

要實現(xiàn)從JSON字符串轉(zhuǎn)換為JS對象，使用 JSON.parse() 方法：

var obj = JSON.parse('{"a": "Hello", "b": "World"}'); //結(jié)果是 {a: 'Hello', b: 'World'}

要實現(xiàn)從JS對象轉(zhuǎn)換為JSON字符串，使用 JSON.stringify() 方法：

var json = JSON.stringify({<!--{C}%3C!%2D%2D%20%2D%2D%3E-->a: 'Hello', b: 'World'}); //結(jié)果是 '{"a": "Hello", "b": "World"}'

簡單地說，JSON 可以將 JavaScript 對象中表示的一組數(shù)據(jù)轉(zhuǎn)換為字符串，然后就可以在網(wǎng)絡(luò)或者程序之間輕松地傳遞這個字符串，并在需要的時候?qū)⑺€原為各編程語言所支持的數(shù)據(jù)格式，例如在 Java中，可以將 JSON 還原為數(shù)組或者一個基本對象。

XML本質(zhì)上也可以作為跨語言的數(shù)據(jù)交換格式，JSON和XML的可讀性可謂不相上下，一邊是簡易的語法，一邊是規(guī)范的標簽形式，很難分出勝負。

三、Mysql當(dāng)中json函數(shù)

http://www.dbjr.com.cn/database/2878197v4.htm

四、JSON值部分更新

4.1.使用 Partial Updates 的條件

在MySQL 8.0中，優(yōu)化器可以對JsoN列執(zhí)行局部就地更新，而不是刪除舊文檔并將整個新文檔寫入該列。此優(yōu)化可以在滿足以下條件的更新中執(zhí)行:

• 要更新的列被聲明為JSON。
• UPDATE語句使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()這三個函數(shù)中的任意一個來更新列。直接賦值列值(例如，UPDATE mytable SET jcol = '{"a": 10, "b": 25}')不能作為部分更新執(zhí)行。MySQL只能對使用上面列出的三個函數(shù)更新值的列執(zhí)行部分更新。
• 輸入列和目標列必須是同一列，像UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100)這樣的語句不能作為部分更新執(zhí)行。
• 所有的更改都用新的值替換現(xiàn)有的數(shù)組或?qū)ο笾?，并且不向父對象或?shù)組添加任何新元素。
• 被替換的值必須至少與替換值一樣大。換句話說，新值不能大于舊值。當(dāng)先前的部分更新為較大的值留下了足夠的空間時，可能會出現(xiàn)此需求的異常。您可以使用JSON_STORAGE FREE()函數(shù)查看JSON列的任何部分更新釋放了多少空間。
• JSON文檔的部分更新只能在列值上執(zhí)行。對于存儲JSON值的用戶變量，該值總是被完全替換，即使使用JSON_SET()執(zhí)行更新:

JSON_STORAGE_FREE（更新后釋放的空間）

• 描述：主要是記錄JSON_SET()、JSON_REPLACE()或JSON_REMOVE()使用這三個函數(shù)進行就地更新后，其二進制表示形式釋放了多少存儲空間。
• 語法：JSON_STORAGE_FREE(json_val)

返回值：

• 如果參數(shù)是一個JSON列值，并按照前面的描述進行了更新，則返回一個正的非零值，這樣它的二進制表示比更新之前占用的空間更少。
• 假如其二進制表示與以前相同或更大，或者如果更新無法利用部分更新，則返回0
• 假如沒有使用那三個函數(shù)，也會返回0

創(chuàng)建測試表

mysql> CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.38 sec)
mysql> INSERT INTO jtable VALUES
    ->     ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM jtable;
+----------------------------------------------+
| jcol                                         |
+----------------------------------------------+
| {"a": 10, "b": "wxyz", "c": "[true, false]"} |
+----------------------------------------------+
1 row in set (0.00 sec)

現(xiàn)在我們使用JSON_SET()更新列值，這樣就可以執(zhí)行部分更新;在本例中，我們將c鍵所指向的值(數(shù)組[true, false])替換為占用更少空間的值(整數(shù)1):

mysql> UPDATE jtable
    ->     SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wx", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                      16 |
+-------------------------+
1 row in set (0.00 sec)

4.2.如何在 binlog 中開啟 Partial Updates

這種部分更新可以使用壓縮格式寫入二進制日志，以節(jié)省空間;這可以通過將 binlog_row_value_options選項系統(tǒng)變量設(shè)置為PARTIAL_JSON來啟用。

binlog_row_value_options 參數(shù)是MySQL 8.0.3 版本引入的新參數(shù)，該參數(shù)主要用于JSON類型的字段更新時，只記錄更新的那部分數(shù)據(jù)到binlog，而不是記錄完整的JSON數(shù)據(jù)，這樣能夠顯著減少JSON字段更新產(chǎn)生的binlog文件大小。

需要注意的是，binlog 中使用 部分更新（Partial Updates），只需滿足存儲引擎層使用 Partial Updates 的前幾個條件，無需考慮變更前后，JSON 文檔的空間使用是否會增加。

4.3.關(guān)于 Partial Updates 的性能測試

首先構(gòu)造測試數(shù)據(jù)，t 表一共有 16 個文檔，每個文檔近 10 MB。

create table t(id int auto_increment primary key,
               json_col json,
               name varchar(100) as (json_col->>'$.name'),
               age int as (json_col->'$.age'));
insert into t(json_col) values
(json_object('name', 'Joe', 'age', 24,
             'data', repeat('x', 10 * 1000 * 1000))),
(json_object('name', 'Sue', 'age', 32,
             'data', repeat('y', 10 * 1000 * 1000))),
(json_object('name', 'Pete', 'age', 40,
             'data', repeat('z', 10 * 1000 * 1000))),
(json_object('name', 'Jenny', 'age', 27,
             'data', repeat('w', 10 * 1000 * 1000)));
insert into t(json_col) select json_col from t;
insert into t(json_col) select json_col from t;

接下來，測試下述 SQL：update t set json_col = json_set(json_col, '$.age', age + 1);

在以下四種場景下的執(zhí)行時間：

• MySQL 5.7.36
• MySQL 8.0.27
• MySQL 8.0.27，binlog_row_value_options=PARTIAL_JSON
• MySQL 8.0.27，binlog_row_value_options=PARTIAL_JSON + binlog_row_image=MINIMAL

以 MySQL 5.7.36 的查詢時間作為基準：

• MySQL 8.0 只開啟存儲引擎層的 Partial Updates，查詢時間比 MySQL 5.7 快 1.94 倍。
• MySQL 8.0 同時開啟存儲引擎層和 binlog 中的 Partial Updates，查詢時間比 MySQL 5.7 快 4.87 倍。
• 如果在 2 的基礎(chǔ)上，同時將 binlog_row_image 設(shè)置為 MINIMAL，查詢時間更是比 MySQL 5.7 快 102.22 倍。

當(dāng)然，在生產(chǎn)環(huán)境，我們一般很少將 binlog_row_image 設(shè)置為 MINIMAL。
但即使如此，只開啟存儲引擎層和 binlog 中的 Partial Updates，查詢時間也比 MySQL 5.7 快 4.87 倍，性能提升還是比較明顯的。

五、如何對 JSON 字段創(chuàng)建索引

http://www.dbjr.com.cn/database/287839lwd.htm

六、mybatis取json類型的數(shù)據(jù)

http://www.dbjr.com.cn/program/2878405ct.htm

七、總結(jié)

• 使用longtext存儲json也可以使用函數(shù)等操作，既然mysql出了json數(shù)據(jù)類型，那我們盡量就使用json數(shù)據(jù)類型來存儲json，而且該數(shù)據(jù)類型還有數(shù)據(jù)校驗。
• Mysql提供了大量的json相關(guān)函數(shù)，基于此，我們針對于一些需求完全可以在sql層面使用函數(shù)解決問題，而不需要將數(shù)據(jù)拿到業(yè)務(wù)層，然后通過業(yè)務(wù)代碼來解決問題。
• 盡量使用8.0以上的mysql來使用json數(shù)據(jù)類型存儲json
• mysql提供了JSON_SET()、JSON_REPLACE()或JSON_REMOVE()三個函數(shù)可以進行值部分更新。其效率是5.7版本的5倍！雖然5.7版本也有這幾個函數(shù)，但是并不是部分更新！
• 涉及到根據(jù)json當(dāng)中某個字段查詢，我們可以通過虛擬列進行建立索引。同時在MySQL 8.0.17及以后的版本中，InnoDB存儲引擎支持JSON數(shù)組上的多值索引。
• 假如使用的是mybatis框架，如果圖省事可以直接使用JSONObject來接受前端的值，也可以作為返回值使用，當(dāng)然也可以自定義json的Java對象，但是都需要設(shè)置typeHandler，好處是中間不會出現(xiàn)轉(zhuǎn)義問題，而使用String不需要設(shè)置typeHandler，但是會存在轉(zhuǎn)義的問題。

 到此這篇關(guān)于Mysql存儲json格式的實現(xiàn)的文章就介紹到這了,更多相關(guān)Mysql存儲json格式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論