Mysql5.7的json存储以及sys schema是怎么样的

今天就跟大家聊聊有关Mysql5.7的json存储以及sys schema是怎么样的,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。

重点介绍新版本对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有一定的认识。
创建

  1. mysql> SELECT JSON_ARRAY(‘id’, 1, ‘name’, ‘dba@youzan’);

  2. +——————————————-+

  3. | JSON_ARRAY(‘id’, 1, ‘name’, ‘dba@youzan’) |

  4. +——————————————-+

  5. | [“id”, 1, “name”, “dba@youzan”] |

  6. +——————————————-+

  7. 1 row in set (0.00 sec)

  8. mysql> SELECT JSON_OBJECT(‘id’, 1, ‘name’, ‘dba@youzan’);

  9. +——————————————–+

  10. | JSON_OBJECT(‘id’, 1, ‘name’, ‘dba@youzan’) |

  11. +——————————————–+

  12. | {“id”: 1, “name”: “dba@youzan”} |

  13. +——————————————–+

  14. 1 row in set (0.00 sec)

初始化 

  1. create table json_test (

  2.     id int(11) PRIMARY KEY NOT NULL auto_increment,

  3.     data json

  4.     ) engine=innodb default charset=utf8;

  5. insert into json_test values (1,'{ “DBA”: [ { “firstName”: “yi”, “lastName”:”yang”, “email”: “[email protected]” }],

  6. “SA”: [{ “firstName”: “you”, “lastName”: “zan”, “email”: “[email protected]” }],

  7. “PE”: [{ “firstName”: “xiao”, “lastName”: “xiao”, “email”: “[email protected]” }] }’)

修改

  1. mysql> select * from json_test \\G

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

  3.   id: 1

  4. data: {“PE”: [{“email”: “[email protected]”, “lastName”: “xiao”, “firstName”: “xiao”}], “SA”: [{“email”: “[email protected]”, “lastName”: “zan”, “firstName”: “you”}], “DBA”: [{“email”: “[email protected]”, “lastName”: “yang”, “firstName”: “yi”}]}

  5. 1 row in set (0.00 sec)

  6. mysql> update json_test set data=json_array_append(data,’$.DBA’,'{“email”: “[email protected]”, “lastName”: “yang”, “firstName”: “qilong”}’) where id=1;

  7. Query OK, 1 row affected (0.01 sec)

  8. Rows matched: 1 Changed: 1 Warnings: 0

  1. mysql> select * from json_test \\G

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

  3.   id: 1

  4. data: {“PE”: [{“email”: “[email protected]”, “lastName”: “xiao”, “firstName”: “xiao”}], “SA”: [{“email”: “[email protected]”, “lastName”: “zan”, “firstName”: “you”}], “DBA”: [{“email”: “[email protected]”, “lastName”: “yang”, “firstName”: “yi”}, “{\\”email\\”: \\”[email protected]\\”, \\”lastName\\”: \\”yang\\”, \\”firstName\\”: \\”qilong\\”}”]}

  5. 1 row in set (0.00 sec)

删除 

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

  2. Query OK, 1 row affected (0.01 sec)

  3. Rows matched: 1 Changed: 1 Warnings: 0

  4. mysql> select * from json_test \\G

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

  6.   id: 1

  7. data: {“PE”: [{“email”: “[email protected]”, “lastName”: “xiao”, “firstName”: “xiao”}], “SA”: [{“email”: “[email protected]”, “lastName”: “zan”, “firstName”: “you”}], “DBA”: [{“email”: “[email protected]”, “lastName”: “yang”, “firstName”: “yi”}]}

  8. 1 row in set (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

  1. mysql> SELECT id,json_keys(data) as “keys” FROM json_test;

  2. +—-+———————+

  3. | id | keys |

  4. +—-+———————+

  5. | 1 | [“PE”, “SA”, “DBA”] |

  6. +—-+———————+

  7. 1 row in set (0.00 sec)

  8. 查看DBA对应的值 

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

  10. +—-+——————————————————————–+

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

  12. +—-+——————————————————————–+

  13. | 1 | {“email”: “[email protected]”, “lastName”: “yang”, “firstName”: “yi”}  |

  14. +—-+——————————————————————–+

  15. 1 row in set (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 功能的强大。

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

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

  3.      table_schema: yang

  4.        table_name: json_test

  5.     total_latency: 1.81 ms

  6.      rows_fetched: 21

  7.     fetch_latency: 1.45 ms

  8.     rows_inserted: 2

  9.    insert_latency: 192.67 us

  10.      rows_updated: 2

  11.    update_latency: 166.94 us

  12.      rows_deleted: 0

  13.    delete_latency: 0 ps

  14.  io_read_requests: 54

  15.           io_read: 4.21 KiB

  16.   io_read_latency: 289.37 us

  17. io_write_requests: 43

  18.          io_write: 388.53 KiB

  19.  io_write_latency: 703.51 us

  20.  io_misc_requests: 75

  21.   io_misc_latency: 40.02 ms

  22. ##直接查看未使用过的索引 ,方便吗?

  23. mysql> SELECT * FROM schema_unused_indexes;

  24. +—————+————-+————+

  25. | object_schema | object_name | index_name |

  26. +—————+————-+————+

  27. | yang          | t           | idx_a |

  28. | yang          | yy          | idx_nm |

  29. +—————+————-+————+

  30. 2 rows in set (0.00 sec)

看完上述内容,你们对Mysql5.7的json存储以及sys schema是怎么样的有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注云搜网行业资讯频道,感谢大家的支持。


【AD】美国洛杉矶/香港/日本VPS推荐,回程电信CN2 GIA线路,延迟低、稳定性高、免费备份_搬瓦工

【AD】炭云:36元/年/1GB内存/20GB SSD空间/500GB流量/5Gbps端口/KVM/香港/国际线路LUMEN