重点介绍新版本对JSON格式的支持。

1 支持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().
我们通过简单的例子来对json有一定的认识。
创建

mysql>SELECT JSON_ARRAY('id',1,'name','dba@youzan');

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

|JSON_ARRAY('id',1,'name','dba@youzan')|

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

|["id",1,"name","dba@youzan"]|

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

1 rowinset(0.00 sec)

mysql>SELECT JSON_OBJECT('id',1,'name','dba@youzan');

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

|JSON_OBJECT('id',1,'name','dba@youzan')|

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

|{"id":1,"name":"dba@youzan"}|

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

1 rowinset(0.00 sec)

初始化

create table json_test(

idint(11)PRIMARY KEYNOTNULL auto_increment,

data json

)engine=innodb default charset=utf8;

insertintojson_test values(1,'{ "DBA": [ { "firstName": "yi", "lastName":"yang", "email": "dba@youzan.com" }],

"SA": [{ "firstName": "you", "lastName": "zan", "email": "sa@youzan.com" }],

"PE": [{ "firstName": "xiao", "lastName": "xiao", "email": "pe@youzan.com" }] }')

修改

mysql>select*from json_test \G

***************************1.row***************************

id:1

data:{"PE":[{"email":"pe@youzan.com","lastName":"xiao","firstName":"xiao"}],"SA":[{"email":"sa@youzan.com","lastName":"zan","firstName":"you"}],"DBA":[{"email":"dba@youzan.com","lastName":"yang","firstName":"yi"}]}

1 rowinset(0.00 sec)

mysql>update json_test set data=json_array_append(data,'$.DBA','{"email": "dba@youzan.com", "lastName": "yang", "firstName": "qilong"}')where id=1;

Query OK,1 row affected(0.01 sec)

Rows matched:1 Changed:1 Warnings:0

mysql>select*from json_test \G

***************************1.row***************************

id:1

data:{"PE":[{"email":"pe@youzan.com","lastName":"xiao","firstName":"xiao"}],"SA":[{"email":"sa@youzan.com","lastName":"zan","firstName":"you"}],"DBA":[{"email":"dba@youzan.com","lastName":"yang","firstName":"yi"},"{\"email\": \"dba@youzan.com\", \"lastName\": \"yang\", \"firstName\": \"qilong\"}"]}

1 rowinset(0.00 sec)

删除

mysql>update json_test set data=json_remove(data,'$.DBA[1]')where id=1;

Query OK,1 row affected(0.01 sec)

Rows matched:1 Changed:1 Warnings:0

mysql>select*from json_test \G

***************************1.row***************************

id:1

data:{"PE":[{"email":"pe@youzan.com","lastName":"xiao","firstName":"xiao"}],"SA":[{"email":"sa@youzan.com","lastName":"zan","firstName":"you"}],"DBA":[{"email":"dba@youzan.com","lastName":"yang","firstName":"yi"}]}

1 rowinset(0.00 sec)

-- 删除语句
update history_tab_sum set data=json_remove(data,'$."2017.11.28"') ;

-- 更新
-- update history_tab_sum set total=json_replace(total,'$."2017-11-28"',id);

-- 插入：
UPDATE history_tab_sum set data=JSON_MERGE(data,'{"2017-11-28":3344}') where id=10283;

-- append
update history_tab_sum set data=json_array_append(data,'$."2017-11-28"',id) where id=10283;




查看 json的key

mysql>SELECT id,json_keys(data)as"keys"FROM json_test;

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

|id|keys|

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

|1|["PE","SA","DBA"]|

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

1 rowinset(0.00 sec)

查看DBA对应的值

mysql>SELECT id,json_extract(data,'$.DBA[0]')from json_test;

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

|id|json_extract(data,'$.DBA[0]')|

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

|1|{"email":"dba@youzan.com","lastName":"yang","firstName":"yi"}|

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

1 rowinset(0.00 sec)

MySQL 5.7 版本提供的json格式以及对应的操作函数极丰富了MySQL的存储格式,可以在一定程度上和Mongodb和pg竞争，对于经常使用MySQL varchar 存储json的业务是一个福音。同时再强调一下对于OLTP业务的表结构设计 尽可能的避免大字段存储。一来是减少不必要的查询带来的IO，带宽，内存方面的影响 二来是 避免因为表大小太大导致的ddl 时间成本增加系统风险。
2 sys schema
MySQL 5.7 版本新增了sys 数据库,该库通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据，帮助DBA快速获取数据库系统的各种纬度的元数据信息，帮助DBA和开发快速定位性能瓶颈。这里给两个例子能直观的了解sys 功能的强大。

mysql>select*from sys.schema_table_statistics limit 2\G

***************************1.row***************************

table_schema:yang

table_name:json_test

total_latency:1.81 ms

rows_fetched:21

fetch_latency:1.45 ms

rows_inserted:2

insert_latency:192.67 us

rows_updated:2

update_latency:166.94 us

rows_deleted:0

delete_latency:0 ps

io_read_requests:54

io_read:4.21 KiB

io_read_latency:289.37 us

io_write_requests:43

io_write:388.53 KiB

io_write_latency:703.51 us

io_misc_requests:75

io_misc_latency:40.02 ms

##直接查看未使用过的索引 ，方便吗？

mysql>SELECT*FROM schema_unused_indexes;

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

|object_schema|object_name|index_name|

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

|yang |t |idx_a|

|yang |yy |idx_nm|

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

2 rowsinset(0.00 sec)