腳本之家服務器常用軟件

快捷導航

MySQL JSON類型字段的簡單使用

更新時間：2025年08月06日 10:18:40 作者：鍋菌魚

JSON作為現(xiàn)在最為流行的數(shù)據(jù)交互形式,在5.7版本開始新增JSON數(shù)據(jù)類型,本文就來介紹一下MySQL JSON類型字段的使用,具有一定的參考價值,感興趣的可以了解一下

MySQL 5.7起支持JSON數(shù)據(jù)類型的字段。JSON作為現(xiàn)在最為流行的數(shù)據(jù)交互形式，MySQL也不斷跟進，在5.7版本開始新增JSON數(shù)據(jù)類型。雖然現(xiàn)在的應用應該還比較少，但也說不準能成為一種趨勢。先簡單學習一下MySQL對JSON數(shù)據(jù)類型的相關(guān)操作和一些內(nèi)置函數(shù)(以下內(nèi)容基MySQL于8.0.13)。

PS: 以下內(nèi)容有不合理的地方，比如實際寫SQL的時候關(guān)鍵字應該大寫，禁用SELECT *等，只是為了直觀，見諒~

創(chuàng)建含有JSON字段的表

create table test_json ( 
    `id` int auto_increment,
    `obj_json` JSON,
    `arr_json` JSON,
    primary key (`id`)
)engine = InnoDB default charset = utf8mb4;

JSON字段無需設(shè)置長度，也不能設(shè)置默認值

插入JSON記錄

MySQL的JSON類型支持JSON數(shù)組和JSON對象

#JSON_ARRAY
["xin", 2019, null, true, false, "2019-5-14 21:30:00"]
#JSON_OBJECT
{"key1": "value", "key2": 2019, "time": "2015-07-29 12:18:29.000000"}

JSON_ARRAY和JSON_OBJECT值可為字符串，數(shù)值，null，時間類型，以及布爾值

JSON_OBJECT的鍵需為字符串類型

插入方式：直接通過字符串的形式

insert into test_json (obj_json, arr_json) 
       values ('{"key1": "value", "key2": 2019, "time": "2015-07-29 12:18:29.000000"}', 
               '["xin", 2019, null, true, false, "2019-5-14 21:30:00"]');

查詢結(jié)果

select * from test_json

插入方式：通過JSON_OBJECT()，JSON_ARRAY()

insert into test_json (obj_json, arr_json) 
    values (JSON_OBJECT('key1', 'insert by JSON_OBJECT', 'key2', 3.14159),
            JSON_ARRAY('Go', 'Ruby', 'Java', 'PHP'));

查詢結(jié)果

select * from test_json

PS：兩種類型可嵌套使用。

查詢含JSON類型的字段

前面可以通過select語句查詢含有JSON的記錄，結(jié)果如上所示。

如果要提取JSON字段中具體值呢？

OBJECT類型

col->path形式，其中表達式path為$.key

select obj_json->'$."key1"' key1, obj_json->'$."key2"' key2 from test_json;

ARRAY類型

col->path形式，其中表達式path為$[index]

select arr_json->'$[0]' index1, arr_json->'$[1]' index2 , arr_json->'$[2]' index3 from test_json;

JSON類型字段的更新

更新你當然可以直接通過update覆蓋掉整個字段

接下來簡單介紹一下對JSON內(nèi)的更新

內(nèi)置函數(shù)JSON_SET()，JSON_INSERT()，JSON_REPLACE()，JSON_REMOVE()

JSON_SET() 插入值，如果存在則進行覆蓋

update test_json
    set obj_json = JSON_SET(obj_json, '$."json_set_key"', 'json_set_value', '$.time', 'new time'),
        arr_json = JSON_SET(arr_json, '$[6]', 'seven element', '$[0]', 'replace first') 
    where id = 1;

select * from test_json where id = 1;

JSON_INSERT()插入值，不會覆蓋原有值

