2017年1月16日 星期一

MySQL 支援JSON文件面面觀(之一)

- 詳解新世代MySQL如何支持NoSQL應用開發


MySQL面世了20多年,在大家的認知中它都是一個關聯式資料庫,但是從2016年起MySQL有了一個革命性的變化-它除了是一個說SQL語言的關聯式資料庫,它也學會了第二種語言-JSON,它現在也是一個NoSQL文件型資料庫。

大約在兩年前,Oracle的MySQL產品部門體認到了當今的軟體工程正朝向雛型法,快速開發,和開發維運(DevOp)的方向發展。傳統的關聯式資料庫雖以嚴僅稱著,強調資料完整性,和透過正規化帶來查詢彈性。但相對的以較長時間更動資料架構的做法,要支援現今軟體工程的方法論,有一定程度的適用性問題。同時過度強調正規化也會因必須以多表的JOIN才能查一份完整的資料,而可能會有性能上的問題。所以MySQL決定啟動一個代碼為MySQLng (MySQL Next Generation)項目,這個工程經過橫跨資料庫核心、前端工具和連結器(Connector)部門大量的開發工程師的努力,成就各位看到的MySQL在2016年推出的以MySQL當文件儲存(MySQL as a Document Store)新功能。

MySQL的架構因此而出現的變化


MySQL在5.7.12以後就是一個完全支援JSON文件的文件型資料庫,它從資料庫核心到前端工具和支援各種前端應用技術的連結器(Connector)都出現了許多的新功能, 如下圖所示,可分成四個模組:


1. 資料庫核心加了新的JSON資料型別和JSON函式

2. 新加入一個名為mysqlx的插件,執行時會聽33060端口,使資料庫能透過X Protocol和X Dav API執行應用程式下達的CRUD操作

3. 新的MySQL客戶端 – MySQL Shell

4. 擴增多種連結器 (包括Connector/J, Connector/Python, Connector/Nodejs, Connetor/C,C++) 的功能,包裹(wrap)X Dav API,透過X Protocol能以純NoSQL方式方式對資料庫的JSON文件做操作,使整份應用的開發完全沒有SQL的影子。





以下對這四個支援以MySQL當做文件存儲(MySQL as a Document Store)的模組模組特性詳細說明

資料庫核心加入了新的JSON資料型別和JSON函式

JSON資料型別


如下圖的1.所示,自MySQL 5.7.8起這項功能就含在MySQL中了。您可以用您熟悉的mysql客戶端程式登入資料庫,建一個有JSON欄位的表,再將JSON文件到插入這個欄位,具體的範例如下所示。JSON欄位是以binary為基礎再於欄位頭加上文件中各物件的位移(offset),如此能讓MySQL能快速的找到查詢所指定的物件。



### 注意:是JSON資料型別,不是text或varchar資料型別

mysql> CREATE TABLE t1 (data JSON);


Query OK, 0 rows affected (0.08 sec)



### 以字串形式將資料插入表中,物件名稱要在引號內
mysql> INSERT INTO t1(data) VALUES ('{ "series": 1}'), ('{ "series": 7}'), ('{ "series": 3}'), ('{ "series": 4}'), ('{ "series": 10}'), ('{ "series": 2}'), ('{ "series": 6}'), ('{ "series": 5}'), ('{ "series": 8}'), ('{ "series": 11}'); Query OK, 10 rows affected (0.02 sec)

Records: 10 Duplicates: 0 Warnings: 0;

###如果插入的不是合於JSON規範的資料會被MySQL拒絕並報錯

mysql> insert into t1(data) values ('abc');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 't1.data'.
mysql> insert into t1(data) values (123);
ERROR 3140 (22032): Invalid JSON text: "not a JSON text, may need CAST" at position 0 in value for column 't1.data

###以JSON函式指定SQL查詢的WHERE條件

mysql> SELECT * FROM t1 WHERE json_extract(data,"$.series") >= 7 AND json_extract(data,"$.series") > 10;

+----------------+
| data                |
+----------------+
| {"series": 7} |
| {"series": 10}|
| {"series": 8} |
+----------------+

3 rows in set (0.00 sec);

