MySQL的JSON 數(shù)據(jù)類型有哪些

126次閱讀

共計 9729 個字符，預(yù)計需要花費 25 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

丸趣 TV 小編給大家分享一下 MySQL 的 JSON 數(shù)據(jù)類型有哪些，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

MySQL 5.7 增加了 JSON 數(shù)據(jù)類型的支持，在之前如果要存儲 JSON 類型的數(shù)據(jù)的話我們只能自己做 JSON.stringify() 和 JSON.parse() 的操作，而且沒辦法針對 JSON 內(nèi)的數(shù)據(jù)進行查詢操作，所有的操作必須讀取出來 parse 之后進行，非常的麻煩。原生的 JSON 數(shù)據(jù)類型支持之后，我們就可以直接對 JSON 進行數(shù)據(jù)查詢和修改等操作了，較之前會方便非常多。

為了方便演示我先創(chuàng)建一個 user 表，其中 info 字段用來存儲用戶的基礎(chǔ)信息。要將字段定義成 JSON 類型數(shù)據(jù)非常簡單，直接字段名后接 JSON 即可。

CREATE TABLE user (id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(30) NOT NULL,
 info JSON
); 復(fù)制代碼

表創(chuàng)建成功之后我們就按照經(jīng)典的 CRUD 數(shù)據(jù)操作來講講怎么進行 JSON 數(shù)據(jù)類型的操作。

添加數(shù)據(jù)

添加數(shù)據(jù)這塊是比較簡單，不過需要理解 MySQL 對 JSON 的存儲本質(zhì)上還是字符串的存儲操作。只是當定義為 JSON 類型之后內(nèi)部會對數(shù)據(jù)再進行一些索引的創(chuàng)建方便后續(xù)的操作而已。所以添加 JSON 數(shù)據(jù)的時候需要使用字符串包裝。

mysql INSERT INTO user (`name`, `info`) VALUES(lilei , { sex : male , age : 18, hobby : [ basketball , football], score : [85, 90, 100]} 
Query OK, 1 row affected (0.00 sec) 復(fù)制代碼

除了自己拼 JSON 之外，你還可以調(diào)用 MySQL 的 JSON 創(chuàng)建函數(shù)進行創(chuàng)建。

JSON_OBJECT：快速創(chuàng)建 JSON 對象，奇數(shù)列為 key，偶數(shù)列為 value，使用方法 JSON_OBJECT(key,value,key1,value1)JSON_ARRAY：快速創(chuàng)建 JSON 數(shù)組，使用方法 JSON_ARRAY(item0, item1, item2)

mysql INSERT INTO user (`name`, `info`) VALUES( hanmeimei , JSON_OBJECT(
 - sex , female , 
 - age , 18, 
 - hobby , JSON_ARRAY(badminton , sing), 
 - score , JSON_ARRAY(90, 95, 100)
Query OK, 1 row affected (0.00 sec) 復(fù)制代碼

不過對于 JavaScript 工程師來說不管是使用字符串來寫還是使用自帶函數(shù)來創(chuàng)建 JSON 都是非常麻煩的一件事，遠沒有 JS 原生對象來的好用。所以在 think-model 模塊中我們增加了 JSON 數(shù)據(jù)類型的數(shù)據(jù)自動進行 JSON.stringify() 的支持，所以直接傳入 JS 對象數(shù)據(jù)即可。

由于數(shù)據(jù)的自動序列化和解析是根據(jù)字段類型來做的，為了不影響已運行的項目，需要在模塊中配置 jsonFormat: true 才能開啟這項功能。

//adapter.jsconst MySQL = require( think-model-mysql exports.model = { type: mysql , mysql: { handle: MySQL,
 ... jsonFormat: true
}; 復(fù)制代碼

//user.jsmodule.exports = class extends think.Controller {async indexAction() {const userId = await this.model( user).add({name: lilei , info: { sex: male , age: 16, hobby: [ basketball , football], score: [85, 90, 100]
 }); return this.success(userId);
} 復(fù)制代碼

下面讓我們來看看最終存儲到數(shù)據(jù)庫中的數(shù)據(jù)是什么樣的

mysql SELECT * FROM `user`;
+----+-----------+-----------------------------------------------------------------------------------------+
| id | name | info |
+----+-----------+-----------------------------------------------------------------------------------------+
| 1 | lilei | {age : 18, sex : male , hobby : [ basketball , football], score : [85, 90, 100]} |
| 2 | hanmeimei | {age : 18, sex : female , hobby : [ badminton , sing], score : [90, 95, 100]} |
+----+-----------+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec) 復(fù)制代碼

查詢數(shù)據(jù)

