2018-03-28

MySQL json 数据类型

必须要5.7以上版本才能使用

写在开头

mysql json 的功能很强大，只是用来当一个储存数据的字段就没什么意义了。

使用proto做交互的话，只要JSON 写得好用proro.Unmarshal() 就可以很方便的转换类型可以精简很多代码

JSON path 是以 $ 开头，之后就是JSON的层级使用。

$
$[0].name
$[0][1]
//*表示所有
$[*]

数据比较

json中的数据可以用 =, <, <=, >, >=, <>, !=, and <=> 进行比较。

json里的数据类型是多样的，在不同类型之间进行比较时，高优先级的要大于低优先级的（可以用JSON_TYPE()函数查看类型）。

优先级从高到低如下：

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

函数整理

创建函数

JSON_ARRAY: JSON_ARRAY(val1,val2,val3…)
JSON_OBJECT: 生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数，则抛出异常。
JSON_QUOTE:JSON_QUOTE(json_val) 将json_val用””号括起来。
CONVERT: CONVERT(json_string,JSON)

查询函数

JSON_CONTAINS：JSON_CONTAINS(json_doc,var,[path])

查询json文档是否在指定path包含指定的数据，包含则返回1，否则返回0. 如果有参数为null或者path不存在则返回null
JSON_CONTAINS_PATH：JSON_CONTAINS_PATH(json_doc,one_or_all,path,path…..)

查询是否存在指定路径，存在则返回1，否则返回0.如果有参数为NULL，则返回NULL。

one_or_all只能取值“one”或”all”,one表示只要有一个存在即可;all表示所有的都存在才行。
JSON_EXTRACT：JSON_EXTRACT(json_doc, path[, path] …)

5.7.9及以上版本可以用”->”替代JSON_EXTRACT

从json文档里抽取数据。如果有参数有NULL或path不存在，则返回NULL。如果抽取出多个path，则返回的数据封闭在一个json array里。
JSON_UNQUOTE:表示去掉抽取结果的”号
- JSON_UNQUOTE( JSON_EXTRACT(column, path) )
- JSON_UNQUOTE(column -> path)
- column->>path

JSON_KEYS:JSON_KEYS(json_doc[, path])

获取json文档在指定路径下的所有键值，返回一个json array。如果有参数为NULL或path不存在，则返回NULL

    mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
    +---------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
    +---------------------------------------+
    | ["a", "b"]                            |
    +---------------------------------------+
    mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
    +----------------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
    +----------------------------------------------+
    | ["c"]                                        |
    +----------------------------------------------+
    ```      
- JSON_SEARCH:JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

   查询包含指定字符串的paths，并作为一个json array返回。如果有参数为NUL或path不存在，则返回NULL。
    
    one_or_all："one"表示查询到一个即返回；"all"表示查询所有。
    
    search_str：要查询的字符串。 可以用LIKE里的'%'或‘_’匹配。
    
    path：在指定path下查。
    ```mysql
    mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
     
    mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
    +-------------------------------+
    | JSON_SEARCH(@j, 'one', 'abc') |
    +-------------------------------+
    | "$[0]"                        |
    +-------------------------------+
     
    mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
    +-------------------------------+
    | JSON_SEARCH(@j, 'all', 'abc') |
    +-------------------------------+
    | ["$[0]", "$[2].x"]            |
    +-------------------------------+
    ```  

##### 修改函数

- JSON_ARRAY_APPEND:JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...) 

    在指定path的json array尾部追加val。如果指定path是一个json object，则将其封装成一个json array再追加。如果有参数为NULL，则返回NULL
    ```mysql
    mysql> SET @j = '["a", ["b", "c"], "d"]';
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
    +----------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[1]', 1) |
    +----------------------------------+
    | ["a", ["b", "c", 1], "d"]        |
    +----------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
    +----------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[0]', 2) |
    +----------------------------------+
    | [["a", 2], ["b", "c"], "d"]      |
    +----------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
    +----------------------------------+
    | JSON_ARRAY_APPEND(@j, '$', z) |
    +----------------------------------+
    | ["a", ["b", "c"], "d","z"]      |
    +----------------------------------+

JSON_ARRAY_INSERT:JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)

在path指定的json array元素插入val，原位置及以右的元素顺次右移。如果path指定的数据非json array元素，则略过此val；

如果指定的元素下标超过json array的长度，则插入尾部。
JSON_INSERT:JSON_INSERT(json_doc, path, val[, path, val] …)

在指定path下插入数据，如果path已存在，则忽略此val（不存在才插入）。
JSON_REPLACE:JSON_REPLACE(json_doc, path, val[, path, val] …)

替换指定路径的数据，如果某个路径不存在则略过（存在才替换）。如果有参数为NULL，则返回NULL。
JSON_SET :JSON_SET(json_doc, path, val[, path, val] …)

设置指定路径的数据（不管是否存在）。如果有参数为NULL，则返回NULL。
JSON_MERGE:JSON_MERGE(json_doc, json_doc[, json_doc] …)
- 如果都是json array，则结果自动merge为一个json array；
- 如果都是json object，则结果自动merge为一个json object；
- 如果有多种类型，则将非json array的元素封装成json array再按照规则一进行mege。
JSON_REMOVE: JSON_REMOVE(json_doc, path[, path] …)

移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。

特性查询

JSON_DEPTH:JSON_DEPTH(json_doc)

获取json文档的深度。如果参数为NULL，则返回NULL。

空的json array、json object或标量的深度为1。
JSON_LENGTH: JSON_LENGTH(json_doc[, path])

获取指定路径下的长度。如果参数为NULL，则返回NULL。
- 标量的长度为1
- json array的长度为元素的个数
- json object的长度为key的个数
JSON_TYPE: JSON_TYPE(json_val)

获取json文档的具体类型。如果参数为NULL，则返回NULL。
JSON_VALID: JSON_VALID(val)

判断val是否为有效的json格式，是为1，不是为0。如果参数为NUL，则返回NULL。