寫在開頭

JSON類型通常用于存儲(chǔ)非結(jié)構(gòu)化的數(shù)據(jù)或者一些可變的數(shù)據(jù)，比如存儲(chǔ)日志、配置信息等。我們有時(shí)候需要將類似的信息保存到MySQL數(shù)據(jù)庫中，但是MySQL作為關(guān)系型數(shù)據(jù)庫，對(duì)于JSON這種非結(jié)構(gòu)化存儲(chǔ)存在一定的弊端

Mysql數(shù)據(jù)庫對(duì)json數(shù)據(jù)類型的支持

JSON數(shù)據(jù)類型是MySQL 5.7.8開始支持的。在此之前，只能通過字符類型（CHAR，VARCHAR 、TEXT或LONGTEXT ）來保存JSON數(shù)據(jù)。

相對(duì)字符類型，原生的 JSON 類型具有以下優(yōu)勢(shì)：

• 在插入時(shí)能自動(dòng)校驗(yàn)文檔是否滿足 JSON 格式的要求。
• 數(shù)據(jù)快速解析，MySQL提供了多種函數(shù)，可以快速讀取和操作JSON格式的數(shù)據(jù)  

JSON數(shù)據(jù)的兩種有效格式

JSON數(shù)組，包含在[]中的一系列值，例如: [1, 2, 'a', 'b']  

JSON對(duì)象，包含在{}中的鍵值對(duì)，鍵和值之間用冒號(hào)分隔（鍵必須是字符串），鍵值對(duì)之間以逗號(hào)分隔，如: {'key1':'value1', 'key2':'value2'}  

兩中數(shù)據(jù)格式可以相互嵌套。

了解MySQL中JSON數(shù)據(jù)格式

測(cè)試數(shù)據(jù)

#DDL  
create table json_demo(
id_ int primary key,
val_ json);  
#插入數(shù)據(jù)
insert into json_demo values(1, '[1,2,"a","b"]');
insert into json_demo values(2, '{"key1":1, "key2":2, "key1":"value3"}');  
#查詢
select * from json_demo;

小貼士在JSON對(duì)象中，鍵是不能重復(fù)的，如果出現(xiàn)相同的鍵，規(guī)范化時(shí)后值會(huì)覆蓋前值。JSON對(duì)象中重復(fù)的鍵key1保留了后出現(xiàn)的值"value3"。（MySQL 8.0.3之前保留先出現(xiàn)的值）。

json_array、json_object、cast函數(shù)

json_array() 函數(shù)以將列值轉(zhuǎn)換為JSON數(shù)組：

select json_array(TRUE, FALSE, NULL, 'TRUE', 'FALSE', 'NULL');

小貼士JSON數(shù)據(jù)類型會(huì)對(duì)數(shù)據(jù)進(jìn)行一些預(yù)處理，會(huì)自動(dòng)將boolean類型轉(zhuǎn)換為小寫。 這里TRUE, FALSE, NULL在規(guī)范化過程中均被轉(zhuǎn)換為小寫，而字符串'TRUE', 'FALSE', 'NULL'則保持不變

json_object() 函數(shù)可以把字符串形式的鍵值對(duì)轉(zhuǎn)換成JSON格式對(duì)象。

select json_object('key2',2, 'key1',1, 'key1',3);

小貼士返回的結(jié)果中，key1后出現(xiàn)的值3覆蓋了先出現(xiàn)的值1。
規(guī)范化后，JSON對(duì)象是按鍵排序的，本例中key1被排到的前面

由于鍵必須是字符串，即使以數(shù)字形式提供，在規(guī)范化的過程中也會(huì)被轉(zhuǎn)換為字符串：

select json_object('key2',2, 'key1',1, 3, 'key1');

cast(… as json)會(huì)根據(jù)提供的字符串格式，轉(zhuǎn)換為JSON數(shù)組或JSON對(duì)象：

select cast('[1,2,"abc"]' as json);    -- 轉(zhuǎn)換為JSON數(shù)組
 
select cast('{"key1":1, "key2":2, "key1":3}' as json);    -- 轉(zhuǎn)換為JSON對(duì)象

JSON數(shù)據(jù)類型的操作

