mysql 支持json_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 bytes4G

3. Json相关函数操作

* JSON_OBJECT(string1, string2...) 创建 key-value 类型 Json 对象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 数组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) 判断并显示数据类型 [ 值非法会报错显示 ]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对象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值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值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已存在，不替换valmysql> 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() 替换现有的值并忽略新的值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移除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() 获取所有keymysql> SELECT JSON_KEYS('{"k1":"v1","k2":"v2"}');

+------------------------------------+

| JSON_KEYS('{"k1":"v1","k2":"v2"}') |

+------------------------------------+

| ["k1", "k2"]                       |

+------------------------------------+

1 row in set (0.03 sec)

* JSON_UNQUOTE() 去掉值的引号mysql> SELECT JSON_UNQUOTE('"hello"');

+-------------------------+

| JSON_UNQUOTE('"hello"') |

+-------------------------+

| hello                   |

+-------------------------+

1 row in set (0.01 sec)

* JSON_DEPTH() 获取Json对象的深度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格式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的个数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下查。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. 总结

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