MySQL 新特性应用JSON-DBAspace

julley

　　MySQL5.7之JSON使用
　　MySQL 支持原生的 JSON 数据类型，列不能设置默认值
　　从MySQL 5.7.8 开始，MySQL支持原生的JSON格式，即有独立的json类型，用于存放 json格式的数据。
　　JSON 格式的数据并不是以string格式存储于数据库而是以内部的binary 格式，以便于快速的定位到json 格式中值
　　在插入和更新操作时MySQL会对JSON 类型做校验，已检查数据是否符合json格式，如果不符合则报错。
　　同时5.7.8 版本提供了四种JSON相关的函数，从而不用遍历全部数据
　　a 创建: JSON_ARRAY(), JSON_MERGE(), JSON_OBJECT()
　　b 修改: JSON_APPEND(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT(), JSON_INSERT(), JSON_QUOTE(), JSON_REMOVE(), JSON_REPLACE(),
　　JSON_SET(), and JSON_UNQUOTE()
　　c 查询: JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_EXTRACT(), JSON_KEYS(),JSON_SEARCH().
　　d 属性: JSON_DEPTH(), JSON_LENGTH(), JSON_TYPE() JSON_VALID().
　　CREATE TABLE t1(id INT PRIMARY KEY AUTO_INCREMENT,jdoc json)
　　1、插入 JSON
　　插入 json 格式的字符串，可以是对象的形式，也可以是数组的形式
　　INSERT INTO t1(jdoc)VALUES('{"key1":"value1","key2":"value2"}')
　　INSERT INTO t1(jdoc)VALUE('[1,2,3]')
　　2、查看插入的JSON数据类型:json_type()
　　SELECT json_type(jdoc) FROM t1
　　mysql> select json_type('"abc"') ;
　　+--------------------+
　　| json_type('"abc"') |
　　+--------------------+
　　| STRING             |
　　+--------------------+
　　mysql> select * from t1;
　　+----+--------------------------------------+

　　|>　　+----+--------------------------------------+
　　|  1 | {"key1": "value1", "key2": "value2"} |
　　|  2 | [1, 2, 3]                            |
　　+----+--------------------------------------+
　　2 rows in set (0.00 sec)
　　mysql> select json_type(jdoc) from t1;
　　+-----------------+
　　| json_type(jdoc) |
　　+-----------------+
　　| OBJECT          |
　　| ARRAY           |
　　+-----------------+
　　mysql> select json_type('["a","b",1]');
　　+--------------------------+
　　| json_type('["a","b",1]') |
　　+--------------------------+
　　| ARRAY                    |
　　+--------------------------+
　　1 row in set (0.00 sec)
　　3、用专门的函数将字符串变成JSON数组格式存储，json_object()函数根据键值对返回JSON对象
　　json_array
　　mysql> select json_array("a",1,now());
　　+----------------------------------------+
　　| json_array("a",1,now())                |
　　+----------------------------------------+
　　| ["a", 1, "2017-11-02 01:47:38.000000"] |
　　+----------------------------------------+
　　json_object
　　mysql> select json_object("key1",1,"key2","abc");
　　+------------------------------------+
　　| json_object("key1",1,"key2","abc") |
　　+------------------------------------+
　　| {"key1": 1, "key2": "abc"}         |
　　+------------------------------------+
　　1 row in set (0.00 sec)
　　json_merge():将两个或两个以上的JSON文档合并成一个列表对象
　　mysql> select json_merge('["a",1,2,3]','{"key1":"value"}');
　　+----------------------------------------------+
　　| json_merge('["a",1,2,3]','{"key1":"value"}') |
　　+----------------------------------------------+
　　| ["a", 1, 2, 3, {"key1": "value"}]            |
　　+----------------------------------------------+
　　若是多个列表的JSON格式会被合并成一个列表存储
　　mysql> SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');
　　+-----------------------------------------------------+
　　| JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') |
　　+-----------------------------------------------------+
　　| [1, 2, "a", "b", true, false]                       |
　　+-----------------------------------------------------+
　　1 row in set (0.00 sec)
　　若多个对象有相同的键，值会合并成一个数组。
　　4、将JSON值自定义一个变量
　　mysql> set  @a=json_object("key1","sharesoe");
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> select @a;
　　+----------------------+
　　| @a                   |
　　+----------------------+
　　| {"key1": "sharesoe"} |
　　+----------------------+
　　1 row in set (0.00 sec)
　　5、查询和修改JSON值
　　通过提取JSON中键获取值
　　mysql> select json_extract(jdoc,'$.key1'),json_extract(jdoc,'$.key2')from t1;
　　+-----------------------------+-----------------------------+
　　| json_extract(jdoc,'$.key1') | json_extract(jdoc,'$.key2') |
　　+-----------------------------+-----------------------------+
　　| "value1"                    | "value2"                    |
　　+-----------------------------+-----------------------------+
　　1 row in set (0.00 sec)
　　查询 json 中的数据用 column->path 的形式，其中对象类型 path 这样表示 $.path, 而数组类型则是 $[index]

　　mysql> select>　　+----+----------------+----------------+

　　|>　　+----+----------------+----------------+
　　|  1 | "value1"       | "value2"       |
　　+----+----------------+----------------+
　　可以用 JSON_UNQUOTE 函数将双引号去掉，从 MySQL 5.7.13 起也可以通过这个操作符 ->> 这个和 JSON_UNQUOTE 是等价的

　　mysql> select>　　+----+-----------------+-----------------+

　　|>　　+----+-----------------+-----------------+
　　|  1 | value1          | value2          |
　　+----+-----------------+-----------------+
　　1 row in set (0.00 sec)

　　mysql> select>　　+----+------------------------------+----------------+

　　|>　　+----+------------------------------+----------------+
　　|  1 | value1                       | "value2"       |
　　+----+------------------------------+----------------+
　　JSON 作为条件进行搜索。因为 JSON 不同于字符串，所以如果用字符串和 JSON 字段比较，是不会相等的
　　mysql> select * from t1 where jdoc='{"key1":"value1","key2":"value2"}';
　　Empty set (0.00 sec)
　　可以通过 CAST 将字符串转成 JSON 的形式
　　mysql> select * from t1 where jdoc=cast('{"key1":"value1","key2":"value2"}' as json);
　　+----+--------------------------------------+

　　|>　　+----+--------------------------------------+
　　|  1 | {"key1": "value1", "key2": "value2"} |
　　+----+--------------------------------------+
　　通过 JSON 中的元素进行查询, 对象型的查询同样可以通过 column->path
　　mysql> select * from t1 where jdoc->'$.key1'="value1";
　　+----+--------------------------------------+

　　|>　　+----+--------------------------------------+
　　|  1 | {"key1": "value1", "key2": "value2"} |
　　+----+--------------------------------------+
　　要特别注意的是，JSON 中的元素搜索是严格区分变量类型的，比如说整型和字符串是严格区分的
　　用JSON_CONTAINS 函数，但和 column->path 的形式有点相反的是，JSON_CONTAINS 第二个参数是不接受整数的，若第2个位字符也是有问题
　　select * from t1 where JSON_CONTAINS(jdoc,"value1","$.key1");
　　更新 JSON
　　如果是整个 json 更新的话，和插入时类似的。但如果要更新 JSON 下的元素，MySQL 并不支持 column->path 的形式
　　JSON_INSERT() 插入新值，但不会覆盖已经存在的值

　　mysql> update t1 set jdoc=json_insert(jdoc,'$.key1','abcd','$.name','www.sharesoe.com') where>　　Query OK, 1 row affected (0.11 sec)
　　mysql> select * from t1;
　　+----+------------------------------------------------------------------+

　　|>　　+----+------------------------------------------------------------------+
　　|  1 | {"key1": "value1", "key2": "value2", "name": "www.sharesoe.com"} |
　　+----+------------------------------------------------------------------+
　　1 row in set (0.00 sec)
　　JSON_SET() 插入新值，并覆盖已经存在的值

　　mysql> update t1 set jdoc=json_set(jdoc,'$.key1','abcd','$.host','www.sharesoe.com') where>　　Query OK, 1 row affected (0.39 sec)
　　mysql> select * from t1;
　　+----+--------------------------------------------------------------------------------------------+

　　|>　　+----+--------------------------------------------------------------------------------------------+
　　|  1 | {"host": "www.sharesoe.com", "key1": "abcd", "key2": "value2", "name": "www.sharesoe.com"} |
　　+----+--------------------------------------------------------------------------------------------+
　　1 row in set (0.00 sec)
　　JSON_REPLACE() 只替换存在的值

　　mysql> update t1 set jdoc=json_replace(jdoc,'$.key1','abcd1','$.host1','www.sharesoe.com') where>　　Query OK, 1 row affected (0.14 sec)
　　mysql> select * from t1;
　　+----+---------------------------------------------------------------------------------------------+

　　|>　　+----+---------------------------------------------------------------------------------------------+
　　|  1 | {"host": "www.sharesoe.com", "key1": "abcd1", "key2": "value2", "name": "www.sharesoe.com"} |
　　+----+---------------------------------------------------------------------------------------------+
　　1 row in set (0.00 sec)
　　JSON_REMOVE() 删除 JSON 元素

　　mysql> update t1 set jdoc=json_remove(jdoc,'$.key1','$.host') where>　　Query OK, 1 row affected (0.28 sec)
　　mysql> select * from t1;
　　+----+------------------------------------------------+

　　|>　　+----+------------------------------------------------+
　　|  1 | {"key2": "value2", "name": "www.sharesoe.com"} |
　　+----+------------------------------------------------+
　　1 row in set (0.00 sec)
　　对于使用MySQL varchar 存储json的业务是一个福音。在设计表的时候，避免大字段存储。
　　一来是减少不必要的查询带来的IO，带宽，内存方面的影响 二来是 避免因为表大小太大导致的ddl 时间成本增加系统风险。