mysql5.7 原生json数据类型总结

发布于:2019-12-19 18:39:21

总结:mysql5.7 开始支持字段原生的json数据类型存储


1. 创建表含json类型的字段示例

CREATE TABLE `t_testjson` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) DEFAULT NULL,
    `ext_info` json DEFAULT NULL,
    `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试mysql json格式';


备注:json字段不可以设置长度,可以是null,不能用有默认值


2. 插入json数据示例:

     INSERT INTO `db_damon`.`t_testjson`(`id`, `name`, `ext_info`, `modify_time`) VALUES (1, 'jelly2', '{"sex": "男", "name": "张三", "userid": 1}', '2019-12-19 18:13:21');

     INSERT INTO `db_damon`.`t_testjson`(`id`, `name`, `ext_info`, `modify_time`) VALUES (2, 'tom', '[1, 2, 3]', '2019-12-19 18:14:24');

    INSERT INTO `db_damon`.`t_testjson`(`name`, `ext_info`, `modify_time`) VALUES ('tom', '[1, 2, 3]', '2019-12-19 18:14:24');

    INSERT INTO `db_damon`.`t_testjson`( `name`, `ext_info`) VALUES ('tom2', JSON_ARRAY("123", "goods", true, "", 0, null,CURRENT_DATE));

    


3. 更新json数据示例:

        UPDATE t_testjson SET ext_info = JSON_INSERT(ext_info, '$.username', 'admin', '$.url', 'www.nfangbian.com') WHERE id = 1;

        UPDATE t_testjson SET ext_info = JSON_SET(ext_info, '$.userid', '3838', '$.username', 'admin3838') WHERE id = 1;
        UPDATE t_testjson SET ext_info = JSON_REPLACE(ext_info, '$.name', 'php', '$.url', 'http://www.php.net') WHERE id = 2;
        UPDATE t_testjson SET ext_info = JSON_REMOVE(ext_info, '$.userid', '$.username') WHERE id = 3;


4. 查询json数据示例:

       SELECT id, ext_info->'$.userid' as userid, ext_info->'$.username', ext_info->'$[0]', ext_info->'$[2]' FROM t_testjson;

        SELECT JSON_UNQUOTE(ext_info->'$.username') as username from t_testjson;
            等价 SELECT ext_info->>'$.username' as username from t_testjson;

        SELECT * FROM t_testjson WHERE ext_info = CAST('{"userid": 123, "username": "admin"}' as JSON);
        SELECT * FROM t_testjson WHERE ext_info->'$.username' = 'admin';


5. 删除示例:




mysql支持的json函数如下:

JSON_OBJECT([key, val[, key, val] ...])
JSON_OBJECT("key名1",值1,"key名2",值2,"key名N",值N)
select JSON_OBJECT("userid", 123, "username", "admin")
返回对象字符串:{"userid": 123, "username": "admin"}

JSON_ARRAY([val[, val] ...])
select JSON_ARRAY("123", "goods", true, "", 0, null,CURRENT_DATE);
返回数组字符串:["123", "goods", true, "", 0, null, "2019-12-20"]

将json字符串转成json:CAST(expr AS type)
select CAST('{"userid": 100, "username": "admin"}' as JSON);
返回:{"userid": 100, "username": "admin"}
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
JSON_CONTAINS(target, candidate[, path])
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
JSON_DEPTH(json_doc)
JSON_EXTRACT(json_doc, path[, path] ...)
只会追加新的值,原有的值不会被覆盖:
JSON_INSERT(json_doc, path, val[, path, val] ...)
JSON_KEYS(json_doc[, path])
JSON_LENGTH(json_doc[, path])
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
JSON_PRETTY(json_val)
JSON_QUOTE(string)
JSON_UNQUOTE(json_val)
删除元素(即删除key):
JSON_REMOVE(json_doc, path[, path] ...)
只替换原有的值,新值忽略:
JSON_REPLACE(json_doc, path, val[, path, val] ...)
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
插入新值并可以覆盖原有的值:
JSON_SET(json_doc, path, val[, path, val] ...)
JSON_STORAGE_SIZE(json_val)
JSON_TYPE(json_val)
JSON_VALID(val)
JSON_ARRAYAGG(col_or_expr)
JSON_OBJECTAGG(key, value)