使用JSON格式的最大好處是MySQL提供了一系列操作函數(shù)，可以快速對(duì)JSON類型的數(shù)據(jù)進(jìn)行查詢/更新，不必再對(duì)整個(gè)字符串進(jìn)行格式解析，在處理JSON數(shù)據(jù)時(shí)簡(jiǎn)便許多。

這是目前的測(cè)試庫中的數(shù)據(jù)：

JSON類型高頻使用函數(shù)。

使用JSON格式的最大好處是MySQL提供了一系列操作函數(shù)，可以快速對(duì)JSON類型的數(shù)據(jù)進(jìn)行查詢/更新，不必再對(duì)整個(gè)字符串進(jìn)行格式解析，在處理JSON數(shù)據(jù)時(shí)簡(jiǎn)便許多。

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

（一）： json_extract()函數(shù)

1：json_extract()函數(shù)可以從JSON格式的數(shù)據(jù)中快速取出指定值，語法如下：

• json_extract(js_array, '$[n]') 通過'$[n]'的形式取出JSON數(shù)組中編號(hào)為n的元素（編號(hào)從0開始）  
  

select id_,json_extract(val_,'$[1]') from json_demo ;

小貼士如果目標(biāo)是JSON對(duì)象，則返回null（id為2的記錄）

2：從JSON數(shù)組中取出范圍值，語法如下：

• json_extract(js_array, '$[m to n]') 通過'$[m to n]'的形式取出JSON數(shù)組中編號(hào)m到n的所有元素（編號(hào)從0開始）
  

select id_, json_extract(val_, '$[1 to 2]') from json_demo where id_ = '1';

json_extract(val, '$[1 to 2]')取出JSON數(shù)組中2~3范圍內(nèi)的元素。

3： 從JSON對(duì)象中取出單個(gè)值，語法如下：

• json_extract(js_object, '$.key') 通過'$.key'的形式取出JSON對(duì)象中鍵為key所對(duì)應(yīng)的值  
  

select id_, json_extract(val_, '$.key1') from json_demo where id_ = '2';

小貼士'和.key'還可以替換為'和.*'表示返回所有的值。
json_extract函數(shù)還有一種更簡(jiǎn)便的column->path寫法，用'->'或'->>'符號(hào)來替代json_extract()。注意符號(hào)->和->>的輸出結(jié)果差異，->的結(jié)果是帶引號(hào)的，如果想要的是不帶引號(hào)的值，使用->>即可。

select id_,val_->'$[1]' from json_demo where id_ = '1';  
select id_, val_->'$[1 to 2]' from json_demo where id_ = '1';  
select id_, val_->'$.key1' from json_demo where id_ = '2';

（二）： json_keys()函數(shù)

json_extract()函數(shù)返回的都是JSON對(duì)象的值，如果想返回鍵，則需要用json_keys()函數(shù)。json_keys()會(huì)以JSON數(shù)組的形式返回JSON對(duì)象中頂層的鍵，即將所有的鍵組成一個(gè)數(shù)組返回。語法如下：

• json_keys(json_doc [, path])，當(dāng)提供path參數(shù)時(shí)（JSON對(duì)象嵌套），會(huì)返回指定path處元素的頂層鍵。  
  

select json_keys('{"key1": "value1", "key2": {"a":"b"}}');

這里的頂層鍵有'key1','key2'，其中'key2'的值又是一個(gè)JSON對(duì)象（嵌套）,當(dāng)沒有指定path參數(shù)，這里只返回了頂層的鍵。

如果要返回key2值中的鍵，需要提供path參數(shù)'$.key2'

select json_keys('{"key1": "value3", "key2": {"a":"b"}}', '$.key2');

JSON數(shù)據(jù)新增

JSON對(duì)象的中的元素新增，可以通過json_arry_append()，json_array_insert()或json_insert()函數(shù)來完成。

這是當(dāng)前的測(cè)試數(shù)據(jù)

（一）：json_array_append()函數(shù)

josn_arry_append() 會(huì)在JSON數(shù)組指定的位置添加新的數(shù)據(jù)，新增的數(shù)據(jù)與原位置的數(shù)據(jù)會(huì)合并為新的JSON數(shù)組（依然算1個(gè)元素），不會(huì)改變?cè)璊SON數(shù)據(jù)的元素個(gè)數(shù)。語法如下：

