Mysql支持JSON類型 - 純淨天空

Mysql從5.7.8版本之後，就開始原生支持JSON(JavaScript Object Notation)類型了。這跟以往直接把JSON字符串存到Mysql的文本類型不一樣，因為使用MySQL原生的JSON類型，數據校驗之後是以二進製形式存儲，避免了每次讀數據時都要做字符串解析的額外負擔，大大提升了讀取性能。這裏介紹MySQL中JSON類型的常用用法。

1. 創建帶JSON類型的表格

下麵的語句創建一個擁有主鍵和json類型字段的mysql表格：

CREATE TABLE table_name (
    id INT NOT NULL AUTO_INCREMENT, 
    json_col JSON,
    PRIMARY KEY(id)
);

2. 插入JSON格式的數據到表格中

簡單的KEY/VALUE詞典形式的JSON數據摻入到M有SQL

INSERT INTO
    table_name (json_col) 
VALUES
    ('{"City": "Galle", "Description": "Best damn city in the world"}');

注意：考慮到JSON字符串中使用雙引號來標記字符串，所以整個JSON串使用單引號標示。存入的數據在校驗通過之後，將以二進製(binary format）的形式存儲。

3. 插入嵌套類型的JSON數據到MySQL表格

更複雜的JSON數據，其中一個Value是數組

INSERT INTO myjson(dict) 
VALUES('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}');

4. 查詢JSON類型內部的值

通常可以使用”->”來取JSON內容

mysql> SELECT sentence FROM facts;
+---------------------------------------------------------+
| sentence                                                |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+
To look up this particular sentence employing mascot as the key, you can use the column-path operator ->, as shown here:

mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+

也可以使用JSON_EXTRACT函數提取JSON類型內部的值, MySQL 5.7.9版本之後支持了JSON_EXTRACT(table_column, path)

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

如果有下麵這個JSON結構

[3, {"a": [5, 6], "b": 10}, [99, 100] ]

那麽相關的值為：

$[0] evaluates to 3.
$[1] evaluates to {"a": [5, 6], "b": 10}.
$[2] evaluates to [99, 100].
$[3] evaluates to NULL (it refers to the fourth array element, which does not exist).

5. 更新JSON類型的字段

前麵介紹的是如何插入JSON數據到MySQL表格，這裏介紹如何更新JSON類型的數據。下麵的代碼是新增數據到上麵的實例數據中。

UPDATE 
    myjson 
SET 
    dict=JSON_ARRAY_APPEND(dict,'$.variations','scheveningen') 
WHERE 
    id = 2;

注意: 1) 美元符號”$”表示當前的JSON數據，$.variations取該JSON數據中的對應字段的值(一個數組)
2)更新數據之後，使用select * from myjson查詢出的結果如下：

+----+-----------------------------------------------------------------------------------------+
| id | dict                                                                                    |
+---+-----------------------------------------------------------------------------------------+
| 2  | {"opening": "Sicilian", "variations": ["pelikan", "dragon", "najdorf", "scheveningen"]} |
+----+-----------------------------------------------------------------------------------------+

6. 將數據CAST為MySQL JSON類型

SELECT CAST('[1,2,3]' as JSON) ;
SELECT CAST('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' as JSON);

7. 創建JSON對象和JSON數組

JSON_OBJECT可以創建JSON對象:

SELECT JSON_OBJECT('key1',col1 , 'key2',col2 , 'key3','col3') as myobj;

JSON_ARRAY創建JSON數組:

SELECT JSON_ARRAY(col1,col2,'col3') as myarray;

注意: myobj.key3 和 myarray[2] 是 “col3” 這個固定字符串。

更複雜的JSON數據:

SELECT JSON_OBJECT("opening","Sicilian", "variations",JSON_ARRAY("pelikan","dragon","najdorf") ) as mymixed ;

json

參考

MYSQL JSON官方文檔：https://dev.mysql.com/doc/refman/5.7/en/json.html