MySQL 5.7新支持--------Json类型实战

1. 背景

　 * 在MySQL 5.7.8中，MySQL支持由RFC 7159定义的本地JSON数据类型，它支持对JSON(JavaScript对象标记)文档中的数据进行有效访问.

　 * MySQL会对DML JSON数据自动验证。无效的DML JSON数据操作会产生错误.

* 优化的存储格式。存储在JSON列中的JSON文档转换为一种内部格式，允许对Json元素进行快速读取访问.

* MySQL Json类型支持建立索引增加查询性能提升.

2. Json类型所需的存储空间和值范围

类型

占用字节

最大长度

Json

数据长度 + 4 bytes

3. Json相关函数操作

* JSON_OBJECT(string1, string2...) 创建 key-value 类型 Json 对象

<code>mysql> SELECT JSON_OBJECT(</code><code>'k1'</code><code>, </code><code>'v1'</code><code>, </code><code>'k2'</code><code>, </code><code>'v2'</code><code>);</code>

<code>| JSON_OBJECT(</code><code>'k1'</code><code>, </code><code>'v1'</code><code>, </code><code>'k2'</code><code>, </code><code>'v2'</code><code>) |</code>

* JSON_ARRAY(string1, string2...) 创建一个 Json 数组

<code>mysql> SELECT JSON_ARRAY(</code><code>'a'</code><code>, </code><code>'b'</code><code>, </code><code>'c'</code><code>, </code><code>'d'</code><code>);</code>

<code>| JSON_ARRAY(</code><code>'a'</code><code>, </code><code>'b'</code><code>, </code><code>'c'</code><code>, </code><code>'d'</code><code>) |</code>

* JSON_TYPE(object) 判断并显示数据类型 [ 值非法会报错显示 ]

<code>mysql> SELECT JSON_TYPE(</code><code>'"lisea"'</code><code>);</code>

<code>| JSON_TYPE(</code><code>'"lisea"'</code><code>) |</code>

<code>| STRING |</code>

<code>mysql> SELECT JSON_TYPE(</code><code>'["a", "b", 1]'</code><code>);</code>

<code>| ARRAY |</code>

<code>mysql> SELECT JSON_TYPE(</code><code>'1'</code><code>);</code>

<code>| INTEGER |</code>

<code>mysql> SELECT JSON_TYPE(</code><code>'{"k1":"v1", "k2":"v2"}'</code><code>);</code>

<code>| OBJECT |</code>

* JSON_MERGE(doc1,doc2....) 合并多个Json对象

<code>mysql> SELECT JSON_MERGE(</code><code>'{"k1":"v1"}'</code><code>,</code><code>'{ "k2":"v2"}'</code><code>);</code>

<code>| JSON_MERGE(</code><code>'{"k1":"v1"}'</code><code>,</code><code>'{ "k2":"v2"}'</code><code>) |</code>

<code>mysql> SELECT JSON_MERGE(</code><code>'["k1","k2"]'</code><code>,</code><code>'{ "k3":"v3"}'</code><code>);</code>

<code>| JSON_MERGE(</code><code>'["k1","k2"]'</code><code>,</code><code>'{ "k3":"v3"}'</code><code>) |</code>

* JSON_EXTRACT(object, key) 通过Json key方式获取Val值

<code>mysql> SELECT JSON_EXTRACT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>);</code>

<code>| JSON_EXTRACT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>) |</code>

<code>mysql> SELECT JSON_EXTRACT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.*'</code><code>);</code>

<code>| JSON_EXTRACT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.*'</code><code>) |</code>

* JSON_SET(object, key, val, key, val....) 通过key修改val值

<code>mysql> SELECT JSON_SET(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'lisea'</code><code>, </code><code>'$.k2'</code><code>, </code><code>'hello'</code><code>);</code>

<code>| JSON_SET(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'lisea'</code><code>, </code><code>'$.k2'</code><code>, </code><code>'hello'</code><code>) |</code>

<code>| {</code><code>"k1"</code><code>: </code><code>"lisea"</code><code>, </code><code>"k2"</code><code>: </code><code>"hello"</code><code>} |</code>

* JSON_INSERT(object, key, val)添加新值到对象中，如果key已存在，不替换val

<code>mysql> SELECT JSON_INSERT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'hello'</code><code>);</code>

<code>| JSON_INSERT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'hello'</code><code>) |</code>

<code>mysql> SELECT JSON_INSERT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k3'</code><code>, </code><code>'v3'</code><code>);</code>