為了更好的支持 JSON 數(shù)據(jù)的操作，MySQL 提供了一些 JSON 數(shù)據(jù)操作類的方法。和查詢操作相關(guān)的方法主要如下：

JSON_EXTRACT()：根據(jù) Path 獲取部分 JSON 數(shù)據(jù)，使用方法 JSON_EXTRACT(json_doc, path[, path] …)-：JSON_EXTRACT() 的等價寫法 -：JSON_EXTRACT() 和 JSON_UNQUOTE() 的等價寫法 JSON_CONTAINS()：查詢 JSON 數(shù)據(jù)是否在指定 Path 包含指定的數(shù)據(jù)，包含則返回 1，否則返回 0。使用方法 JSON_CONTAINS(json_doc, val[, path])JSON_CONTAINS_PATH()：查詢是否存在指定路徑，存在則返回 1，否則返回 0。one_or_all 只能取值 one 或 all，one 表示只要有一個存在即可，all 表示所有的都存在才行。使用方法 JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)JSON_KEYS()：獲取 JSON 數(shù)據(jù)在指定路徑下的所有鍵值。使用方法 JSON_KEYS(json_doc[, path])，類似 JavaScript 中的 Object.keys() 方法。JSON_SEARCH()：查詢包含指定字符串的 Paths，并作為一個 JSON Array 返回。查詢的字符串可以用 LIKE 里的 % 或 _ 匹配。使用方法 JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])，類似 JavaScript 中的 findIndex() 操作。

我們在這里不對每個方法進行逐個的舉例描述，僅提出一些場景舉例應(yīng)該怎么操作。

返回用戶的年齡和性別

舉這個例子就是想告訴下大家怎么獲取 JSON 數(shù)據(jù)中的部分內(nèi)容，并按照正常的表字段進行返回。這塊可以使用 JSON_EXTRACT 或者等價的 – 操作都可以。其中根據(jù)例子可以看到 sex 返回的數(shù)據(jù)都帶有引號，這個時候可以使用 JSON_UNQUOTE() 或者直接使用 – 就可以把引號去掉了。

mysql SELECT `name`, JSON_EXTRACT(`info`, $.age) as `age`, `info`- $.sex as sex FROM `user`;
+-----------+------+----------+
| name | age | sex |
+-----------+------+----------+
| lilei | 18 | male |
| hanmeimei | 16 | female |
+-----------+------+----------+
2 rows in set (0.00 sec) 復(fù)制代碼

這里我們第一次接觸到了 Path 的寫法，MySQL 通過這種字符串的 Path 描述幫助我們映射到對應(yīng)的數(shù)據(jù)。和 JavaScript 中對象的操作比較類似，通過 . 獲取下一級的屬性，通過 [] 獲取數(shù)組元素。

不一樣的地方在于需要通過 $ 表示本身，這個也比較好理解。另外就是可以使用 * 和 ** 兩個通配符，比如 .* 表示當前層級的所有成員的值，[*] 則表示當前數(shù)組中所有成員值。** 類似 LIKE 一樣可以接前綴和后綴，比如 a**b 表示的是以 a 開頭，b 結(jié)尾的路徑。

路徑的寫法非常簡單，后面的內(nèi)容里也會出現(xiàn)。上面的這個查詢對應(yīng)在 think-model 的寫法為

//user.jsmodule.exports = class extends think.Controller {async indexAction() {const userModel = this.model( user const field = name, JSON_EXTRACT(info, $.age) AS age, info- $.sex as sex const users = await userModel.field(field).where(1=1).select(); return this.success(users);
} 復(fù)制代碼

返回喜歡籃球的男性用戶

