mysql在2015年的5.7以后，InnoDB存儲(chǔ)引擎已經(jīng)開始對(duì)json數(shù)據(jù)進(jìn)行支持，對(duì)json支持有如下優(yōu)勢：

• JSON數(shù)據(jù)有效性檢查：BLOB類型無法在數(shù)據(jù)庫層做這樣的約束性檢查，如不合法會(huì)報(bào)錯(cuò)：
• 查詢性能的提升：查詢不需要遍歷所有字符串才能找到數(shù)據(jù)
• 支持索引：通過虛擬列的功能可以對(duì)JSON中的部分?jǐn)?shù)據(jù)進(jìn)行索引
• 存儲(chǔ)的容量大，近似于LongBlob與LongText（4G）

建表語句：

CREATE TABLE `employe` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(18) DEFAULT NULL,`sex` int DEFAULT NULL,`others` json DEFAULT NULL,`time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '錄入時(shí)間',PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=42334 DEFAULT CHARSET=utf8mb4;

?獲取JSON中的某個(gè)字段的風(fēng)格與alibaba中fastjson中的JSONPath類似，但是功能與之相差甚遠(yuǎn)：

• .? ? 屬性訪問，例如$.name
• $? ?根對(duì)象，即當(dāng)前json
• *? ?對(duì)象的所有屬性，如：$.* ，當(dāng)為JSONArray時(shí)，$.* 無效

??

其中支持json的主要函數(shù)如下表：或見https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

?簡單列一下常用操作，便于記憶：

• ?JSON_EXTRACT(column_name,'$.name'）返回該json列column中的name中的值
• JSON_SEARCH(others, 'one', "露露")? ?若該字段存在，返回路徑，若不存在返回null：select * from employe where JSON_SEARCH(others, 'one', "露露") is not null;//查找存在“露露”的記錄；該方法中的中間參數(shù)有“one”與“all”兩種，one返回該json中第一次找到的記錄路徑，all返回該json中所有找到的路徑，即若存在多個(gè)“露露”則返回多個(gè)露露的路徑
• JSON_TYPE(others) 判斷alias的json類型（ARRAY或者OBJECT）
• JSON_VALID(others) 判斷alias 是否是一個(gè)合法的json
• JSON_CONTAINS(others, '露露', '$.alias');
• mysql5.7.9開始增加了一種簡寫方式：column->path； select others->'$.address'? from employe? ?查詢json中的address列 類似：select JSON_EXTRACT(others, '$.address') from employe where id = 42332;

• select json_contains('{"a":1,"b":4}','{"a":1}')??結(jié)果1

  select json_contains('{"a":2,"b":1}','{"a":1}')??結(jié)果0

  select json_contains('{"a":[1,2,3],"b":1}','[1,2]','$.a')??結(jié)果1。數(shù)組包含則需要所有元素都存在。

  select json_contains('{"a":[1,2,3],"b":1}','1','$.a')? ?

• select json_contains(others, '\"露露\"','$.alias') from employe；? ? ? ? ?使用json_contains的時(shí)候，如果是字符串要用雙引號(hào)包括，如果去掉雙引號(hào)則報(bào)錯(cuò)。

虛擬列：虛擬列類似于視圖，將others字段中的address提取出來作為一個(gè)虛擬列address，當(dāng)others字段中的address發(fā)生改變時(shí)，虛擬列會(huì)同時(shí)改變。

ALTER TABLE employe ADD address varchar(128) GENERATED ALWAYS AS (json_extract(others,'$.address')) VIRTUAL;

若去掉address列的引號(hào)可用replace替換：

ALTER TABLE employe ADD address varchar(128) GENERATED ALWAYS AS (REPLACE(json_extract(others,'$.address'),"\"",'')) VIRTUAL;

可對(duì)該虛擬字段進(jìn)行查詢：

select * from employe where address="北京昌平";

?在MySQL 5.7中，支持兩種generated column，即virtual generated column和stored generated column，前者只將generated column保存在數(shù)據(jù)字典中（表的元數(shù)據(jù)），并不會(huì)將這一列數(shù)據(jù)持久化到磁盤上；后者會(huì)將generated column持久化到磁盤上，而不是每次讀取的時(shí)候計(jì)算所得。很明顯，后者存放了可以通過已有數(shù)據(jù)計(jì)算而得的數(shù)據(jù)，需要更多的磁盤空間，與virtual column相比并沒有優(yōu)勢，因此，MySQL 5.7中，不指定generated column的類型，默認(rèn)是virtual column。

?

?虛擬索引：虛擬列與普通列類似可創(chuàng)建索引，虛擬索引列也是通過傳統(tǒng)的B+樹索引即可實(shí)現(xiàn)對(duì)JSON格式部分屬性的快速查詢。使用方法是首先創(chuàng)建該虛擬列，然后在該虛擬列上創(chuàng)建索引：

alter table employe add INDEX add_index(address);

?在創(chuàng)建索引后執(zhí)行語句select * from employe where address="北京昌平";如下圖，發(fā)現(xiàn)使用到了索引：?

?

效率問題：關(guān)于效率方面，使用json報(bào)錯(cuò)或?qū)son轉(zhuǎn)化為多表存儲(chǔ)的效率對(duì)比參考https://www.cnblogs.com/lkpnotice/p/6903187.html

?

轉(zhuǎn)載于:https://www.cnblogs.com/smallProgrammer1/p/10091950.html

總結(jié)

以上是生活随笔為你收集整理的mysql与Json学习总结的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。