- 詳解新世代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文件的根
- .
- [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文件。我將在下一篇文章為各位分享。有興趣的朋友們敬請期待。
沒有留言:
張貼留言