mysql SELECT `name` FROM `user` WHERE JSON_CONTAINS(`info`, male , $.sex) AND JSON_SEARCH(`info`, one , basketball , null, $.hobby 
+-------+
| name |
+-------+
| lilei |
+-------+
1 row in set, 1 warning (0.00 sec) 復(fù)制代碼

這個例子就是簡單的告訴大家怎么對屬性和數(shù)組進行查詢搜索。其中需要注意的是 JSON_CONTAINS() 查詢字符串由于不帶類型轉(zhuǎn)換的問題字符串需要使用加上包裹查詢，或者使用 JSON_QUOTE( male) 也可以。

如果你使用的是 MySQL 8 的話，也可以使用新增的 JSON_VALUE() 來代替 JSON_CONTAINS()，新方法的好處是會帶類型轉(zhuǎn)換，避免剛才雙引號的尷尬問題。不需要返回的路徑的話，JSON_SEARCH() 在這里也可以使用新增的 MEMBER OF 或者 JSON_OVERLAPS() 方法替換。

mysql SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, $.sex) = male AND basketball MEMBER OF(JSON_VALUE(`info`, $.hobby 
+-------+
| name |
+-------+
| lilei |
+-------+
1 row in set (0.00 sec)
mysql SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, $.sex) = male AND JSON_OVERLAPS(JSON_VALUE(`info`, $.hobby), JSON_QUOTE( basketball 
+-------+
| name |
+-------+
| lilei |
+-------+
1 row in set (0.00 sec) 復(fù)制代碼

上面的這個查詢對應(yīng)在 think-model 的寫法為

//user.jsmodule.exports = class extends think.Controller {async indexAction() {const userModel = this.model( user const where = { _string: [ JSON_CONTAINS(info, \ male\ , $.sex) , JSON_SEARCH(info, one , basketball , null, $.hobby) 
 }; const where1 = {_string: [ JSON_VALUE(`info`, $.sex) = male , basketball MEMBER OF (JSON_VALUE(`info`, $.hobby)) 
 }; const where2 = {_string: [ JSON_VALUE(`info`, $.sex) = male , JSON_OVERLAPS(JSON_VALUE(`info`, $.hobby), JSON_QUOTE(basketball)) 
 } const users = await userModel.field(name).where(where).select(); return this.success(users);
} 復(fù)制代碼

修改數(shù)據(jù)

MySQL 提供的 JSON 操作函數(shù)中，和修改操作相關(guān)的方法主要如下：

JSON_APPEND/JSON_ARRAY_APPEND：這兩個名字是同一個功能的兩種叫法，MySQL 5.7 的時候為 JSON_APPEND，MySQL 8 更新為 JSON_ARRAY_APPEND，并且之前的名字被廢棄。該方法如同字面意思，給數(shù)組添加值。使用方法 JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)JSON_ARRAY_INSERT：給數(shù)組添加值，區(qū)別于 JSON_ARRAY_APPEND() 它可以在指定位置插值。使用方法 JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)JSON_INSERT/JSON_REPLACE/JSON_SET：以上三個方法都是對 JSON 插入數(shù)據(jù)的，他們的使用方法都為 JSON_[INSERT|REPLACE|SET](json_doc, path, val[, path, val] …)，不過在插入原則上存在一些差別。JSON_INSERT：當路徑不存在才插入 JSON_REPLACE：當路徑存在才替換 JSON_SET：不管路徑是否存在 JSON_REMOVE：移除指定路徑的數(shù)據(jù)。使用方法 JSON_REMOVE(json_doc, path[, path] …)

由于 JSON_INSERT, JSON_REPLACE, JSON_SET 和 JSON_REMOVE 幾個方法支持屬性和數(shù)組的操作，所以前兩個 JSON_ARRAY 方法用的會稍微少一點。下面我們根據(jù)之前的數(shù)據(jù)繼續(xù)舉幾個實例看看。

修改用戶的年齡

mysql UPDATE `user` SET `info` = JSON_REPLACE(`info`, $.age , 20) WHERE `name` = lilei 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql SELECT JSON_VALUE(`info`, $.age) as age FROM `user` WHERE `name` = lilei 
+------+
| age |
+------+
| 20 |
+------+
1 row in set (0.00 sec) 復(fù)制代碼

JSON_INSERT 和 JSON_SET 的例子也是類似，這里就不多做演示了。對應(yīng)到 think-model 中的話，需要使用 EXP 條件表達式處理，對應(yīng)的寫法為

//user.jsmodule.exports = class extends think.Controller {async indexAction() {const userModel = this.model( user await userModel.where({name: lilei}).update({info: [ exp , JSON_REPLACE(info, $.age , 20) ]
 }); return this.success();} 復(fù)制代碼

修改用戶的愛好

mysql UPDATE `user` SET `info` = JSON_ARRAY_APPEND(`info`, $.hobby , badminton) WHERE `name` = lilei 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql SELECT JSON_VALUE(`info`, $.hobby) as hobby FROM `user` WHERE `name` = lilei 
+-----------------------------------------+
| hobby |
+-----------------------------------------+
| [basketball , football , badminton] |
+-----------------------------------------+
1 row in set (0.00 sec) 復(fù)制代碼

JSON_ARRAY_APPEND 在對數(shù)組進行操作的時候還是要比 JSON_INSERT 之類的方便的，起碼你不需要知道數(shù)組的長度。對應(yīng)到 think-model 的寫法為

//user.jsmodule.exports = class extends think.Controller {async indexAction() {const userModel = this.model( user await userModel.where({name: lilei}).update({info: [ exp , JSON_ARRAY_APPEND(info, $.hobby , badminton) ]
 }); return this.success();} 復(fù)制代碼

刪除用戶的分數(shù)