23個JSON函式(隨著更新的版本推出,可能會增加更多的JSON函式以完善處理JSON文件的功能),依這些函式使用的時機可分為檢視JSON文件資訊、更改由路徑參數指定的JSON文件之內容、建立JSON文件、取出由路徑參數指定部份的JSON文件內容、和輔助性函式等五類。

JSON路徑

 

由於JSON函式會以路徑(path)指定要到JSON文件中一定的位置,取出或參照部份文件的資訊,在介紹JSON函式前,我先說明路徑語法:

1.在SQL命令中先要找到存放JSON文件的JSON欄位,其語法為[[database].table.]field –找到整份JSON文件
2.找到欄位後再指定路徑 – 在JSON文件中的路徑的語法如下:

 - $ - 代表JSON文件的根

 - . – 指定名稱,到該名稱所對應的值或物件,例如$.user-找出JSON文件中user物件或物件的值

 - [array-position] –指定陣列中某一序位的內容(由0開始),例如$.user.address[1]-找到JSON文件的user物件之下的address陣列之第2個序位的地址物件

 - * - 指定某一物件之下的所有成員物件,例如$user.* 代表找出JSON文件中user物件下所有的成員物件,可能包括名字,生日,及所有的地址…等

 - ** - 可當成一個wild card,用法是[prefix]**suffix,prefix是選項,也就是不會以**當結尾, 例如$user**.phone指向user物件下的phone物件

 - [*] – 找出陣列中所有的序位的內容

JSON path之範例將在下一段JSON_EXTRAT函式中再為您做詳細的說明

五大類JSON函式

1.檢視JSON文件的內容或屬性的JSON函式

 i. JSON_VALID(val), 檢查傳入的資料是否是一個合規的JSON文件,目前是以較舊的RFC2627為標準。合規傳出1, 不合規傳出0,若傳入NULL值,則傳出的也是NULL值。這個函式能讓我們在應用中就先測試所拿到的資料是否是一個JSON文件,如果不是在應用中就能先處理。

 ii. JSON_TYPE(json_val[,path]),檢視指定的JSON文件或文件中的一部份(以路徑-path指向)的資料型別。這個函式的第二個參數-path在後多

 iii. JSON_KEYS(json_doc[,path]),傳回指定JSON文件的最上一層(或是指定路徑)的鍵值(identifier),以下範例更能說明其用途:

# 文件的根之下有兩個identifier –"a"和"b"

mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
+--------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
+--------------------------------------------+
| ["a", "b"]                                           |
+--------------------------------------------+

# 文件的"根"之下的成員"b"物件之下有一個identifier – "b"

mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------------+
| JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------------+
| ["c"]                                                               |
+----------------------------------------------------+

iv. JSON_LENGTH(json_doc,path),

傳回指定JSON文件最上一層(或是指定路徑)的成員數,例如:

mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
+---------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}]') |
+---------------------------------+
| 3                                           |
+---------------------------------+

mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
| 2                                                      |
+-----------------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
+------------------------------------------------+
| 1                                                               |
+------------------------------------------------+

v. JSON_DEPTH(json_doc),傳回指定JSON文件最大(深)的階層,例如:

mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
+------------------+------------------+--------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
+------------------+------------------+--------------------+
| 1                       |                       1 |                         1 |
+------------------+------------------+--------------------+

mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
+------------------------+------------------------+
| JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |
+------------------------+------------------------+
| 2                               |                               2 |
+------------------------+------------------------+

mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');
+-------------------------------+
| JSON_DEPTH('[10, {"a": 20}]') |
+-------------------------------+
|                                        3 |
+-------------------------------+

vi. JSON_CONTAINS_PATH(json_doc,one | all, path[,path…]),檢視指定的JSON文件中是否有一個或全部指定的路徑,例如:

l 取出JSON文件的內容

i. JSON_EXTRACT(jond_doc,path)例如:

# 定一個名為j的JSON文件的mysql區域變量
mysql> set @j='{"user":{"name":{"first":"abc","last":"ssss"},"salary":12234,"address":[{"street":"def","number":"ghi","phone":"5678890"},{"street":"jkl","number":"mno","phone":"565656"}]}}';

#整理JSON文件的內容以階層方式排列於下:

#{"user":
      {
          "name":{"first":"abc","last":"ssss"},
          "salary":12234,
          "address":
                [ {"street":"def","number":"ghi","phone":"5678890"},
                  {"street":"jkl","number":"mno","phone":"565656"}
                ]
    }
}

# 驗證變量j的內容是否是一個合規的 JSON文件

mysql> select json_valid(@j);

+----------------+
| json_valid(@j)|
+----------------+
| 1                    |
+----------------+

1 row in set (0.00 sec)

查出JSON文件”根”以下的內容

mysql> select json_extract(@j,'$');
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_extract(@j,'$')                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"user": {"name": {"last": "ssss", "first": "abc"}, "salary": 12234, "address": [{"phone": "5678890", "number": "ghi", "street": "def"}, {"phone": "565656", "number": "mno", "street": "jkl"}]}} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

# 找出位於JSON文件”根”-‘$’以下的成員”name”的內容
mysql> select json_extract(@j,'$.user.name');
+------------------------------------+
| json_extract(@j,'$.user.name') |
+------------------------------------+
| {"last": "ssss", "first": "abc"}  |
+------------------------------------+
1 row in set (0.00 sec)

# 找出位於JSON文件”根”以下的成員”address”陣列所有的cell

mysql> select json_extract(@j,'$.address[*]');
+-----------------------------------------------------------------------------------------------------------------+
| json_extract(@j,'$.address[*]')                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------+
| [{"phone": "5678890", "number": "ghi", "street": "def"}, {"phone": "565656", "number": "mno", "street": "jkl"}] |
+------------------------------------------------------------------------------------------------------------

# 找出位於JSON文件”根”以下的成員”address”陣列中位於第二位的cell

mysql> select json_extract(@j,'$.address[1]');
+-------------------------------------------------------+
| json_extract(@j,'$.address[1]') |
+-------------------------------------------------------+
| {"phone": "565656", "number": "mno", "street": "jkl"} |
+-------------------------------------------------------+
1 row in set (0.00 sec)

# 找出JSON文件位於”根”之下所有的成員,和路徑’$’不同的是,它不會列示文件的根-‘user’,而是user之下的所有成員。

mysql> select json_extract(@j,'$.*');

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_extract(@j,'$.*')                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"name": {"last": "ssss", "first": "abc"}, "salary": 12234, "address": [{"phone": "5678890", "number": "ghi", "street": "def"}, {"phone": "565656", "number": "mno", "street": "jkl"}]}] |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

# 找出”根”之下”user”成員中任何一個地方的成子員”last”的內容

mysql> select json_extract(@j,'$.user**.last');

+--------------------------------------+
| json_extract(@j,'$.user**.last') |
+--------------------------------------+
| ["ssss"]                                        |
+--------------------------------------+

1 row in set (0.00 sec)

# 找出”根”之下”user”成員的”name”成員的所有內容

mysql> select json_extract(@j,'$.user.name.*');
+----------------------------------------+
| json_extract(@j,'$.user.name.*') |
+----------------------------------------+
| ["ssss", "abc"]                              |
+----------------------------------------+

1 row in set (0.00 sec)

# 查出JSON文件”根”的成員”user”的”salary”的資料型別,注意可以函式是可以圈套的,此處先以JSON_EXTRACT找到”slary”物件,再傳給JSON_TYPE判斷其資料型別

mysql> select json_type(json_extract(@j,'$.user.salary'));
+---------------------------------------------+
| json_type(json_extract(@j,'$.user.salary')) |
+---------------------------------------------+
| INTEGER                                            |
+---------------------------------------------+

1 row in set (0.00 sec)

ii. JSON_SEARCH(json_doc,one or all, search_str [,escape_char[,path]…]),傳回指定的JSON文件,或由傳入的路徑指定的部份JSON文件之下,合搜索字串相符的物件所在的路徑,如果找不到和搜索字串相符的物件則傳回NULL值,範例如下:

# JSON文件由”根”往下找,第一次碰到值為”abc”的位置為在根之下的陣列中第一個位置(cell) - $[0]

mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
+------------------------------------------+
| JSON_SEARCH(@j, 'one', 'abc') |
+------------------------------------------+
| "$[0]"                                               |
+------------------------------------------+


