一、前言

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

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

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

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

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

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

插入數(shù)據(jù)的時(shí)候自動(dòng)驗(yàn)證數(shù)據(jù)是否是標(biāo)準(zhǔn)的json數(shù)據(jù)格式，如果不是會(huì)報(bào)異常！假如使用longtext存儲(chǔ)json并不會(huì)有這種校驗(yàn)。

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

注意：

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

二、什么是 JSON

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

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

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

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

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

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

空值：null。

一些合法的JSON的實(shí)例：

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

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

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

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

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

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

要實(shí)現(xiàn)從JS對(duì)象轉(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"}'

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

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

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

http://chabaoo.cn/database/2878197v4.htm

四、JSON值部分更新

4.1.使用 Partial Updates 的條件

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

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

JSON_STORAGE_FREE（更新后釋放的空間）

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

返回值：

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

創(chuàng)建測(cè)試表

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 中開(kāi)啟 Partial Updates

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

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

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

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

首先構(gòu)造測(cè)試數(shù)據(jù)，t 表一共有 16 個(gè)文檔，每個(gè)文檔近 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;

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

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

• 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 的查詢時(shí)間作為基準(zhǔn)：

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

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

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

http://chabaoo.cn/database/287839lwd.htm

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

http://chabaoo.cn/program/2878405ct.htm

七、總結(jié)

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

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

最新評(píng)論