mysql UPDATE `user` SET `info` = JSON_REMOVE(`info`, $.score[0] ) WHERE `name` = lilei 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql SELECT `name`, JSON_VALUE(`info`, $.score) as score FROM `user` WHERE `name` = lilei 
+-------+-----------+
| name | score |
+-------+-----------+
| lilei | [90, 100] |
+-------+-----------+
1 row in set (0.00 sec) 復(fù)制代碼

刪除這塊和之前修改操作類似，沒有什么太多需要說的。但是對數(shù)組進行操作很多時候我們可能就是想刪值，但是卻不知道這個值的 Path 是什么。這個時候就需要利用之前講到的 JSON_SEARCH() 方法，它是根據(jù)值去查找路徑的。比如說我們要刪除 lilei 興趣中的 badminton 選項可以這么寫。

mysql UPDATE `user` SET `info` = JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, one , badminton))) WHERE `name` = lilei 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql SELECT JSON_VALUE(`info`, $.hobby) as hobby FROM `user` WHERE `name` = lilei 
+----------------------------+
| hobby |
+----------------------------+
| [basketball , football] |
+----------------------------+
1 row in set (0.00 sec) 復(fù)制代碼

這里需要注意由于 JSON_SEARCH 不會做類型轉(zhuǎn)換，所以匹配出來的路徑字符串需要進行 JSON_UNQUOTE() 操作。另外還有非常重要的一點是 JSON_SEARCH 無法對數(shù)值類型數(shù)據(jù)進行查找，也不知道這個是 Bug 還是 Feature。這也是為什么我沒有使用 score 來進行舉例而是換成了 hobby 的原因。如果數(shù)值類型的話目前只能取出來在代碼中處理了。

mysql SELECT JSON_VALUE(`info`, $.score) FROM `user` WHERE `name` = lilei 
+-------------------------------+
| JSON_VALUE(`info`, $.score) |
+-------------------------------+
| [90, 100] |
+-------------------------------+
1 row in set (0.00 sec)
mysql SELECT JSON_SEARCH(`info`, one , 90, null, $.score) FROM `user` WHERE `name` = lilei 
+-------------------------------------------------+
| JSON_SEARCH(`info`, one , 90, null, $.score) |
+-------------------------------------------------+
| NULL |
+-------------------------------------------------+
1 row in set (0.00 sec) 復(fù)制代碼

以上對應(yīng)到 think-model 的寫法為

//user.jsmodule.exports = class extends think.Controller {async indexAction() { const userModel = this.model( user // 刪除分數(shù)
 await userModel.where({name: lilei}).update({info: [ exp , JSON_REMOVE(info, $.score[0] ) ]
 }); // 刪除興趣
 await userModel.where({name: lilei}).update({info: [ exp , JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, one , badminton))) ]
 }); 
 return this.success();} 復(fù)制代碼

后記

由于最近有一個需求，有一堆數(shù)據(jù)，要記錄這堆數(shù)據(jù)的排序情況，方便根據(jù)排序進行輸出。一般情況下肯定是給每條數(shù)據(jù)增加一個 order 字段來記錄該條數(shù)據(jù)的排序情況。但是由于有著批量操作，在這種時候使用單字段去存儲會顯得特別麻煩。在服務(wù)端同事的建議下，我采取了使用 JSON 字段存儲數(shù)組的情況來解決這個問題。

也因為這樣了解了一下 MySQL 對 JSON 的支持情況，同時將 think-model 做了一些優(yōu)化，對 JSON 數(shù)據(jù)類型增加了支持。由于大部分 JSON 操作需要通過內(nèi)置的函數(shù)來操作，這個本身是可以通過 EXP 條件表達式來完成的。所以只需要對 JSON 數(shù)據(jù)的添加和查詢做好優(yōu)化就可以了。

整體來看，配合提供的 JSON 操作函數(shù)，MySQL 對 JSON 的支持完成一些日常的需求還是沒有問題的。除了作為 WHERE 條件以及查詢字段之外，其它的 ORDER, GROUP, JOIN 等操作也都是支持 JSON 數(shù)據(jù)的。

不過對比 MongoDB 這種天生支持 JSON 的話，在操作性上還是要麻煩許多。特別是在類型轉(zhuǎn)換這塊，使用一段時間后發(fā)現(xiàn)非常容易掉坑。什么時候會帶引號，什么時候會不帶引號，什么時候需要引號，什么時候不需要引號，這些都容易讓新手發(fā)憷。另外 JSON_SEARCH() 不支持數(shù)字查找這個也是一個不小的坑了。

以上是 MySQL 的 JSON 數(shù)據(jù)類型有哪些的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

向 AI 問一下細節(jié)

丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！

正文完