• json_arry_append(json_doc, path, val [,path, val] …)  
  

update json_demo set val_ = json_array_append(val_,'$[0]','x', '$[3]','y') where id_=1;
# 在JSON數(shù)組第一個(gè)元素中增加一個(gè)'x'，第三個(gè)元素中增加一個(gè)'y'，數(shù)組的元素依然是4個(gè)
update json_demo set val_=json_array_append(val_,'$.key2','y') where id_=2;
#在JSON對(duì)象鍵key2的值中附件一個(gè)'y'

（二）： json_array_insert()函數(shù)

josn_arry_insert() 會(huì)在JSON數(shù)組指定的位置添加新的數(shù)據(jù)，與json_array_append()不同的是，新增的數(shù)據(jù)會(huì)作為一個(gè)獨(dú)立的元素，此函數(shù)會(huì)改變JSON數(shù)組中元素的個(gè)數(shù)，函數(shù)語法為：

• json_arry_insert(json_doc, path, val [,path, val] …)   
  

update json_demo set val_=json_array_insert(val_,'$[0]','x', '$[3]','y', '$[100]','z') where id_=1;
# 插入了3個(gè)元素，JSON數(shù)組的元素由4個(gè)增加至7個(gè)
# 第一個(gè)元素插入指定位置后，從這個(gè)位置開始，所有元素向后移動(dòng)一位，這會(huì)改變后續(xù)元素編號(hào)，并影響后續(xù)的插入位置
# '$[0]'位置插入'x'后，所有元素后移一位，"a"的位置由'$[2]'變成了'$[3]'，因此函數(shù)第二個(gè)插入'$[3]'是插在"a"的前面，而不是原'$[3]'元素"b"的前面
# 函數(shù)第三個(gè)元素'$[100]'，超出了數(shù)組長(zhǎng)度，因此附加在數(shù)組的最后

執(zhí)行SQL后的數(shù)據(jù)：

（三）： json_insert()函數(shù)

json_insert() 函數(shù)可以對(duì)JSON數(shù)組或JSON對(duì)象新增元素，根據(jù)給定的path，如果元素不存在，則進(jìn)行新增，如果元素已存在，則忽略，不做任何操作，即只新增不更新。語法為：

• json_insert(json_doc, path1, val1, [path2, val2 …])
  

update json_demo set val_=json_insert(val_,'$[0]','x','$[7]','y') where id_=1;
#對(duì)JSON數(shù)組操作時(shí)，第一個(gè)path '$[0]'，指定插入'x'，但因'$[0]'已存在，因此忽略
#第二個(gè)path '$[4]'，指定插入'y'，由于原數(shù)組只有4個(gè)元素，最大編號(hào)只到'$[3]'，新增成功

執(zhí)行SQL后的數(shù)據(jù)：

JSON數(shù)據(jù)更新

JSON數(shù)據(jù)的更新，你可以把JSON作為一個(gè)字符串，更新完后再整體賦值回去，但這種方法在JSON對(duì)象較大的時(shí)候可能效率較低，且解析成本也高。

這是當(dāng)前的測(cè)試數(shù)據(jù)

（一）： json_replace()函數(shù)

json_replace() 函數(shù)可以對(duì)JSON數(shù)據(jù)進(jìn)行原地（in-place update）更新，即用新的值替換舊值，此函數(shù)僅對(duì)已存在的值進(jìn)行更新，對(duì)不存在的值直接忽略，即只更新不新增。語法為：

•  json_replace(json_doc, path1, new_val1, [path2, new_val2 …])     
  

update json_demo set val_=json_replace(val_,'$[0]','x','$[1]','y','$[5]','z') where id_=1;
#josn_replace函數(shù)通過'$[0]'和'$[1]'指定第1，2個(gè)元素
#這里$[5]指定更新了一個(gè)不存在的元素，因此忽略

執(zhí)行后數(shù)據(jù)：

（二）： json_set()函數(shù)

如果需要同時(shí)進(jìn)行更新和新增（例如數(shù)據(jù)同步），利用json_set() 函數(shù)可以同時(shí)完成對(duì)數(shù)據(jù)的更新和新增，對(duì)于已存在的元素更新，不存在的元素新增，語法為：