update test_json
    set obj_json = JSON_INSERT(obj_json, '$."json_insert_key"', 'json_insert_value', '$."key1"', 'Set existing key'),
        arr_json = JSON_INSERT(arr_json, '$[4]', 'json_insert_value', '$[0]', 'Set existing index') 
    where id = 2;

select * from test_json where id = 2;

JSON_REPLACE()只會覆蓋原來有的值

update test_json
    set obj_json = JSON_REPLACE(obj_json, '$."key1"', 'json_replace_key1', '$."json_replace_insert"', 'test'),
        arr_json = JSON_REPLACE(arr_json, '$[3]', 'PHP is best language!', '$[5]', 'json_replace_insert')
    where id = 2;

select * from test_json where id = 2;

JSON_REMOVE()移除

update test.test_json
    set obj_json = JSON_REMOVE(obj_json, '$."key1"', '$."Nonexistent key"'),
        arr_json = JSON_REMOVE(arr_json, '$[0]', '$[5]')
    where id = 2;

select * from test_json where id = 2;

其他

如果存儲的JSON有引號？

插入時需要進行轉(zhuǎn)義

insert into test_json (obj_json, arr_json) values ('{"key1":  "test_obj_value1\\""}', '["\\"test_arr_value1\\""]');
insert into test_json (obj_json, arr_json) values (JSON_OBJECT('key1', '\"test_obj_value1 \'single\''), 
                                                   JSON_ARRAY('\"test_arr_value1\" \'single\''));

查詢

select obj_json->'$."key1"' obj_key1, arr_json->'$[0]' arr_index1 
    from test_json where id in (3, 4);

如果查詢結(jié)果不想保留轉(zhuǎn)義可采用col->>path的形式

select obj_json->>'$."key1"' obj_key1, arr_json->>'$[0]' arr_index1 
    from test_json where id in (3, 4);

合并函數(shù)JSON_MERGE_PRESERVE()和JSON_MERGE_PATCH()

8.0之后提供了合并函數(shù)，可將多個JSON進行合并

區(qū)別在于JSON_MERGE_PATCH()會將原有值覆蓋，而JSON_MERGE_PRESERVE()不會

下面進行試驗一下（不插入表）

select
    JSON_MERGE_PATCH(
        JSON_OBJECT('obj_key1', 'obj_value1', 'obj_key2', 'obj_value2'),
        JSON_OBJECT('obj_key2', 'new_obj_value2')
    ) as col1,
    JSON_MERGE_PATCH(
        JSON_ARRAY('arr_index1', 'arr_index2', 'arr_index3'),
        JSON_ARRAY('arr_index4', 'arr_index5', 'arr_index6')
    ) as col12;

select
    JSON_MERGE_PRESERVE(
        JSON_OBJECT('obj_key1', 'obj_value1', 'obj_key2', 'obj_value2'),
        JSON_OBJECT('obj_key2', 'new_obj_value2')
    ) as col2,
    JSON_MERGE_PRESERVE(
        JSON_ARRAY('arr_index1', 'arr_index2', 'arr_index3'),
        JSON_ARRAY('arr_index4', 'arr_index5', 'arr_index6')
    ) as col3;

如前面所說，JSON數(shù)據(jù)類型支持嵌套，簡單演示一下

select
    JSON_MERGE_PRESERVE(
        JSON_ARRAY('arr_index1', 'arr_index2'),
        JSON_OBJECT('obj_key1', 'obj_value1')
    ) as col1,
    JSON_MERGE_PRESERVE(
        JSON_OBJECT('obj_key1', 'obj_value1'),
        JSON_ARRAY('arr_index1', 'arr_index2')
    ) as col2;

寫在最后

JSON類型字段的使用，應當認真考慮數(shù)據(jù)庫設(shè)計，看看適不適合應用JSON數(shù)據(jù)類型。開發(fā)往往結(jié)合其他語言使用，MySQL作為一款TRDB，有時候?qū)SON數(shù)據(jù)類型操縱會比較繁瑣，如強類型語言的ORM映射，是否使用JSON數(shù)據(jù)類型，還需結(jié)合實際情況斟酌。