mysql-5.7-json函数使用与常用函数

最新推荐文章于 2025-07-03 14:58:35 发布

原创最新推荐文章于 2025-07-03 14:58:35 发布 · 1.3k 阅读

1 ·

CC 4.0 BY-SA版权

mysql 同时被 2 个专栏收录

11 篇文章

订阅专栏

项目中遇到的问题

6 篇文章

订阅专栏

这篇博客介绍了在MySQL 5.7中如何使用JSON数据类型和相关函数，包括创建、插入、查询和修改JSON字段。内容涵盖JSON列的创建、JSON对象和数组的更新、JSON_EXTRACT和JSON_SEARCH等查询方法，以及JSON_ARRAY_APPEND和JSON_REMOVE等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天是2019年的第一天。

在项目中遇到了关于mysql中对json字段进行处理的问题，顺便进行了学习。

项目demo：

--需要处理的json 字段
json:
{
    "6A90F6607C06084FE053201E12AC9004": {
        "sign": "1",
        "prodPzwh": "J20170028",
        "prodZczh": "H20170193",
        "pzwhAttach": [{
            "attachUrl": "reviseAttach/ciNXiCZ3P51537345506305.edc",
            "attachName": "ciNXiCZ3P51537345506305.edc",
            "attachSize": 354214,
            "attachType": "application/octet-stream",
            "oAttachName": "1.1吉泰瑞40 - 注册证批件 - 国药准字J20170028.edc"
        }],
        "zczhAttach": [{
            "attachUrl": "reviseAttach/8nRMXikTEY1537345509939.edc",
            "attachName": "8nRMXikTEY1537345509939.edc",
            "attachSize": 354214,
            "attachType": "application/octet-stream",
            "oAttachName": "1.1吉泰瑞40 - 注册证批件 - 国药准字J20170028.edc"
        }],
        "auditResult": {
            "auditOption": "",
            "auditStatus": ""
        },
        "auditResults": [],
        "prodPlaceCode": "F",
        "pzwhValidityEnd": "2022-02-20",
        "zczhValidityEnd": "2022-02-20",
        "pzwhValidityStart": "2017-04-24",
        "zczhValidityStart": "2017-04-01",
        "pzwhValidityStart,pzwhValidityEnd": "",
        "zczhValidityStart,zczhValidityEnd": ""
    },
    "6A90F6607C07084FE053201E12AC9004": {
        "sign": "1",
        "attachJsons": [{
            "attachUrl": "reviseAttach/nxtFEhyh6D1537345528512.edc",
            "attachName": "nxtFEhyh6D1537345528512.edc",
            "attachSize": 863593,
            "attachType": "application/octet-stream",
            "oAttachName": "6.吉泰瑞40mg－说明书包装20170424.edc"
        }],
        "auditResult": {
            "auditOption": "",
            "auditStatus": ""
        },
        "auditResults": []
    },
    "6A90F6607C0A084FE053201E12AC9004": {
        "sign": "1",
        "auditResult": {
            "auditOption": "",
            "auditStatus": ""
        },
        "auditResults": [],
        "prodSpeAttach": [{
            "attachUrl": "reviseAttach/EXnzcyt7nF1537345551645.edc",
            "attachName": "EXnzcyt7nF1537345551645.edc",
            "attachSize": 240180,
            "attachType": "application/octet-stream",
            "oAttachName": "外包装.edc"
        }]
    },
    "6A90F6607C0B084FE053201E12AC9004": {
        "sign": "1",
        "isLoca": "",
        "isNoSuger": "",
        "bezoarType": "",
        "attachJsons": [],
        "vitaminType": "",
        "zcyDrugType": [],
        "aminoAcidType": "",
        "osmoticPressure": "",
        "specialDrugType": "",
        "specialDrugGroup": "0",
        "isInjectorInsulin": ""
    },
    "6A90F6607C0B084FE053201E12AC9006": {
        "sign": "1",
        "isDirDrug": "0",
        "dirDrugAttach": []
    },
    "6A90F6607C0B084FE053201E12AC9009": {
        "sign": "1",
        "remark": "",
        "attachJsons": []
    },
    "6A90F6607C0C084FE053201E12AC9004": {
        "sign": "1",
        "attachJsons": [{
            "attachUrl": "reviseAttach/NdDx7RStYe1537345593977.edc",
            "attachName": "NdDx7RStYe1537345593977.edc",
            "attachSize": 2162498,
            "attachType": "application/octet-stream",
            "oAttachName": "4.吉泰瑞 - 中国保护期内化合物专利ZL01820866.5.edc"
        }],
        "auditResult": {
            "auditOption": "",
            "auditStatus": ""
        },
        "auditResults": [],
        "originalFlag": "1",
        "originalType": "02"
    },
    "6A90F6607C10084FE053201E12AC9004": {
        "sign": "1",
        "isFDA": "1",
        "attachJsons": [{
            "attachUrl": "reviseAttach/bPerpMRpD31537345611307.edc",
            "attachName": "bPerpMRpD31537345611307.edc",
            "attachSize": 913326,
            "attachType": "application/octet-stream",
            "oAttachName": "10.FDA证明－吉泰瑞（美国）.edc"
        }, {
            "attachUrl": "reviseAttach/jPee5DjSBQ1537345618052.edc",
            "attachName": "jPee5DjSBQ1537345618052.edc",
            "attachSize": 235975,
            "attachType": "application/octet-stream",
            "oAttachName": "FDA截图.edc"
        }],
        "auditResult": {
            "auditOption": "",
            "auditStatus": ""
        },
        "auditResults": []
    },
    "6A90F6607C12084FE053201E12AC9004": {
        "sign": "1",
        "attachJsons": [{
            "attachUrl": "reviseAttach/8D6G5CG4c61537345560192.edc",
            "attachName": "8D6G5CG4c61537345560192.edc",
            "attachSize": 332869,
            "attachType": "application/octet-stream",
            "oAttachName": "诊疗指南.edc"
        }],
        "auditResult": {
            "auditOption": "",
            "auditStatus": ""
        },
        "auditResults": [],
        "drugProperties": "01"
    },
    "6A90F6607C13084FE053201E12AC9004": {
        "sign": "1",
        "attachJsons": [],
        "consistencyEvaluation": "0"
    }
}
-- 因为减少代码重复，直接写了一个函数，取json 中的值
CREATE DEFINER=`root`@`%` FUNCTION `yanshitest`(keyValue VARCHAR(50),val VARCHAR(50),jsonStr JSON) RETURNS varchar(5000) CHARSET utf8
BEGIN
    -- 申明变量
    DECLARE jsonval VARCHAR(200);
    DECLARE jsonvalStr VARCHAR(5000);
    DECLARE result VARCHAR(5000);
    DECLARE url VARCHAR(5000);
    DECLARE y int;
        set y = 0;
        set jsonval = CONCAT('$."',keyValue,'".',val);
        set jsonvalStr = CONCAT(jsonval,'[',y,'].attachUrl');
        set result = '';
        set url = 'http://bucket-dup.oss-cn-shanghai.aliyuncs.com/';
        WHILE JSON_EXTRACT(jsonStr,jsonvalStr) is not null  DO
        set y = y + 1;
        set result = CONCAT(result,',',url,JSON_UNQUOTE(JSON_EXTRACT(jsonStr,jsonvalStr)));
        set jsonvalStr = CONCAT(jsonval,'[',y,'].attachUrl');
