MySQL 5.7 JSON 數據型別使用總結

從MySQL5.7.8開始，MySQL支持原生的JSON數據型別。MySQL 支持RFC 7159定義的全部json 數據型別，具體的包含四種基本型別（ strings , numbers , booleans , null ）和兩種結構化型別（ objects and arrays ）。

將 JSON 格式的字串儲存在字串列中相比，該數據型別具有以下優勢：

JSON型別的儲存結構

MySQL為了提供對 json 物件的支持，提供了一套將 json 字串轉為結構化二進制物件的儲存方式。json會被轉為二進制的doc物件儲存於磁盤中（在處理JSON時MySQL使用的 utf8mb4 字元集， utf8mb4 是 utf8 和 ascii 的超集）。

doc物件包含兩個部份，type和value部份。其中type占1字節，可以表示16種型別：大的和小的json object型別、大的和小的 json array型別、literal型別（true、false、null三個值）、number型別（int6、uint16、int32、uint32、int64、uint64、double型別、utf8mb4 string型別和custom data（mysql自訂型別）

JSON數據型別意義

其實，沒有JSON數據型別的支持，我們一樣可以透過 varchar 型別或者 text 等型別來保存這一格式的數據，但是，為什麽還要專門增加這一數據格式的支持呢？其中肯定有較varchar或者text來儲存此型別更優越的地方。

JSON 數據型別

JSON 物件

JSON 陣列

建立 JSON

類似varchar，設定主要將欄位的type是json，不能設定長度，可以是NULL但不能有預設值。

CREATETABLE`tinywan_json` (
idINT ( 11 ) NOTNULL auto_increment,
 tag jsonNOTNULLCOMMENT'標簽列表',
 catagory jsonNOTNULLCOMMENT'分類列表',
 create_time INT ( 11 ) DEFAULTNULLCOMMENT'建立時間',
PRIMARY KEY ( id ) 
) ENGINE = INNODBDEFAULTcharset = utf8mb4;


使用 describe tinywan_json 檢視建立的表結構：

mysql> describe tinywan_json;
+-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| tag | json | NO | | NULL | |
| catagory | json | NO | | NULL | |
| create_time | int(11) | YES | | NULL | |
+-------------+---------+------+-----+---------+----------------+
4 rows in set (0.05 sec)


寫入JSON數據

INSERTINTO tinywan_json ( catagory, tag )
VALUES
 ( '{"0":"廚衛","1":"童裝","2":"休閑"}', '["Good","Fine","Bad"]' );


MYSQL也有專門的函式 JSON_OBJECT , JSON_ARRAY 函式生成json格式的數據。

INSERTINTO tinywan_json ( catagory, tag )
VALUES
 ( JSON_OBJECT ( "name", "John", "age", 23 ), JSON_ARRAY ( 'Low', 'Middle', 'High' ) )


檢視插入的兩條數據

mysql> select * from tinywan_json;
+----+---------------------------+-----------------------------------------+-------------+
| id | tag | catagory | create_time |
+----+---------------------------+-----------------------------------------+-------------+
| 1 | ["Good", "Fine", "Bad"] | {"0": "廚衛", "1": "童裝", "2": "休閑"} | NULL |
| 2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL |
+----+---------------------------+-----------------------------------------+-------------+
2 rows inset (0.10 sec)


查詢 JSON

查詢json中的數據使用 column->path 的形式，其中物件型別 path 這樣表示 $.path ，而陣列型別則是 $[index] 。

檢視每一行數據的JSON型別，篩選如下：

mysql> select tag,json_type(tag),catagory,json_type(catagory) from tinywan_json;
+---------------------------+----------------+-----------------------------------------+---------------------+
| tag | json_type(tag) | catagory | json_type(catagory) |
+---------------------------+----------------+-----------------------------------------+---------------------+
| ["Good", "Fine", "Bad"] | ARRAY | {"0": "廚衛", "1": "童裝", "2": "休閑"} | OBJECT |
| ["Low", "Middle", "High"] | ARRAY | {"age": 23, "name": "John"} | OBJECT |
+---------------------------+----------------+-----------------------------------------+---------------------+
2 rows inset (0.07 sec)


篩選出 catagory 中 name = John 的那條記錄的所有 tag:

mysql> select tag from tinywan_json where catagory->'$.name' = 'John';
+---------------------------+
| tag |
+---------------------------+
| ["Low", "Middle", "High"] |
+---------------------------+
1 row inset (0.06 sec)


透過 json_type 確定了 tag 的數據型別是 ARRAY ，那麽就可以使用陣列索引的方式查詢：

