## 介紹 本文將介紹 MySQL 5.7 中如何實現非結構化（JSON）數據的存儲，在介紹 MySQL 5.7 的非結構化數據存儲之前，首先介紹在之前的 MySQL 的版本中，用戶如何通過 BLOB 實現 JSON 對象的存儲，以及這樣處理的缺點是什么，這些缺點也就是 MySQL 5.7 支持 JSON 的理由；然后我們介紹了 MySQL 5.7 如何支持 JSON 格式，本文將重點關注MySQL 5.7 JSON 的存儲格式。 ## 5.7 之前 BLOB 方式實現 JSON 對象的存儲 MySQL 是一個關系型數據庫，在 MySQL 5.7 之前，沒有提供對非結構化數據的支持，但是如果用戶有這樣的需求，也可以通過 MySQL 的 BLOB 來存儲非結構化的數據。如下所示： ~~~ mysql> create table t(json_data blob); Query OK, 0 rows affected (0.13 sec) mysql> insert into t values('{"key1":"data1", "key2":2, "key3":{"sub_key1":"sub_val1"}}'); Query OK, 1 row affected (0.01 sec) mysql> select * from t; +------------------------------------------------------------+ | json_data | +------------------------------------------------------------+ | {"key1":"data1", "key2":2, "key3":{"sub_key1":"sub_val1"}} | +------------------------------------------------------------+ 1 row in set (0.00 sec) ~~~ 在本例中，我們使用 BLOB 來存儲 JSON 數據，使用這種方法，需要用戶保證插入的數據是一個能夠轉換成 JSON 格式的字符串，MySQL 并不保證任何正確性。在MySQL看來，這就是一個普通的字符串，并不會進行任何有效性檢查，此外提取 JSON 中的字段，也需要用戶的代碼中完成，如下所示： ~~~ #!/usr/bin/python import pymysql import json try: conn = pymysql.connect(host="127.0.0.1", db="test", user="root", passwd="root", port=7799) sql = "select * from t" cur = conn.cursor() cur.execute(sql) rows = cur.fetchall() print json.dumps(json.loads(rows[0][0]), indent=4) except: conn.close() ~~~ 執行python腳本的結果如下所示： ~~~ root@dev1:~# python test.py { "key3": { "sub_key1": "sub_val1" }, "key2": 2, "key1": "data1" } ~~~ 這種方式雖然也能夠實現 JSON 的存儲，但是有諸多缺點，最為顯著的缺點有： 1. 需要用戶保證 JSON 的正確性，如果用戶插入的數據并不是一個有效的 JSON 字符串，MySQL 并不會報錯； 2. 所有對 JSON 的操作，都需要在用戶的代碼里進行處理，不夠友好； 3. 即使只是提取 JSON 中某一個字段，也需要讀出整個 BLOB，效率不高； 4. 無法在 JSON 字段上建索引。 ## 5.7中的JSON實現 MySQL本身已經是一個比較完備的數據庫系統，對于底層存儲并不適合有太大的改動，那么 MySQL 是如何支持 JSON 格式的呢？說來也巧，和我們前面的做法幾乎一樣——通過 BLOB 來存儲。也就是說，MySQL 5.7支持 JSON 的做法是，在server層提供了一堆便于操作 JSON 的函數，至于存儲，就是簡單地將 JSON 編碼成 BLOB，然后交由存儲引擎層進行處理，也就是說，MySQL 5.7的JSON 支持與存儲引擎沒有關系，MyISAM 存儲引擎也支持 JSON 格式，如下所示： ~~~ mysql> create table t_innodb(data json)engine=innodb; Query OK, 0 rows affected (0.18 sec) mysql> insert into t_innodb values('{"key":"val"}'); Query OK, 1 row affected (0.03 sec) mysql> create table t_myisam(data json)engine=myisam; Query OK, 0 rows affected (0.02 sec) mysql> insert into t_myisam values('{"key":"val"}'); Query OK, 1 row affected (0.00 sec) ~~~ MySQL 5.7 提供了很多操作 JSON 的函數，都是為了提高易用性，可以參考[官方文檔](https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html)。本文將主要關注實現。 關于MySQL 5.7的JSON存儲，MySQL的源碼里寫得比較清楚，在sql/json_binary.h中有下面這段注釋： ~~~ If the value is a JSON object, its binary representation will have a header that contains: - the member count - the size of the binary value in bytes - a list of pointers to each key - a list of pointers to each value The actual keys and values will come after the header, in the same order as in the header. Similarly, if the value is a JSON array, the binary representation will have a header with - the element count - the size of the binary value in bytes - a list of pointers to each value ~~~ 從注釋里面我們可以知道，對于JSON數組和JSON對象，MySQL如何編碼成BLOB對象，數組比較簡單，下面給出JSON對象的示意圖（見json_binary.cc中的`serialize_json_object`函數），如下所示：  JSON 對象 說明如下：首先存放的是 JSON 的元素個數，然后存放的是轉換成 BLOB 以后的字節數，接下來存放的是key pointers和value pointers。為了加快查找速度，MySQL 內部會對key進行排序，以便對key進行二分查找，以提高處理速度。 此外，對于key pointers，有如下注釋： ~~~ /* The size of key entries for objects when using the small storage format or the large storage format. In the small format it is 4 bytes (2 bytes for key length and 2 bytes for key offset). In the large format it is 6 (2 bytes for length, 4 bytes for offset). */ #define KEY_ENTRY_SIZE_SMALL (2 + SMALL_OFFSET_SIZE) #define KEY_ENTRY_SIZE_LARGE (2 + LARGE_OFFSET_SIZE) ~~~ 也就是說，在MySQL 5.7中，key的長度只用2個字節保存（65535），如果超過這個長度，MySQL將報錯，如下所示： ~~~ mysql> insert into t1 values(JSON_OBJECT(repeat('a', 65535), 'val')); Query OK, 1 row affected (0.37 sec) mysql> insert into t1 values(JSON_OBJECT(repeat('a', 65536), 'val')); ERROR 3151 (22032): The JSON object contains a key name that is too long. ~~~ 如果查看MySQL的源碼，可以看到，與JSON相關的文件有： ~~~ json_binary.cc json_binary.h json_dom.cc json_dom.h json_path.cc json_path.h ~~~ 其中，json_binary 處理JSON 的編碼、解碼，json_dom 是 JSON 的內存表示，json_path 用以將字符串解析成 JSON，具體說明見[WL#7909](http://dev.mysql.com/worklog/task/?id=7909)。 對于 JSON 的編碼，入口是json_binary.cc 文件中的`serialize`函數，對于 JSON 的解碼，即將 BLOB 解析成 JSON 對象，入口是json_binary.cc文件中的`parse_binary`函數，只要搞清楚了 JSON 的存儲格式，這兩個函數是很好理解的。 > 作者介紹 > 賴明星 廈門大學碩士畢業，網易杭研服務器端開發工程師，MySQL 愛好者，網名“不知一不知二”。