寫在開頭

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

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

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

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

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

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

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

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

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

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

測試數(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對象中，鍵是不能重復的，如果出現(xiàn)相同的鍵，規(guī)范化時后值會覆蓋前值。JSON對象中重復的鍵key1保留了后出現(xiàn)的值"value3"。（MySQL 8.0.3之前保留先出現(xiàn)的值）。

json_array、json_object、cast函數(shù)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

小貼士如果目標是JSON對象，則返回null（id為2的記錄）

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

• json_extract(js_array, '$[m to n]') 通過'$[m to n]'的形式取出JSON數(shù)組中編號m到n的所有元素（編號從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范圍內的元素。

3： 從JSON對象中取出單個值，語法如下：

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

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

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

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對象的值，如果想返回鍵，則需要用json_keys()函數(shù)。json_keys()會以JSON數(shù)組的形式返回JSON對象中頂層的鍵，即將所有的鍵組成一個數(shù)組返回。語法如下：

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

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

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

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

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

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

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

這是當前的測試數(shù)據(jù)

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

josn_arry_append() 會在JSON數(shù)組指定的位置添加新的數(shù)據(jù)，新增的數(shù)據(jù)與原位置的數(shù)據(jù)會合并為新的JSON數(shù)組（依然算1個元素），不會改變原JSON數(shù)據(jù)的元素個數(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ù)組第一個元素中增加一個'x'，第三個元素中增加一個'y'，數(shù)組的元素依然是4個
update json_demo set val_=json_array_append(val_,'$.key2','y') where id_=2;
#在JSON對象鍵key2的值中附件一個'y'

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

josn_arry_insert() 會在JSON數(shù)組指定的位置添加新的數(shù)據(jù)，與json_array_append()不同的是，新增的數(shù)據(jù)會作為一個獨立的元素，此函數(shù)會改變JSON數(shù)組中元素的個數(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個元素，JSON數(shù)組的元素由4個增加至7個
# 第一個元素插入指定位置后，從這個位置開始，所有元素向后移動一位，這會改變后續(xù)元素編號，并影響后續(xù)的插入位置
# '$[0]'位置插入'x'后，所有元素后移一位，"a"的位置由'$[2]'變成了'$[3]'，因此函數(shù)第二個插入'$[3]'是插在"a"的前面，而不是原'$[3]'元素"b"的前面
# 函數(shù)第三個元素'$[100]'，超出了數(shù)組長度，因此附加在數(shù)組的最后

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

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

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

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

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

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

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

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

這是當前的測試數(shù)據(jù)

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

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

•  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個元素
#這里$[5]指定更新了一個不存在的元素，因此忽略

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

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

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

•  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;
#第一個path參數(shù)'$[0]'，匹配數(shù)組中第一個元素，將'x'更新為'0'
#第二個path參數(shù)'$[1]'，匹配數(shù)組中第二個元素，將'y'更新為'1'
#第三個path參數(shù)'$[100]'，匹配數(shù)組中第100個元素，由于不存在，新增到數(shù)組最后

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

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

這是當前的測試數(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個元素，對應位置是'$[1]','$[2]'
#注意在刪除'$[1]'位置的元素后，所有后續(xù)元素編號都向前移動1位，因此'$[2]'刪除的是新'$[3]'位置的元素"b"，而不是原'$[3]'位置的元素"a"

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

判斷元素否存在

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

當前的測試數(shù)據(jù)

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

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

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

小貼士該函數(shù)第二個參數(shù)one_or_all有2種取值，代表2種測試行為：
one： 至少有1個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在對象中是否存在：

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]…)
  

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

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]');
 # 第三個元素是否包含 abc 
select json_search(@array1,'all','abc',null,'$[4]');
 # 第四個元素是否包含 abc 

（三）： value member of(json_array)

對于JSON數(shù)組，如果只是想知道某個值是否存在，還可以用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版本后，對于JSON數(shù)組（JSON對象不行），可以建立多值索引（Multi-valued Index）。

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

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

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

• expr： 是列的生成表達式，需要依賴其他列計算。

• virtual： 代表該列不實例化，不消耗存儲空間，每次用到該列時計算。

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

是不是感覺晦澀難懂，接著往下看這個例子幫助我們理解下。↓下面示例中，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列查詢時即可利用索引提速，快速定位記錄：

explain select * from person where b=2;

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

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

在對JSON數(shù)組列建立索引前，需要先用cast(… as type array)將其由JSON數(shù)組類型轉換為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ù)組中元素必須可以轉換為數(shù)字，例如插入字符"a"則會報錯。（unsigned 屬性表示無符號類型，即只能存儲正數(shù)和零，不能存儲負數(shù)） 在建立多值索引后，member of(), json_contains()函數(shù)在where條件中則可以利用多值索引來加速查詢。

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

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

總結

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

最新評論