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

总结：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)