小編給大家分享一下MySQL的JSON 數(shù)據(jù)類型有哪些，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
專注于為中小企業(yè)提供成都網(wǎng)站制作、網(wǎng)站建設(shè)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)靈寶免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了成百上千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。
MySQL 5.7 增加了 JSON 數(shù)據(jù)類型的支持，在之前如果要存儲 JSON 類型的數(shù)據(jù)的話我們只能自己做 JSON.stringify() 和 JSON.parse() 的操作，而且沒辦法針對 JSON 內(nèi)的數(shù)據(jù)進(jìn)行查詢操作，所有的操作必須讀取出來 parse 之后進(jìn)行，非常的麻煩。原生的 JSON 數(shù)據(jù)類型支持之后，我們就可以直接對 JSON 進(jìn)行數(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ù)操作來講講怎么進(jìn)行 JSON 數(shù)據(jù)類型的操作。
添加數(shù)據(jù)這塊是比較簡單，不過需要理解 MySQL 對 JSON 的存儲本質(zhì)上還是字符串的存儲操作。只是當(dāng)定義為 JSON 類型之后內(nèi)部會對數(shù)據(jù)再進(jìn)行一些索引的創(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ù)進(jìn)行創(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 都是非常麻煩的一件事，遠(yuǎn)沒有 JS 原生對象來的好用。所以在 think-model 模塊中我們增加了 JSON 數(shù)據(jù)類型的數(shù)據(jù)自動進(jìn)行 JSON.stringify() 的支持，所以直接傳入 JS 對象數(shù)據(jù)即可。
由于數(shù)據(jù)的自動序列化和解析是根據(jù)字段類型來做的，為了不影響已運(yùn)行的項目，需要在模塊中配置 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ù)制代碼為了更好的支持 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() 操作。我們在這里不對每個方法進(jìn)行逐個的舉例描述，僅提出一些場景舉例應(yīng)該怎么操作。
舉這個例子就是想告訴下大家怎么獲取 JSON 數(shù)據(jù)中的部分內(nèi)容，并按照正常的表字段進(jìn)行返回。這塊可以使用 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ù)組元素。
不一樣的地方在于需要通過 $ 表示本身，這個也比較好理解。另外就是可以使用 * 和 ** 兩個通配符，比如 .* 表示當(dāng)前層級的所有成員的值，[*] 則表示當(dāng)前數(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ù)組進(jìn)行查詢搜索。其中需要注意的是 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ù)制代碼MySQL 提供的 JSON 操作函數(shù)中，和修改操作相關(guān)的方法主要如下：
JSON_APPEND/JSON_ARRAY_APPEND：這兩個名字是同一個功能的兩種叫法，MySQL 5.7 的時候?yàn)?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：當(dāng)路徑不存在才插入JSON_REPLACE：當(dāng)路徑存在才替換JSON_SET：不管路徑是否存在JSON_REMOVE：移除指定路徑的數(shù)據(jù)。使用方法 JSON_REMOVE(json_doc, path[, path] ...)由于 JSON_INSERT, JSON_REPLACE, JSON_SET 和 JSON_REMOVE 幾個方法支持屬性和數(shù)組的操作，所以前兩個 JSON_ARRAY 方法用的會稍微少一點(diǎn)。下面我們根據(jù)之前的數(shù)據(jù)繼續(xù)舉幾個實(shí)例看看。
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 條件表達(dá)式處理，對應(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ù)組進(jìn)行操作的時候還是要比 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ù)制代碼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ù)組進(jìn)行操作很多時候我們可能就是想刪值，但是卻不知道這個值的 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)換，所以匹配出來的路徑字符串需要進(jìn)行 JSON_UNQUOTE() 操作。另外還有非常重要的一點(diǎn)是 JSON_SEARCH 無法對數(shù)值類型數(shù)據(jù)進(jìn)行查找，也不知道這個是 Bug 還是 Feature。這也是為什么我沒有使用 score 來進(jìn)行舉例而是換成了 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'); // 刪除分?jǐn)?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ù)排序進(jìn)行輸出。一般情況下肯定是給每條數(shù)據(jù)增加一個 order 字段來記錄該條數(shù)據(jù)的排序情況。但是由于有著批量操作，在這種時候使用單字段去存儲會顯得特別麻煩。在服務(wù)端同事的建議下，我采取了使用 JSON 字段存儲數(shù)組的情況來解決這個問題。
也因?yàn)檫@樣了解了一下 MySQL 對 JSON 的支持情況，同時將 think-model 做了一些優(yōu)化，對 JSON 數(shù)據(jù)類型增加了支持。由于大部分 JSON 操作需要通過內(nèi)置的函數(shù)來操作，這個本身是可以通過 EXP 條件表達(dá)式來完成的。所以只需要對 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() 不支持?jǐn)?shù)字查找這個也是一個不小的坑了。
以上是MySQL的JSON 數(shù)據(jù)類型有哪些的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！
