mysql5.7强势支持原生json格式!!全面掌握

mysql一直是如此优秀,但是随着最近一些nosql的强劲发展,甚为关系型数据库的mysql,也不例外在某些层面稍有逊色。其中,是否支持json格式是最常被用来比较的。

终于mysql5.7.7+版本,innodb引擎也可以支持原生Json了,而且不是粗略支持,是全面强大的支持,各种json处理函数满足你所有对json操作的场景。它不是简单的类似BLOB的替换,它不是以字符串格式存储,而是以内部的二进制格式存储,这样支持可以快速访问json数据;并且在创建时会自动校验是否合法json格式;而且这种原生的json type可以进行 =, <, <=, >, >=, <>, !=, 和 <=> 运算符的比较。

我们来通过实例来认识它,对它所提供的功能有个全面的了解。

首先你要升级你的mysql版本到5.7的最新GA版,升级方法可以参考本公众号的上一篇文章(关注公众号可获取)。

1.新建数据表

# 新建test1表

create table test_57.test1 (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

`info` JSON NULL,

primary key (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试5.7版本json格式';


2.插入json数据

#insert into test_57.test1 (info) values('{"name":"yuhaiwei","age":18,"hobby":"swim,reading,writting"}');

#insert into test_57.test1 (info) values('{"name":"tangwei","age":28,"hobby":"show,song,movie"}');


3.json 函数详解

#json_array 数组json串

#insert into test57.test1 (info) values (json_array('"testname', 99, 'basketball'));


#json_object 对象json串

#insert into test_57.test1 (info) values (json_object('name','"json_object_test_name"', 'age',99, 'hobby','basketball'));


#JSON_MERGE(json_doc, json_doc[, json_doc] ...) 合并json

select json_merge('[{"name":"yhw"},{"name":"test"}]', '{"name":"test1"}');


#json_type 判断json类型,是数组还是对象

select json_type(info) from test_57.test1;


#JSON_VALID(val) 判断是否合法json

select info,json_valid(info) from test_57.test1;


#json_quote  用双引号括起来,并对json中的一些特殊字符转义

insert into test_57.test1 (info) values((('{"name":"noquote_name","age":18,"hobby":"swim,reading,writting"}')));


#JSON_UNQUOTE(val) 和 json_quote相反

#JSON_EXTRACT(json_doc, path[, path] ...) 查询出json的属性值 $-整个json,$[0]-json数组第一个元素,$.name-json的name属性值

select info,json_extract(info, '$.name') from test_57.test1;


# ->符号 ,作用等同于 json_extract

select info->'$.name' from test_57.test1 limit 1;


# JSON_SET(json_doc, path, val[, path, val] ...) 替换json串中的值, 有新属性值会添加

select json_set(info, '$.name', 'yuhaiweiset', '$.age', 11, '$.key1', 'value1') from test_57.test1 limit 1;

# json_insert 添加值(不替换),用法同json_set

# json_replace 替换值 (不添加),用法同json_set


# json_remove 删除

select json_remove(info, '$.name', '$.age', '$.name') from test_57.test1 limit 1;


#JSON_CONTAINS(json_doc, val[, path]) json_doc的path值 是否包含val

SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '{"d": 4}', '$.c'); #结果 1


#JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...) json_doc是否包含路径path,第二个参数为one 只要包含一个就返回true

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); #结果 1

SELECt JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e'); #结果 0


#json_keys(json,[,path]) json的path路径的所有属性名

SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b'); #结果["c"]


#JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) 返回search_str在json_doc的path路径下的全路径

SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]'); #结果 "$[2].x"


#JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...) 在最后添加子节点

SELECT JSON_ARRAY_APPEND('{"a": 1, "b": [2, 3], "c": 4}', '$.b[0]', 'y'); # 结果{"a": 1, "b": [[2, "y"], 3], "c": 4}


#JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...) 在前面添加兄弟节点

SELECT JSON_ARRAY_INSERT('{"a": 1, "b": [2, 3], "c": 4}', '$.b[0]', 'y'); # 结果{"a": 1, "b": ["y", 2, 3], "c": 4}


#JSON_DEPTH(json_doc) json深度

SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'), JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]'); #结果:1 1 1 2 2


#JSON_LENGTH(json_doc[, path]) json的长度,不包括嵌套的子元素的长度

SELECT JSON_LENGTH('[1, 2, {"a": 3}]'); #结果:3

SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') #结果 1

原文发布于微信公众号 - nginx(nginx-study)

原文发表时间:2015-11-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏蓝天

MySQL的NO_BACKSLASH_ESCAPES

官方说明: https://dev.mysql.com/doc/refman/5.7/en/mysql-real-escape-string.html 相...

1274
来自专栏加米谷大数据

Hive的数据类型

本文介绍hive的数据类型,数据模型以及文件存储格式。这些知识大家可以类比关系数据库的相关知识。

1342
来自专栏黑泽君的专栏

day31_Hibernate学习笔记_03

在数据库表中如何表达多对多关系:   使用中间表,分别引用两方的ID。 在对象中如何表达多对多关系:   两方都使用集合表达。 在配置文件中如何表达一对多关系...

1004
来自专栏崔庆才的专栏

Python操作MySQL存储,这些你都会了吗?

3785
来自专栏后台架构

Sphinx源码学习笔记(二):查询关键词

 继续上一篇索引创建流程完成,学习理解一下查询关键词的逻辑处理流程。

3106
来自专栏一个会写诗的程序员的博客

6.2 Spring Boot集成jpa6.2 Spring Boot集成jpa小结

Java持久化API(JPA,Java Persistence API)是一个将对象映射为关系数据库的标准技术。JPA通过注解或XML描述ORM(Object ...

894
来自专栏Java开发

Spring集成MyBatis 通用Mapper以及 pagehelper分页插件

这里送上楼主配置的Spring DataSource 和 TransactionManager 和MyBatis配置

2832
来自专栏信安之路

sql注入学习总结

所谓SQL注入,就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。

1080
来自专栏运维技术迷

连仕彤博客[Python笔记] Python 一次性替换多个字符

在一个字符串中,一次性替换多个字符串,可以使用字符串的replace函数来处理(目前我想到的是这个方法,大神请指教!) 要求 把INSERT语句中的riqi、b...

3575
来自专栏开源优测

AutoLine源码分析之数据库模型

AutoLine开源平台是一个开源自动化测试解决方案,基于RobotFramework进行二次开发,支持RobotFramework几乎所有的库。

921

扫码关注云+社区

领取腾讯云代金券