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 |
4G |
3. Json相关函数操作
* JSON_OBJECT(string1, string2...) 创建 key-value 类型 Json 对象
|
1
2
3
4
5
6
7
|
mysql> SELECT JSON_OBJECT(
'k1'
,
'v1'
,
'k2'
,
'v2'
);
+-------------------------------------+
| JSON_OBJECT(
'k1'
,
'v1'
,
'k2'
,
'v2'
) |
+-------------------------------------+
| {
"k1"
:
"v1"
,
"k2"
:
"v2"
} |
+-------------------------------------+
1 row
in
set
(0.01 sec)
|
* JSON_ARRAY(string1, string2...) 创建一个 Json 数组
|
1
2
3
4
5
6
7
|
mysql> SELECT JSON_ARRAY(
'a'
,
'b'
,
'c'
,
'd'
);
+--------------------------------+
| JSON_ARRAY(
'a'
,
'b'
,
'c'
,
'd'
) |
+--------------------------------+
| [
"a"
,
"b"
,
"c"
,
"d"
] |
+--------------------------------+
1 row
in
set
(0.00 sec)
|
* JSON_TYPE(object) 判断并显示数据类型 [ 值非法会报错显示 ]
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
mysql> SELECT JSON_TYPE(
'"lisea"'
);
+----------------------+
| JSON_TYPE(
'"lisea"'
) |
+----------------------+
| STRING |
+----------------------+
1 row
in
set
(0.01 sec)
mysql> SELECT JSON_TYPE(
'["a", "b", 1]'
);
+----------------------------+
| JSON_TYPE(
'["a", "b", 1]'
) |
+----------------------------+
| ARRAY |
+----------------------------+
1 row
in
set
(0.01 sec)
mysql> SELECT JSON_TYPE(
'1'
);
+----------------+
| JSON_TYPE(
'1'
) |
+----------------+
| INTEGER |
+----------------+
1 row
in
set
(0.00 sec)
mysql> SELECT JSON_TYPE(
'{"k1":"v1", "k2":"v2"}'
);
+-------------------------------------+
| JSON_TYPE(
'{"k1":"v1", "k2":"v2"}'
) |
+-------------------------------------+
| OBJECT |
+-------------------------------------+
1 row
in
set
(0.00 sec)
|
* JSON_MERGE(doc1,doc2....) 合并多个Json对象
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT JSON_MERGE(
'{"k1":"v1"}'
,
'{ "k2":"v2"}'
);
+------------------------------------------+
| JSON_MERGE(
'{"k1":"v1"}'
,
'{ "k2":"v2"}'
) |
+------------------------------------------+
| {
"k1"
:
"v1"
,
"k2"
:
"v2"
} |
+------------------------------------------+
1 row
in
set
(0.04 sec)
mysql> SELECT JSON_MERGE(
'["k1","k2"]'
,
'{ "k3":"v3"}'
);
+------------------------------------------+
| JSON_MERGE(
'["k1","k2"]'
,
'{ "k3":"v3"}'
) |
+------------------------------------------+
| [
"k1"
,
"k2"
, {
"k3"
:
"v3"
}] |
+------------------------------------------+
1 row
in
set
(0.00 sec)
|
* JSON_EXTRACT(object, key) 通过Json key方式获取Val值
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT JSON_EXTRACT(
'{"k1":"v1","k2":"v2"}'
,
'$.k1'
);
+-----------------------------------------------+
| JSON_EXTRACT(
'{"k1":"v1","k2":"v2"}'
,
'$.k1'
) |
+-----------------------------------------------+
|
"v1"
|
+-----------------------------------------------+
1 row
in
set
(0.01 sec)
mysql> SELECT JSON_EXTRACT(
'{"k1":"v1","k2":"v2"}'
,
'$.*'
);
+----------------------------------------------+
| JSON_EXTRACT(
'{"k1":"v1","k2":"v2"}'
,
'$.*'
) |
+----------------------------------------------+
| [
"v1"
,
"v2"
] |
+----------------------------------------------+
1 row
in
set
(0.00 sec)
|
* JSON_SET(object, key, val, key, val....) 通过key修改val值
|
1
2
3
4
5
6
7
|
mysql> SELECT JSON_SET(
'{"k1":"v1","k2":"v2"}'
,
'$.k1'
,
'lisea'
,
'$.k2'
,
'hello'
);
+---------------------------------------------------------------------+
| JSON_SET(
'{"k1":"v1","k2":"v2"}'
,
'$.k1'
,
'lisea'
,
'$.k2'
,
'hello'
) |
+---------------------------------------------------------------------+
| {
"k1"
:
"lisea"
,
"k2"
:
"hello"
} |
+---------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
|
* JSON_INSERT(object, key, val)添加新值到对象中，如果key已存在，不替换val
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT JSON_INSERT(
'{"k1":"v1","k2":"v2"}'
,
'$.k1'
,
'hello'
);
+-------------------------------------------------------+
| JSON_INSERT(
'{"k1":"v1","k2":"v2"}'
,
'$.k1'
,
'hello'
) |
+-------------------------------------------------------+
| {
"k1"
:
"v1"
,
"k2"
:
"v2"
} |
+-------------------------------------------------------+
1 row
in
set
(0.03 sec)
mysql> SELECT JSON_INSERT(
'{"k1":"v1","k2":"v2"}'
,
'$.k3'
,
'v3'
);
+----------------------------------------------------+
| JSON_INSERT(
'{"k1":"v1","k2":"v2"}'
,
'$.k3'
,
'v3'
) |
+----------------------------------------------------+
| {
"k1"
:
"v1"
,
"k2"
:
"v2"
,
"k3"
:
"v3"
} |
+----------------------------------------------------+
1 row
in
set
(0.02 sec)
|
* JSON_REPLACE() 替换现有的值并忽略新的值
|
1
2
3
4
5
6
7
|
mysql> SELECT JSON_REPLACE(
'{"k1":"v1","k2":"v2"}'
,
'$.k1'
,
'hello'
,
'$.k3'
,
'v3'
);
+----------------------------------------------------------------------+
| JSON_REPLACE(
'{"k1":"v1","k2":"v2"}'
,
'$.k1'
,
'hello'
,
'$.k3'
,
'v3'
) |
+----------------------------------------------------------------------+
| {
"k1"
:
"hello"
,
"k2"
:
"v2"
} |
+----------------------------------------------------------------------+
1 row
in
set
(0.01 sec)
|
* JSON_REMOVE() 通过key移除
|
1
2
3
4
5
6
7
|
mysql> SELECT JSON_REMOVE(
'{"k1":"v1","k2":"v2"}'
,
'$.k1'
);
+----------------------------------------------+
| JSON_REMOVE(
'{"k1":"v1","k2":"v2"}'
,
'$.k1'
) |
+----------------------------------------------+
| {
"k2"
:
"v2"
} |
+----------------------------------------------+
1 row
in
set
(0.00 sec)
|
* JSON_KEYS() 获取所有key
|
1
2
3
4
5
6
7
|
mysql> SELECT JSON_KEYS(
'{"k1":"v1","k2":"v2"}'
);
+------------------------------------+
| JSON_KEYS(
'{"k1":"v1","k2":"v2"}'
) |
+------------------------------------+
| [
"k1"
,
"k2"
] |
+------------------------------------+
1 row
in
set
(0.03 sec)
|
* JSON_UNQUOTE() 去掉值的引号
|
1
2
3
4
5
6
7
|
mysql> SELECT JSON_UNQUOTE(
'"hello"'
);
+-------------------------+
| JSON_UNQUOTE(
'"hello"'
) |
+-------------------------+
| hello |
+-------------------------+
1 row
in
set
(0.01 sec)
|
* JSON_DEPTH() 获取Json对象的深度
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT JSON_DEPTH(
'{"k1":"v1","k2":"v2"}'
);
+-------------------------------------+
| JSON_DEPTH(
'{"k1":"v1","k2":"v2"}'
) |
+-------------------------------------+
| 2 |
+-------------------------------------+
1 row
in
set
(0.00 sec)
mysql> SELECT JSON_DEPTH(
'{}'
);
+------------------+
| JSON_DEPTH(
'{}'
) |
+------------------+
| 1 |
+------------------+
1 row
in
set
(0.01 sec)
|
* JSON_VALID() 判断是否为有效的json格式
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT JSON_VALID(
'{"k1":"v1"}'
);
+---------------------------+
| JSON_VALID(
'{"k1":"v1"}'
) |
+---------------------------+
| 1 |
+---------------------------+
1 row
in
set
(0.00 sec)
mysql> SELECT JSON_VALID(
'{"k1":"v1"'
);
+--------------------------+
| JSON_VALID(
'{"k1":"v1"'
) |
+--------------------------+
| 0 |
+--------------------------+
1 row
in
set
(0.00 sec)
|
* JSON_LENGTH() 获取指定路径下的长度
长度的计算规则:
标量的长度为1
json array的长度为元素的个数
json object的长度为key的个数
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT JSON_LENGTH(
'[1, 2, 3]'
);
+--------------------------+
| JSON_LENGTH(
'[1, 2, 3]'
) |
+--------------------------+
| 3 |
+--------------------------+
1 row
in
set
(0.00 sec)
mysql> SELECT JSON_LENGTH(
'{"k1":"v1", "k2":"v2"}'
);
+---------------------------------------+
| JSON_LENGTH(
'{"k1":"v1", "k2":"v2"}'
) |
+---------------------------------------+
| 2 |
+---------------------------------------+
1 row
in
set
(0.00 sec)
|
* 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下查。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> SELECT JSON_SEARCH(
'{"k1":"v1", "k2":"v2", "k3":"v2"}'
,
'one'
,
'v2%'
);
+----------------------------------------------------------------+
| JSON_SEARCH(
'{"k1":"v1", "k2":"v2", "k3":"v2"}'
,
'one'
,
'v2%'
) |
+----------------------------------------------------------------+
|
"$.k2"
|
+----------------------------------------------------------------+
1 row
in
set
(0.00 sec)
mysql> SELECT JSON_SEARCH(
'{"k1":"v1", "k2":"v2", "k3":"v2"}'
,
'all'
,
'v2%'
);
+----------------------------------------------------------------+
| JSON_SEARCH(
'{"k1":"v1", "k2":"v2", "k3":"v2"}'
,
'all'
,
'v2%'
) |
+----------------------------------------------------------------+
| [
"$.k2"
,
"$.k3"
] |
+----------------------------------------------------------------+
1 row
in
set
(0.01 sec)
|
4. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1943339，如需转载请自行联系原作者