•  json_set(json_doc, path1, new_val1, [path2, new_val2 …])
  

update json_demo set val_=json_set(val_,'$[0]','0','$[1]','1','$[5]','5') where id_=1;
#第一個(gè)path參數(shù)'$[0]'，匹配數(shù)組中第一個(gè)元素，將'x'更新為'0'
#第二個(gè)path參數(shù)'$[1]'，匹配數(shù)組中第二個(gè)元素，將'y'更新為'1'
#第三個(gè)path參數(shù)'$[100]'，匹配數(shù)組中第100個(gè)元素，由于不存在，新增到數(shù)組最后

執(zhí)行后數(shù)據(jù)：

JSON數(shù)據(jù)刪除

這是當(dāng)前的測(cè)試數(shù)據(jù)

（一）： json_remove()函數(shù)

json_remove() 函數(shù)從JSON數(shù)據(jù)中刪除指定的元素，語法為：

• json_remove(json_doc, path1 [,path2 …])
  

select json_remove(val_,'$[1]','$[2]') from json_demo where id_=1;
#這里指定刪除了第2，第3個(gè)元素，對(duì)應(yīng)位置是'$[1]','$[2]'
#注意在刪除'$[1]'位置的元素后，所有后續(xù)元素編號(hào)都向前移動(dòng)1位，因此'$[2]'刪除的是新'$[3]'位置的元素"b"，而不是原'$[3]'位置的元素"a"

執(zhí)行后數(shù)據(jù)：

判斷元素否存在

某些時(shí)候，我們并不需要提取數(shù)值，而僅僅想知道某個(gè)元素是否存在（例如作判斷條件時(shí)）。

當(dāng)前的測(cè)試數(shù)據(jù)

（一）： json_contains_path()函數(shù)

josn_contains_path()函數(shù)可以用來測(cè)試指定的一個(gè)或多個(gè)path是否存在。語法為：

• json_contains_path(json_doc, one_or_all, path [,path …])，當(dāng)path存在時(shí)返回1，不存在時(shí)返回0（不是null）。  
  

小貼士該函數(shù)第二個(gè)參數(shù)one_or_all有2種取值，代表2種測(cè)試行為：
one： 至少有1個(gè)path存在即返回1。
all： 所有path都存在才返回1。

select json_contains_path(val_, 'one', '$[0]', '$[100]') from  json_demo where id_ = '1' ;

select json_contains_path(val_, 'all', '$[0]', '$[100]') from  json_demo where id_ = '1' ;

判斷指定path在對(duì)象中是否存在：

select json_contains_path(val_, 'one', '$.key1', '$.key100') from  json_demo where id_ = '2' ;
select json_contains_path(val_, 'all', '$.key1', '$.key100') from  json_demo where id_ = '2' ;

（二）： json_search()函數(shù)

json_search()函數(shù)可以通過值來查詢path，如果存在則返回其具體的path，不存在則返回null。語法為：

