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. 
   

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

10. +--------------------------------------------+
    

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

12. +--------------------------------------------+
    

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

14. +--------------------------------------------+
    

15. 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": "dba@youzan.com" }],
   

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

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

修改

1. mysql> select * from json_test \G
   

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

3.   id: 1
   

4. 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"}]}
   

5. 1 row in set (0.00 sec)
   

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

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

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

9. 
   

1. mysql> select * from json_test \G
   

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

3.   id: 1
   

4. 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\"}"]}
   

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": "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"}]}
   

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": "dba@youzan.com", "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是怎么樣的有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。