# 找出所有值為”abc”的位置,結果找到兩個,除了根之下陣列的第一個位置(cell)之外,在第二個位置(cell)的成員”x”的內容也是”abc”

mysql> SELECT JSON_SEARCH(@j,'all', 'abc');
+--------------------------------------+
| JSON_SEARCH(@j, 'all', 'abc') |
+--------------------------------------+
| ["$[0]", "$[2].x"]                        |
+--------------------------------------+

mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
+--------------------------------------+
| JSON_SEARCH(@j, 'all', 'ghi') |
+--------------------------------------+
| NULL                                         |
+--------------------------------------+

iii. -> 相當於JSON_EXTRACT(),會傳出整份物件,例如有一個表mycollection的doc欄位內含JSON文件:

mysql> select doc from mycollection limit 4\G
*************************** 1. row ***************************
doc: {"_id": "13a90b0a4af7451c9affb89dae36d5a8", "age": 15, "name": "Sakila"}
*************************** 2. row ***************************
doc: {"_id": "9d0eec5aa65411e6904b0a0027000003", "policy": {"mainInsured": {"coverageList": [{"item": "ULOB"}]}}}
*************************** 3. row ***************************
doc: {"_id": "a34efb1c3344487f90c2d0802aa92098", "age": 39, "name": "Mike"}
*************************** 4. row ***************************
doc: {"_id": "c8a5d26905e843dfa2fc15083f6fbd0d", "age": 24, "name": "Susanne"}

# mysql> select json_extract(doc,'$.name') from mycollection limit 4\G

*************************** 1. row ***************************
json_extract(doc,'$.name'): "Sakila"
*************************** 2. row ***************************
json_extract(doc,'$.name'): NULL
*************************** 3. row ***************************
json_extract(doc,'$.name'): "Mike"
*************************** 4. row ***************************

json_extract(doc,'$.name'): "Susanne"

4 rows in set (0.00 sec)

# 以"->"也能行到同樣的解果:

mysql> select doc->'$.name' from mycollection limit 4\G

*************************** 1. row ***************************
doc->$.name': "Sakila"
*************************** 2. row ***************************
doc->$.name': NULL

*************************** 3. row ***************************
doc->'$.name': "Mike"
*************************** 4. row ***************************
doc->'$.name': "Susanne"

4 rows in set (0.00 sec)

iv. ->> 等同於JSON_UNQUOTE(JSON_EXTRACT()),脫掉引號再傳回,方便我們在WHERE條件中找出某些訊息做比較,例如:

mysql> select doc->'$.name' from mycollection where doc->'$.age' > 20 limit 2;

+--------------------+
| doc->'$.name' |
+--------------------+

|   "Sunny"            |
| "John"                 |
+--------------------+

2 rows in set (0.00 sec)

mysql> select doc->>'$.name' from mycollection where doc->'$.age' > 20 limit 2;
+--------------------------+
| doc->>'$.name' |
+--------------------------+
| Sunny                          |
| John                             |
+--------------------------+

2 rows in set (0.00 sec)

由於JSON_EXTRACT會傳回物件,如果是字串,會帶雙引號,必需以JSON_UNQUOTE()將雙引號移除才能用於比較運算。


1. 建立JSON物件

l JSON_MERGE(json_doc,json_doc[,json_doc]…), 合併多個JSON文件成為一個JSON文件,例如:

l JSON_ARRAY(val[,val]…),將傳入的值做成一個JSON陣列,例如:

l JSON_OBJECT(key,val[,key,val]…),將傳入的一個以上的key(或identifier)-值對做成一個JSON物件,例如:

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+----------------------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+----------------------------------------------------+
| {"id": 87, "name": "carrot"}                      |
+----------------------------------------------------+

2. 更改JSON文件

i. JSON_REMOVE(json_doc,path[,path]…),自文件中移除由路徑所指定的部份文件,可移除多個路徑的物件或以*及**指定多個物件,範例如下:

# 自文件文中移除”根”之下的陣列中第二位置的cell

mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@j, '$[1]');
+------------------------------------+
| JSON_REMOVE(@j, '$[1]') |
+------------------------------------+
| ["a", "d"]                                |
+------------------------------------+