mysql> select tag->'$[0]',tag->'$[2024]',tag->'$.notexist'from tinywan_json where catagory->'$.name' = 'John';
+-------------+----------------+-------------------+
| tag->'$[0]' | tag->'$[2024]' | tag->'$.notexist' |
+-------------+----------------+-------------------+
| "Low" | NULL | NULL |
+-------------+----------------+-------------------+
1 row inset (0.05 sec)


上面的例子中，可以看到，使用 tag->'$[0]' 的方式如期獲取了 tag 陣列內的第一個數據。另外兩個，一個 tag->'$[2024]' 是不存在的陣列元素， 一個tag->'$.notexist' 是不存在的物件鍵，都返回了 null ，這與預期的一致。

還要一個小小的問題，返回的 tag->'$[0]' = "Low" ，並且左右有一對雙引號，可否去掉呢？使用 JSON_UNQUOTE 函式

mysql> select JSON_UNQUOTE(tag->'$[0]'),tag->'$[2024]',tag->'$.notexist' from tinywan_json where catagory->'$.name' = 'John';
+---------------------------+----------------+-------------------+
| JSON_UNQUOTE(tag->'$[0]') | tag->'$[2024]' | tag->'$.notexist' |
+---------------------------+----------------+-------------------+
| Low | NULL | NULL |
+---------------------------+----------------+-------------------+
1 row inset (0.06 sec)


JSON 條件查詢

因為JSON不同於字串，所以如果用字串和JSON欄位進行比較，是不會相等的。這時可以使用 CAST 函式，將字串轉成 JSON 的形式。

mysql> select * from tinywan_json where catagory = '{"age": 23, "name": "John"}';
Empty set
mysql> select * from tinywan_json where catagory = CAST('{"age": 23, "name": "John"}'asJSON);
+----+---------------------------+-----------------------------+-------------+
| id | tag | catagory | create_time |
+----+---------------------------+-----------------------------+-------------+
| 2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL |
+----+---------------------------+-----------------------------+-------------+
1 row inset (0.07 sec)

對應地， select 欄位和 where 篩選條件中，均可以使用 column->path 的方式操作。

mysql> select tag,catagory->'$.age'from tinywan_json where tag->'$[1]' = 'Middle';
+---------------------------+-------------------+
| tag | catagory->'$.age' |
+---------------------------+-------------------+
| ["Low", "Middle", "High"] | 23 |
+---------------------------+-------------------+
1 row inset (0.07 sec)


特別註意的是 。JSON中的元素是嚴格區分變量型別的，比如說整型和字串是嚴格區分的。

mysql> select * from tinywan_json where catagory->'$.age' = 23;
+----+---------------------------+-----------------------------+-------------+
| id | tag | catagory | create_time |
+----+---------------------------+-----------------------------+-------------+
| 2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL |
+----+---------------------------+-----------------------------+-------------+
1 row inset (0.06 sec)
mysql> select * from tinywan_json where catagory->'$.age' = '23';
Empty set

除了用 column->path 的形式搜尋，還可以用 JSON_CONTAINS 函式，但和 column->path 的形式有點相反的是， JSON_CONTAINS 第二個參數是不接受整數的，無論 json 元素是整型還是字串，否則會出現錯誤 nvalid data type for JSON data in argument 2 to function json_contains; a JSON string or JSON type is required.

mysql> select * from tinywan_json where JSON_CONTAINS(catagory,'23','$.age');
+----+---------------------------+-----------------------------+-------------+
| id | tag | catagory | create_time |
+----+---------------------------+-----------------------------+-------------+
| 2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL |
+----+---------------------------+-----------------------------+-------------+
1 row inset (0.08 sec)
mysql> select * from tinywan_json where JSON_CONTAINS(catagory,23,'$.age');
Invalid data typeforJSON data in argument 2 to functionjson_contains; a JSONstring or JSONtype is required.

上面打印的第一行，才是正確的寫法。整數應該寫成 'int' ，字串則要看值內的情況，很多情況下需要帶上雙引號， '"string"' ，這樣寫。如下：

mysql> select * from tinywan_json where JSON_CONTAINS(tag,'"Fine"');
+----+-------------------------+-----------------------------------------+-------------+
| id | tag | catagory | create_time |
+----+-------------------------+-----------------------------------------+-------------+
| 1 | ["Good", "Fine", "Bad"] | {"0": "廚衛", "1": "童裝", "2": "休閑"} | NULL |
+----+-------------------------+-----------------------------------------+-------------+
1 row inset (0.07 sec)


小結