<code>| JSON_INSERT(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k3'</code><code>, </code><code>'v3'</code><code>) |</code>

* JSON_REPLACE() 替换现有的值并忽略新的值

<code>mysql> SELECT JSON_REPLACE(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'hello'</code><code>, </code><code>'$.k3'</code><code>, </code><code>'v3'</code><code>);</code>

<code>| JSON_REPLACE(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>, </code><code>'hello'</code><code>, </code><code>'$.k3'</code><code>, </code><code>'v3'</code><code>) |</code>

<code>| {</code><code>"k1"</code><code>: </code><code>"hello"</code><code>, </code><code>"k2"</code><code>: </code><code>"v2"</code><code>} |</code>

* JSON_REMOVE() 通过key移除

<code>mysql> SELECT JSON_REMOVE(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>);</code>

<code>| JSON_REMOVE(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>, </code><code>'$.k1'</code><code>) |</code>

* JSON_KEYS() 获取所有key

<code>mysql> SELECT JSON_KEYS(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>);</code>

* JSON_UNQUOTE() 去掉值的引号

<code>mysql> SELECT JSON_UNQUOTE(</code><code>'"hello"'</code><code>);</code>

<code>| JSON_UNQUOTE(</code><code>'"hello"'</code><code>) |</code>

<code>| hello |</code>

* JSON_DEPTH() 获取Json对象的深度

<code>mysql> SELECT JSON_DEPTH(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>);</code>

<code>| JSON_DEPTH(</code><code>'{"k1":"v1","k2":"v2"}'</code><code>) |</code>

<code>mysql> SELECT JSON_DEPTH(</code><code>'{}'</code><code>);</code>

<code>| JSON_DEPTH(</code><code>'{}'</code><code>) |</code>

* JSON_VALID() 判断是否为有效的json格式

<code>mysql> SELECT JSON_VALID(</code><code>'{"k1":"v1"}'</code><code>);</code>

<code>| JSON_VALID(</code><code>'{"k1":"v1"}'</code><code>) |</code>

<code>mysql> SELECT JSON_VALID(</code><code>'{"k1":"v1"'</code><code>);</code>

<code>| JSON_VALID(</code><code>'{"k1":"v1"'</code><code>) |</code>

* JSON_LENGTH() 获取指定路径下的长度

长度的计算规则:

标量的长度为1

json array的长度为元素的个数

json object的长度为key的个数

<code>mysql> SELECT JSON_LENGTH(</code><code>'[1, 2, 3]'</code><code>);</code>

<code>| JSON_LENGTH(</code><code>'[1, 2, 3]'</code><code>) |</code>

<code>mysql> SELECT JSON_LENGTH(</code><code>'{"k1":"v1", "k2":"v2"}'</code><code>); </code>

<code>| JSON_LENGTH(</code><code>'{"k1":"v1", "k2":"v2"}'</code><code>) |</code>

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

　　　查询包含指定字符串的paths，并作为一个json array返回　

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

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

　　　path：在指定path下查。

<code>mysql> SELECT JSON_SEARCH(</code><code>'{"k1":"v1", "k2":"v2", "k3":"v2"}'</code><code>, </code><code>'one'</code><code>, </code><code>'v2%'</code><code>);</code>

<code>| JSON_SEARCH(</code><code>'{"k1":"v1", "k2":"v2", "k3":"v2"}'</code><code>, </code><code>'one'</code><code>, </code><code>'v2%'</code><code>) |</code>

<code>mysql> SELECT JSON_SEARCH(</code><code>'{"k1":"v1", "k2":"v2", "k3":"v2"}'</code><code>, </code><code>'all'</code><code>, </code><code>'v2%'</code><code>);</code>

<code>| JSON_SEARCH(</code><code>'{"k1":"v1", "k2":"v2", "k3":"v2"}'</code><code>, </code><code>'all'</code><code>, </code><code>'v2%'</code><code>) |</code>

4. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。

本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1943339，如需转载请自行联系原作者

MySQL 5.7新支持--------Json类型实战

继续阅读

javascript的for (var i in data)慎用javascript中的for (var i in data)谨慎用

MySQL的4种隔离级别？出现问题

tab鼠标经过菜单切换

vue （vue2.0）使用总结(从大体结构总结)

vue搭建过程及出现问题

/\B(?=(?:\d{3})+$)/g 一条令人费解的正则表达式

neo4j之cypher使用文档

适用于JavaScript的ECMAScript 2020规范向前发展

JS生成uuid的四种方法

mysql使用source命令导入.sql文件

vue-cli简介（中文翻译）

sqlServer根据经纬查距离

Ajax发送和获取json数据到Spring mvc 1.spring mvc后端2.web前段

layui多任务上传添加进度条

JSONObject包导入异常 java.lang.NoClassDefFoundErrorweb项目的导入包的问题