一寫在前面
本系列文章基於 5.7.12 版本講述MySQL的新特性。從安裝，檔案結構，SQL ,最佳化，運維層面複製，GITD等幾個方面展開介紹 5.7 的新特性和功能。同時也建議大家跟蹤官方blog和官方文件，以儘快知悉其新的變化。本文將重點介紹新版本對JSON格式的支援。
5.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 row in set (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 row in set (0.00 sec)

初始化

create table json_test (
id int(11) PRIMARY KEY NOT NULL auto_increment,
data json
) engine=innodb default charset=utf8;
insert into json_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 row in set (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 row in set (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 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

mysql> SELECT id,json_keys(data) as "keys" FROM json_test;
+----+---------------------+
| id | keys |
+----+---------------------+
| 1 | ["PE", "SA", "DBA"] |
+----+---------------------+
1 row in set (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 row in set (0.00 sec)

其他函式的用法請感興趣的讀者朋友自行參考《官方文件》
MySQL 5.7 版本提供的json格式以及對應的操作函式極豐富了MySQL的儲存格式,可以在一定程度上和Mongodb和pg競爭，對於經常使用MySQL varchar 儲存json的業務是一個福音。同時再強調一下對於OLTP業務的表結構設計儘可能的避免大欄位儲存。一來是減少不必要的查詢帶來的IO，頻寬，記憶體方面的影響二來是避免因為表大小太大導致的ddl 時間成本增加系統風險。
5.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 rows in set (0.00 sec)

參考文章
[1] 《MySQL 5.7 官方文件》
[2] 《MySQL 5.7 初探》
[3] 《MySQL 5.7新特性之一》
[4] 《MySQL 5.7新特性之二》
[5] 《MySQL 5.7新特性之三》
[6] 《MySQL 5.7新特性之四》

【Mysql】Mysql5.7新特性之-json儲存

相關文章