ii. JSON_APPEND(json_doc,path,val[,path,val]…), 在指定的陣列之後再加上所輸入的值,傳回加上該值後的JSON文件

iii. JSON_SET(json_doc,path,val[,path,val]…),在指定的路徑沒有值時加上輸入的值,或指定的路徑有值時以輸入的值更之,並傳回加上或更新值後的JSON文件,如果如果文件中沒法定位該路徑,則傳回NULL值,

iv. JSON_INSERT(),在指定的路徑加上輸入的值, 並傳回加上或更新值後的JSON文件,如果指定的路徑己經有值則不做任何更改

v. JSON_REPLACE(),更新指定的路徑的值,並傳回加上或更新值後的JSON文件,如果找不到指定路徑,則不做任何更改

以上三個函式的範例下:

# 文件中位於’$.a’路徑的值原來為1,沒有’$.c’路徑;JSON_SET(@j, '$.a', 10, '$.c', '[true, false]')指定將路徑’$.a’的值改為10,在文件”根”之下加一個路徑為’$.c’,值為[true,false]的陣列

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------------+
| JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"}      |
+-----------------------------------------------------------+


# 文件中位於’$.a’路徑己有值,文件”根”之下沒有路徑’$.c’, JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]')不會改變文件路徑’$.a’的值,而會在文件的”根”之下插入一個陣列identifier “c”,其值為輸入的陣列

#文件中位於’$.a’路徑原來的值為1,文件”根”之下沒有路徑’$.c’, JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]')會以10替換在文件根之下indetifier ‘a’的值,不會在文件的”根”之下插入identifier “c”以及該函式所指定的陣列

mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
+-------------------------------------------------------------------+
| JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
+-------------------------------------------------------------------+
| {"a": 10, "b": [2, 3]}                                                    |
+-------------------------------------------------------------------+

l 輔助性的函式

i. JSON_QUOTE(json_val),如果參數是一個字串或數值,則在其前後加上雙引號 - “使它成為JSON物件,如果字串中後有引號,則傳回的結果中會在前後加上一跳脫碼\,再加上雙引號,例如:

mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
+--------------------+----------------------+
| JSON_QUOTE('null') | JSON_QUOTE('"null"') |
+--------------------+----------------------+
| "null" | "\"null\"" |
+--------------------+----------------------+
mysql> SELECT JSON_QUOTE('[1, 2, 3]');
+-------------------------+
| JSON_QUOTE('[1, 2, 3]') |
+-------------------------+
| "[1, 2, 3]" |
+-------------------------+

ii. JSON_UNQUOTE(val),脫掉只傳回的結果只取物件的內容值,例如:

mysql> SET @j = '"abc"';
mysql> SELECT @j, JSON_UNQUOTE(@j);
+-------+------------------+
| @j | JSON_UNQUOTE(@j) |
+-------+------------------+
| "abc" | abc |
+-------+------------------+

4. JOSN函式可以靈活的應用在處理於表中存JSON文件的各種場景,本文以建表時自JSON文件中找出指定的資料,和以指定以兩個表的JSON文件內的值做JOIN並更新表中的JSON文件為範例說明JSON函式的運用。

l 在JSON文件建虛擬欄位和索引以加強性能

在文件型資料庫中一堆JSON文件的集稱之為Collection(本文暫譯為“集”),在MySQL則以表對應collection,存放一堆文入。然而要如何在一堆JSON文件中快速找到符合查詢條件的那幾個文件(條件還要以JSON函式到文件中找出的值來比對)?這得靠 5.7版所提供的虛擬欄位,我們甚至可以用虛擬欄位所建的索引,使查詢能速定位到符合條的JSON文件上,再以JSON函式自文件中取出查詢所指定的資料。這也是X Protocol對Collection查詢,時資料庫在底層的InnoDB引擎中實現的過程。以下範例說明這個做法:

# 建一個表 – t1,內含一個JSON欄位,和一個虛擬欄位 – id,而 id的值是經由JSON_EXTRACT函式算出的,”STORED”運算元指定它的值實際存放在表中,是佔用空間的欄位,由於我們把主鍵加在這個虛擬欄位,它必需為STORED.