•  json_search(json_doc, one_or_all, search_str [escape_char [,path]…)
  

小貼士第二個(gè)參數(shù)one_or_all控制返回path的搜索行為：
one： 返回第一個(gè)匹配的path，當(dāng)找到第一個(gè)匹配的path時(shí)搜索即終止。
all： 返回所有匹配的path。
在提供search_str時(shí)，可以用%或_來代替任意多個(gè)或單個(gè)字符（和like中用法一樣）。但是如果要匹配'%'或'_'字符本身，則要加上轉(zhuǎn)義字符，即后面的參數(shù)escape_str，省略該參數(shù)或提供null值，則默認(rèn)為\

set @array1 = '[1,2, 1, "abc", "abd", "xyz"]';
select json_search(@array1, 'one', 'abc');

set @object1 = '{"key1":"abc", "key2":"abd"}';
select json_search(@array1, 'all', 'ab%');

如何查詢指定位置上是否是某值

select @array1 = '[1,2, 1, "abc", "abd", "xyz"]';
 
select json_search(@array1,'all','abc',null,'$[3]');
 # 第三個(gè)元素是否包含 abc 
select json_search(@array1,'all','abc',null,'$[4]');
 # 第四個(gè)元素是否包含 abc 

（三）： value member of(json_array)

對(duì)于JSON數(shù)組，如果只是想知道某個(gè)值是否存在，還可以用value member of(json_array)來判斷值是否包含在指定數(shù)組中（MySQL 8.0.17后可用），包含返回1，不包含則返回0：

select val_, 2 member of(val_), 'xyz' member of(val_),'x' member of(val_) from json_demo where id_=1;

JSON數(shù)據(jù)與索引

在MySQL的表中，JSON類型的列通常無法直接建立索引，你可以用虛擬生成列(Virtual Generated Columns），并根據(jù)該列來建立間接索引。但是在MySQL8.0.17版本后，對(duì)于JSON數(shù)組（JSON對(duì)象不行），可以建立多值索引（Multi-valued Index）。

（一）： 生成列索引（Generated Column Index）

對(duì)于JSON數(shù)據(jù)類型需要建立索引，可以對(duì)將經(jīng)常查詢的元素提取出來，作為一個(gè)虛擬的生成列，并在該列上建立索引，查詢時(shí)通過虛擬列上索引即可快速定位數(shù)據(jù)。虛擬列的語法是：

col_name data_type [GENERATED ALWAYS] AS (expr)
 [VIRTUAL | STORED] [NOT NULL | NULL]
 [UNIQUE [KEY]] [[PRIMARY] KEY]
 [COMMENT 'string']

• expr： 是列的生成表達(dá)式，需要依賴其他列計(jì)算。

• virtual： 代表該列不實(shí)例化，不消耗存儲(chǔ)空間，每次用到該列時(shí)計(jì)算。

• stored： 代表實(shí)例化存儲(chǔ)，消耗存儲(chǔ)空間，且每次更新其依賴列時(shí)，都會(huì)同時(shí)更新虛擬列數(shù)據(jù)。

是不是感覺晦澀難懂，接著往下看這個(gè)例子幫助我們理解下。↓下面示例中，b是根據(jù)a生成的虛擬列，并且在b上建立了索引idx：

create table index_demo(
a json,
b int generated always as (a->"$.id"),
key idx(b));
#插入三條數(shù)據(jù)
insert into index_demo(a) 
values
('{"id":1, "name":"Vincent"}'),
('{"id":2, "name":"Victor"}'), 
('{"id":3, "name":"Grace"}');

這樣就可以通過b列查詢時(shí)即可利用索引提速，快速定位記錄：

explain select * from person where b=2;

（二）： 多值索引（Multi-valued Index）

MySQL 8.0.17版本引入了多值索引，可以直接對(duì)JSON類型列創(chuàng)建索引，但是僅限JSON數(shù)組。在傳統(tǒng)二級(jí)索引中，一個(gè)索引記錄對(duì)應(yīng)一條數(shù)據(jù)記錄。但在多值索引中，會(huì)根據(jù)JSON數(shù)組中的值建立多個(gè)索引，同時(shí)指向這一條記錄，其底層原理依然是通過虛擬列完成的。

在對(duì)JSON數(shù)組列建立索引前，需要先用cast(… as type array)將其由JSON數(shù)組類型轉(zhuǎn)換為SQL數(shù)組類型

create table index_demo2(
a json,
key idx((cast(a as unsigned array)))
);
 
insert into index_demo2 
values
('[1, 2, "3", 4]'), 
('[5, "6", 7]'),
('[8, 9, 10]');

小貼士由于這里限制的是unsigned array，因此json數(shù)組中元素必須可以轉(zhuǎn)換為數(shù)字，例如插入字符"a"則會(huì)報(bào)錯(cuò)。（unsigned 屬性表示無符號(hào)類型，即只能存儲(chǔ)正數(shù)和零，不能存儲(chǔ)負(fù)數(shù)） 在建立多值索引后，member of(), json_contains()函數(shù)在where條件中則可以利用多值索引來加速查詢。

這里查詢a列中包含3的記錄，通過執(zhí)行計(jì)劃可以發(fā)現(xiàn)member of()函數(shù)使用了多值索引idx：

select * from t where 3 member of(a);
 
explain select * from t where 3 member of(a);

總結(jié)

到此這篇關(guān)于MySQL中JSON類型的文章就介紹到這了,更多相關(guān)MySQL中JSON類型內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！