MySQL JSON类型字段的简单使用

MySQL 5.7起支持JSON数据类型的字段。JSON作为现在最为流行的数据交互形式，MySQL也不断跟进，在5.7版本开始新增JSON数据类型。虽然现在的应用应该还比较少，但也说不准能成为一种趋势。先简单学习一下MySQL对JSON数据类型的相关操作和一些内置函数(以下内容基MySQL于8.0.13)。

PS: 以下内容有不合理的地方，比如实际写SQL的时候关键字应该大写，禁用SELECT *等，只是为了直观，见谅~

创建含有JSON字段的表

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

JSON字段无需设置长度，也不能设置默认值

插入JSON记录

MySQL的JSON类型支持JSON数组和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值可为字符串，数值，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"]');
复制代码

查询结果

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'));
复制代码
查询结果
select * from test_json
PS：两种类型可嵌套使用。
查询含JSON类型的字段
前面可以通过select语句查询含有JSON的记录，结果如上所示。
如果要提取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内的更新
内置函数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有引号？
插入时需要进行转义
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);
如果查询结果不想保留转义可采用col->>path的形式
select obj_json->>'$."key1"' obj_key1, arr_json->>'$[0]' arr_index1 
    from test_json where id in (3, 4);
合并函数JSON_MERGE_PRESERVE()和JSON_MERGE_PATCH()
8.0之后提供了合并函数，可将多个JSON进行合并
区别在于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数据类型支持嵌套，简单演示一下
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类型字段的使用，应当认真考虑数据库设计，看看适不适合应用JSON数据类型。开发往往结合其他语言使用，MySQL作为一款TRDB，有时候对JSON数据类型操纵会比较繁琐，如强类型语言的ORM映射，是否使用JSON数据类型，还需结合实际情况斟酌。
参考资料：dev.mysql.com/doc/refman/…