mysql視頻教程欄目介紹JSON數據類型。
MySQL 5.7 增加了 JSON 數據類型的支持，在之前如果要存儲 JSON 類型的數據的話我們只能自己做 JSON.stringify() 和 JSON.parse() 的操作，而且沒辦法針對 JSON 內的數據進行查詢操作，所有的操作必須讀取出來 parse 之后進行，非常的麻煩。原生的 JSON 數據類型支持之后，我們就可以直接對 JSON 進行數據查詢和修改等操作了，較之前會方便非常多。
為了方便演示我先創建一個 user 表，其中 info 字段用來存儲用戶的基礎信息。要將字段定義成 JSON 類型數據非常簡單，直接字段名后接 JSON 即可。
CREATE TABLE user ( id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, info JSON );復制代碼
表創建成功之后我們就按照經典的 CRUD 數據操作來講講怎么進行 JSON 數據類型的操作。
添加數據
添加數據這塊是比較簡單，不過需要理解 MySQL 對 JSON 的存儲本質上還是字符串的存儲操作。只是當定義為 JSON 類型之后內部會對數據再進行一些索引的創建方便后續的操作而已。所以添加 JSON 數據的時候需要使用字符串包裝。
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)復制代碼
除了自己拼 JSON 之外，你還可以調用 MySQL 的 JSON 創建函數進行創建。
- JSON_OBJECT：快速創建 JSON 對象，奇數列為 key，偶數列為 value，使用方法 JSON_OBJECT(key,value,key1,value1)
- JSON_ARRAY：快速創建 JSON 數組，使用方法 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)復制代碼
不過對于 JavaScript 工程師來說不管是使用字符串來寫還是使用自帶函數來創建 JSON 都是非常麻煩的一件事，遠沒有 JS 原生對象來的好用。所以在 think-model 模塊中我們增加了 JSON 數據類型的數據自動進行 JSON.stringify() 的支持，所以直接傳入 JS 對象數據即可。
由于數據的自動序列化和解析是根據字段類型來做的，為了不影響已運行的項目，需要在模塊中配置 jsonFormat: true 才能開啟這項功能。
//adapter.jsconst MySQL = require('think-model-mysql');exports.model = { type: 'mysql', mysql: { handle: MySQL, ... jsonFormat: true } };復制代碼
//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); } }復制代碼
下面讓我們來看看最終存儲到數據庫中的數據是什么樣的
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)復制代碼
查詢數據
為了更好的支持 JSON 數據的操作，MySQL 提供了一些 JSON 數據操作類的方法。和查詢操作相關的方法主要如下：
- JSON_EXTRACT()：根據 Path 獲取部分 JSON 數據，使用方法 JSON_EXTRACT(json_doc, path[, path] …)
- ->：JSON_EXTRACT() 的等價寫法
- ->>：JSON_EXTRACT() 和 JSON_UNQUOTE() 的等價寫法
- JSON_CONTAINS()：查詢 JSON 數據是否在指定 Path 包含指定的數據，包含則返回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 數據在指定路徑下的所有鍵值。使用方法 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() 操作。
我們在這里不對每個方法進行逐個的舉例描述，僅提出一些場景舉例應該怎么操作。
返回用戶的年齡和性別
舉這個例子就是想告訴下大家怎么獲取 JSON 數據中的部分內容，并按照正常的表字段進行返回。這塊可以使用 JSON_EXTRACT 或者等價的 -> 操作都可以。其中根據例子可以看到 sex 返回的數據都帶有引號，這個時候可以使用 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)復制代碼
這里我們第一次接觸到了 Path 的寫法，MySQL 通過這種字符串的 Path 描述幫助我們映射到對應的數據。和 JavaScript 中對象的操作比較類似，通過 . 獲取下一級的屬性，通過 [] 獲取數組元素。
不一樣的地方在于需要通過 $ 表示本身，這個也比較好理解。另外就是可以使用 * 和 ** 兩個通配符，比如 .* 表示當前層級的所有成員的值，[*] 則表示當前數組中所有成員值。** 類似 LIKE 一樣可以接前綴和后綴，比如 a**b 表示的是以 a 開頭，b結尾的路徑。
路徑的寫法非常簡單，后面的內容里也會出現。上面的這個查詢對應在 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); } }復制代碼
返回喜歡籃球的男性用戶
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)復制代碼
這個例子就是簡單的告訴大家怎么對屬性和數組進行查詢搜索。其中需要注意的是 JSON_CONTAINS() 查詢字符串由于不帶類型轉換的問題字符串需要使用加上 “” 包裹查詢，或者使用 JSON_QUOTE(‘male’) 也可以。
如果你使用的是 MySQL 8 的話，也可以使用新增的 JSON_VALUE() 來代替 JSON_CONTAINS()，新方法的好處是會帶類型轉換，避免剛才雙引號的尷尬問題。不需要返回的路徑的話，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)復制代碼
上面的這個查詢對應在 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); } }復制代碼
修改數據
MySQL 提供的 JSON 操作函數中，和修改操作相關的方法主要如下：
- JSON_APPEND/JSON_ARRAY_APPEND：這兩個名字是同一個功能的兩種叫法，MySQL 5.7 的時候為 JSON_APPEND，MySQL 8 更新為 JSON_ARRAY_APPEND，并且之前的名字被廢棄。該方法如同字面意思，給數組添加值。使用方法 JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
- JSON_ARRAY_INSERT：給數組添加值，區別于 JSON_ARRAY_APPEND() 它可以在指定位置插值。使用方法 JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)
- JSON_INSERT/JSON_REPLACE/JSON_SET：以上三個方法都是對 JSON 插入數據的，他們的使用方法都為 JSON_[INSERT|REPLACE|SET](json_doc, path, val[, path, val] …)，不過在插入原則上存在一些差別。
- JSON_INSERT：當路徑不存在才插入
- JSON_REPLACE：當路徑存在才替換
- JSON_SET：不管路徑是否存在
- JSON_REMOVE：移除指定路徑的數據。使用方法 JSON_REMOVE(json_doc, path[, path] …)
由于 JSON_INSERT, JSON_REPLACE, JSON_SET 和 JSON_REMOVE 幾個方法支持屬性和數組的操作，所以前兩個 JSON_ARRAY 方法用的會稍微少一點。下面我們根據之前的數據繼續舉幾個實例看看。
修改用戶的年齡
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)復制代碼
JSON_INSERT 和 JSON_SET 的例子也是類似，這里就不多做演示了。對應到 think-model 中的話，需要使用 EXP 條件表達式處理，對應的寫法為
//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(); } }復制代碼
修改用戶的愛好
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)復制代碼
JSON_ARRAY_APPEND 在對數組進行操作的時候還是要比 JSON_INSERT 之類的方便的，起碼你不需要知道數組的長度。對應到 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(); } }復制代碼
刪除用戶的分數
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)復制代碼
刪除這塊和之前修改操作類似，沒有什么太多需要說的。但是對數組進行操作很多時候我們可能就是想刪值，但是卻不知道這個值的 Path 是什么。這個時候就需要利用之前講到的 JSON_SEARCH() 方法，它是根據值去查找路徑的。比如說我們要刪除 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)復制代碼
這里需要注意由于 JSON_SEARCH 不會做類型轉換，所以匹配出來的路徑字符串需要進行 JSON_UNQUOTE() 操作。另外還有非常重要的一點是 JSON_SEARCH 無法對數值類型數據進行查找，也不知道這個是 Bug 還是 Feature。這也是為什么我沒有使用 score 來進行舉例而是換成了 hobby 的原因。如果數值類型的話目前只能取出來在代碼中處理了。
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)復制代碼
以上對應到 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_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(); } }復制代碼
后記
由于最近有一個需求，有一堆數據，要記錄這堆數據的排序情況，方便根據排序進行輸出。一般情況下肯定是給每條數據增加一個 order 字段來記錄該條數據的排序情況。但是由于有著批量操作，在這種時候使用單字段去存儲會顯得特別麻煩。在服務端同事的建議下，我采取了使用 JSON 字段存儲數組的情況來解決這個問題。
也因為這樣了解了一下 MySQL 對 JSON 的支持情況，同時將 think-model 做了一些優化，對 JSON 數據類型增加了支持。由于大部分 JSON 操作需要通過內置的函數來操作，這個本身是可以通過 EXP 條件表達式來完成的。所以只需要對 JSON 數據的添加和查詢做好優化就可以了。
整體來看，配合提供的 JSON 操作函數，MySQL 對 JSON 的支持完成一些日常的需求還是沒有問題的。除了作為 WHERE 條件以及查詢字段之外，其它的 ORDER, GROUP, JOIN 等操作也都是支持 JSON 數據的。
不過對比 MongoDB 這種天生支持 JSON 的話，在操作性上還是要麻煩許多。特別是在類型轉換這塊，使用一段時間后發現非常容易掉坑。什么時候會帶引號，什么時候會不帶引號，什么時候需要引號，什么時候不需要引號，這些都容易讓新手發憷。另外 JSON_SEARCH() 不支持數字查找這個也是一個不小的坑了。
相關免費學習推薦：mysql視頻教程