END WHILE;

    RETURN substring(result,2,LENGTH(result));
END

--使用自定义函数
SELECT
t.id,
yanshitest('6A90F6607C06084FE053201E12AC9004','pzwhAttach',t.decl_json) 药品批件,
yanshitest('6A90F6607C06084FE053201E12AC9004','zczhAttach',t.decl_json) 注册证附件,
yanshitest('6A90F6607C0A084FE053201E12AC9004','prodSpeAttach',t.decl_json) 产品图片,
yanshitest('6A90F6607C12084FE053201E12AC9004','drugProperties',t.decl_json) 治疗属性,
yanshitest('6A90F6607C0C084FE053201E12AC9004','attachJsons',t.decl_json) 原研属性,
yanshitest('6A90F6607C10084FE053201E12AC9004','attachJsons',t.decl_json) FDA属性,
yanshitest('6A90F6607C13084FE053201E12AC9004','attachJsons',t.decl_json) 国家仿制药一致性评价,
yanshitest('6A90F6607C18084FE053201E12AC9004','gmpAttach',t.decl_json) GMP证书,
yanshitest('6A90F6607C0B084FE053201E12AC9004','attachJsons',t.decl_json) 特殊药品品种附件,
yanshitest('6A90F6607C0B084FE053201E12AC9009','attachJsons',t.decl_json) 其他附件,
yanshitest('6A90F6607C07084FE053201E12AC9004','attachJsons',t.decl_json) 产品说明书
from yanshi t

JSON列存储的必须是JSON格式数据，否则会报错。
JSON数据类型是没有默认值的。

--创建带有Json类型的表字段