CREATE TABLE t1
(data JSON, id INT AS (JSON_EXTRACT(data,”$.id”)) STORED,
PRIMARY KEY(id));

#如果加上的是非主鍵,則可定為不佔空間的”VIRTUL”,在下例中為該”id”虚擬欄位加上索引時會自每一行的存效”data”欄位的JSON文件中找出各文件的”根”之下的”series”成員的值,將這個值加到索引的B+樹的節點上,也就是表不實際存id的值,但是索引”id_idx”的節點會存各文件的”series”成員的值

ALTER TABLE t1
ADD COLUMN series INT AS (JSON_EXTRACT(data, "$.series")),
ADD INDEX series_idx (series);

# 查詢的WHERE條件以函式”JSON_EXTRACT”找出各行”data”欄位的JSON文件中的”series”成員的值來比對是否在3和5之間

SELECT data, series FROM t1 WHERE
JSON_EXTRACT(data,"$.series") BETWEEN 3 AND 5;
series BETWEEN 3 AND 5;

# 傳回的結果如下:

+------------------------------------+----+
| data | series |
+------------------------------------+----+
| {"series": 3, "inverted": 8} | 3 |
| {"series": 4, "inverted": 7} | 4 |
| {"series": 5, "inverted": 6} | 5 |
+------------------------------------+----+

# 由於JSON_EXTRACT(data,"$.series")的值都存在於索引”series_idx”,所以MySQL的優化器能利用該索引快速找到符合條件的JSON文件是位於那些行(這點可由EXPLAIN該SQL的產出證明)。這種以函式索引支援查詢是5.7版才出現的新功能。

mysql> EXPLAIN SELECT data FROM t1 WHERE JSON_EXTRACT(data,"$.series") BETWEEN 3 AND 5

#傳回的結果如下:

+----+----------------+--------+---------------+--------+…+------------------------------+
| id | select_type | table | partitions | type | | Extra |
+----+----------------+--------+---------------+--------+…+------------------------------+
| 1 | SIMPLE | t1 | NULL | range | | Using index condition |
+----+----------------+--------+---------------+--------+…+------------------------------+

上列SQL命令相當於直接在查詢的WHERE子句將series欄位當成搜尋條件

select `test`.`t1`.`data` AS `data` from `test`.`t1` where (`test`.`t1`.`series` between 3 and 5)

l 以JSON函式更新文件和做JOIN的條件:

#下列 t1和t2表都有一個名為data的JSON欄位,我們想找出t1表中所有JSON文件”根”之下”series”成員的值和在t2表的JSON文件之”根”之下物件”b_series”陣列中第一個位置(cell)之值相等的行,並將該行的JSON文件的”根”之下名為”inverted”的物件的值改為11-‘t2表的JSON文件之”根”之下”b_series”陣列中第一個位置(cell)的值’,則更新資料的SQL命令如下:

UPDATE t1, t2
SET t1.data= JSON_INSERT(t1.data,"$.inverted", 11 – JSON_EXTRACT(t2.data,"$.b_series[0]"))
WHERE
JSON_EXTRACT(t1.data, "$.series") = JSON_EXTRACT(t2.data,"$.b_series[0]");

由以上的說明顯示,MySQL加上了JSON資料型別和23個JOSN函式,使我們能透過傳統的SQL介面完整的操作JSON文件,並享有透過索引加快查詢的性能。同時由於能將JSON文件存於InnoDB表中,原來關聯式資料庫所有的支援事務,多版本併行控制(MVCC),以及備份監控等功能均能完整保留,另一方面資料庫管理師也不需另外適應一套新的資料庫技術。讓MySQL的用戶可以同時保有關聯式資料庫的嚴謹性,又能擁抱NoSQL技術的彈性和JSON文件不需要做JOIN,就能查出滿足應用的需求的VIEW所帶來的性能提升。
然而,MySQL還不僅止於此,MySQL更進一步為我們提供一套全新的NoSQL介面 – X Protocol,使我們能以純NoSQL的技術開發新的應用。至於如何用X Protocol讓應用程式和使用者的介面—MySQL Shell直接存取資料庫中的JSON文件。我將在下一篇文章為各位分享。有興趣的朋友們敬請期待。

沒有留言:

張貼留言