最近刚好用到了MySQL的JSON函数做了一些数据处理相关的工作,顺便记录总结一下相关知识点和用法。

MySQL从5.7.8开始支持原生JSON数据类型,以JSON类型存储json格式的数据,比字符串类型存储json格式的数据有以下几点优势:

1.JSON类型字段提供自动校验功能,非标签JSON写入JSON类型的字段会提示出错。

2.JSON类型存储的读取性能更好,mysql内部对JSON类型数据做了存储优化,可无需读取整个字段值,直接读取json的key对应的值或者数组下标。

3.JSON类型字段无法设置非空默认值(mysql8.0版本开始支持非空默认值)。

4.JSON类型字段占据的存储空间和longblob、longtext差不多。

5.MySQL8.0支持直接对JSON类型字段的JSON值进行增删改查操作,无需整个字段内容取出再操作,很方便。

6.从MySQL8.0开始还支持JSON字段添加索引,目前支持最多三层JSON深度的索引。

下面总结MySQL JSON常用使用方法:

首先新建表:

1.添加JSON值

标准格式:

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');Query OK, 1 row affected (0.01 sec)

插入不符合JSON格式的值,会报错,说明mysql内部会校验字段的JSON结构,不同于单纯的字符串存储。

2.JSON_TYPE()函数

用于判断JSON值的类型,返回输入JSON的类型:

BLOB,BIT,OPAQUE,DATETIME,TIME,DATE,BOOLEAN,ARRAY,OBJECT,STRING,INTEGER,DOUBLE NULL

.JSON的标准化,合并,以及自动转换

重复的key,会取第一个key并丢弃后面的value

标准化JSON对象JSON_OBJECT():

组成JSON数组JSON_ARRAY():

JSON合并函数:JSON_MERGE_PRESERVE()和JSON_MERGE(), 用法基本一致,

如果是多个json object, 则合并为一个json object。

如果是多个json array, 则合并为一个json array。

如果是object和array混合的json数据,则先合并object为array, 再合并为array。

看官方示例:

4.搜索和修改JSON值

读取json对象中的某个key的值, $.name指取对应key的名字。

析json中的所有value和解析json中的key=c对应的所有值,$.*中的*是通配符。

解析数组中指定下标范围的JSON值

JSON值的比较可用符号: =,<,<=,>,>=,<>,!=,<=>

不支持:between,in(),least(),greatest()

JSON对象对比:

如:{"a": 1, "b": 2} = {"b": 2, "a": 1}

6.总结:

平时开发中,如果有需要动态变化的数据结构,可适当考虑使用JSON类型的字段进行存储,也方便使用对应函数在数据库中直接处理JSON数据,对数据量相对小的数据清洗也显得非常方便,比文本类型的存储,用起来舒服很多。