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，如需转载请自行联系原作者