CREATE TABLE user(id INT PRIMARY KEY, info  JSON);

--插入json字段

//更新一个json数组

UPDATE  user set info = JSON_ARRAY(1,"h1",NULL,true)  WHERE id= '0001';

//更新一个json对象

UPDATE  user set info = JSON_OBJECT('age','20','birthday',now())  WHERE id= '0002';

--查询记录

//得到对象中的一个对象

select t.id,JSON_EXTRACT(t.info, '$.mail') mail from user t

//where JSON_EXTRACT(t.info, '$.mail') = 'jiangchengyao@gmail.com'

select t.id, t.info-> '$.age' FROM `user` t;

//查找出key

SELECT t.id,JSON_KEYS(t.info)FROM `user` t

--修改

//有则更新，无则新增 JSON_SET，移除json_remove

UPDATE user t SET t.info = JSON_SET(info,'$.ip','127.0.0.1' ) WHERE id = '0002'

--创建json值的函数

--JSON_ARRAY(val1,val2,val3...)

--json_array生成json数组

--json_object 生成json对象

SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()); -- [1, "abc", null, true, "10:37:08.000000"]

2.JSON_OBJECT(key1,val1,key2,val2...)

--JSON_QUOTE(json_val)

SELECT JSON_OBJECT('age', 20, 'time', now()); -- {"id": 87, "name": "carrot"}

3.JSON_QUOTE 加"号

SELECT JSON_QUOTE('[1,2,3]'); -- "[1,2,3]"

--搜索json值

1.--JSON_CONTAINS 指定数据是否存在

-- 查询json文档是否在指定path包含指定的数据，包含则返回1，否则返回0。如果有参数为NULL或path不存在，则返回NULL。

SELECT JSON_CONTAINS(t.info, '"20"','$.age') FROM `user` t;

2.--JSON_EXTRACT 查找所有指定数据

-- JSON_EXTRACT(json_doc, path[, path] ...)

-- 从json文档里抽取数据。如果有参数有NULL或path不存在，则返回NULL。如果抽取出多个path，则返回的数据封闭在一个json array里。

select t.id,JSON_EXTRACT(t.info, JSON_UNQUOTE(JSON_SEARCH(t.info,'one','20'))) mail from user t ;

JSON_UNQUOTE 去掉""

JSON_QUOTE 加""

3.--JSON_SEARCH 查找所有指定值的位置

--JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

-- one_or_all："one"表示查询到一个即返回；"all"表示查询所有。

-- search_str：要查询的字符串。可以用LIKE里的'%'或‘_’匹配。

-- path：在指定path下查。

select t.id, JSON_SEARCH(t.info,'one','20') FROM `user` t;

--array数组对象操作函数

1.JSON_ARRAY_APPEND 指定位置追加数组元素

-- JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

-- 在指定path的json array尾部追加val。如果指定path是一个json object，则将其封装成一个json array再追加。如果有参数为NULL，则返回NULL。

--插入新的元素

select JSON_ARRAY_APPEND(t.info, '$', 'z')--$号整体的后面

--某个元素的

SELECT JSON_ARRAY_APPEND(@j5, '$.b', 'x'); -- {"a": 1, "b": [2, 3, "x"], "c": 4} 

SELECT JSON_ARRAY_APPEND(@j5, '$.c', 'y'); -- {"a": 1, "b": [2, 3], "c": [4, "y"]}

2.JSON_ARRAY_INSERT 指定位置插入数组元素

-- JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

-- 在path指定的json array元素插入val，原位置及以右的元素顺次右移。如果path指定的数据非json array元素，则略过此val；如果指定的元素下标超过json array的长度，则插入尾部。

SET @j6 = '["a", {"b": [1, 2]}, [3, 4]]';

SELECT JSON_ARRAY_INSERT(@j6, '$[1]', 'x'); -- ["a", "x", {"b": [1, 2]}, [3, 4]]

SELECT JSON_ARRAY_INSERT(@j6, '$[100]', 'x'); -- ["a", {"b": [1, 2]}, [3, 4], "x"]

SELECT JSON_ARRAY_INSERT(@j6, '$[1].b[0]', 'x'); -- ["a", {"b": ["x", 1, 2]}, [3, 4]]SELECT JSON_ARRAY_INSERT(@j6, '$[0]', 'x', '$[3][1]', 'y'); -- ["x", "a", {"b": [1, 2]}, [3, "y", 4]]

--json移除

-- JSON_REMOVE(json_doc, path[, path] ...)

-- 移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。

select JSON_REMOVE(info, '$.age') FROM `user`;