MySQL5.7之Json Column和Generated Column
MySQL5.7新增兩種字段類型：Json和Generated，Generated型的產(chǎn)生和Json的關系密不可分，如果沒有Generated類型，Json類型在強大，生產(chǎn)中可能也無法使用，因為Json不支持索引，但是如果要查詢Json里的數(shù)據(jù)，沒有索引就是全表掃描，在執(zhí)行效率上肯定是不能用于生產(chǎn)環(huán)境的，但是有了Generated類型就不同了，Generated類型簡單地說是一個虛擬字段，值是不可更新的，值來源其他字段或者字段間計算或是轉化而來的，這種類型是可以創(chuàng)建索引，利用Generated的特性，就可以間接的給Json類型中的key創(chuàng)建索引，解決Json不能創(chuàng)建索引的問題。簡而言之， Generated類型的產(chǎn)生，為Json類型在索引方面的問題提供了支持。JSON的值包含單個值、數(shù)組、元組、標注的Json格式等幾種格式。
客觀地說，Json類型的出現(xiàn)，為研發(fā)提供了便利，提供了類似NoSQL的支持，但是走DBA運維的角度來看，也許是一個麻煩，這個麻煩和text等大字段的麻煩是一致的，json可能在實際的生產(chǎn)中會成為text字段的變種，望DBA在以后5.7的使用中密切關注這個類型。
一、創(chuàng)建帶有JSON字段的表
1、直接創(chuàng)建一張測試帶JSON字段的表
- CREATE TABLE json_test (
- id int(11) NOT NULL AUTO_INCREMENT,
- group_name varchar(20) DEFAULT NULL,
- user_info json DEFAULT NULL,
- PRIMARY KEY (id)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、在表中添加一個JSON類型的字段
ALTER TABLE json_test ADD COLUMN user_info JSON DEFAULT NULL;
3、插入數(shù)據(jù)
二、JSON字段相關查詢
1、查詢全部結果
2：JSON_EXTRACT查詢
JSON_EXTRACT這個函數(shù)主要用作精確匹配，如查找user_info中age=21的記錄或者查詢name=’wangwei’的記錄，或者name like ‘%wangwei%’模糊查詢：
- SELECT * FROM json_test WHERE JSON_EXTRACT(user_info,'$.age')=21;
在值為數(shù)組的時候，數(shù)組的起始位置值為0，同時，MySQL在5.5.9的版本增加了->，提供了等價于JSON_EXTRACT另外一種寫：
- SELECT * FROM json_test WHERE user_info->"$.age"=21;
3、JSON_SEARCH查詢
JSON_SEARCH字符串查找函數(shù)，查找json所有key中值為字符串’wangwei’的記錄，為精確匹配;加上%的話有點like的意思：
- SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','wangwei') IS NOT NULL;
- SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','wangwei%') IS NOT NULL;
- SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','%wangwei%') IS NOT NULL;
4、JSON_CONTAINS判斷key是否包含指定的值
JSON_CONTAINS判斷key是否包含指定的值，返回值為0或1或者是null，對應的情況為不包含、包含、和指定的key不存在集中情況：
5、JSON_CONTAINS_PATH判斷key在字段中是否存在
JSON_CONTAINS_PATH判斷key在字段中是否存在，返回值為0和1，格式為JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)，如果為one的時候，代表path中只要有一個在就為1，反之為0;如果為all的話必須全部包含，返回為1，反之為0：
6、JSON_LENGTH長度查詢
JSON_LENGTH是用來計算JSON字段的長度：
7：JSON_DEPTH層級查詢
JSON_DEPTH這個主要是用來查詢Json字段的層級，空的為1，所以正常情況下Json字段類型不為空，這個值的查詢出的結果應該大于等于2:
8：JSON_TYPE查詢Json字段中key值的數(shù)據(jù)類型
JSON_TYPE主要用于查詢Json字段類型中key值的數(shù)據(jù)類型，顯示的類型按照分類如下：
Purely JSON types:
OBJECT: JSON objects;ARRAY: JSON arrays;BOOLEAN: The JSON true and false literals;NULL: The JSON null literal
Numeric types:
INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT and INT and BIGINT scalars;DOUBLE: MySQL DOUBLE FLOAT scalars;DECIMAL: MySQL DECIMAL and NUMERIC scalars
Temporal types:
DATETIME: MySQL DATETIME and TIMESTAMP scalars;DATE: MySQL DATE scalars;TIME: MySQL TIME scalars
String types:
STRING: MySQL utf8 character type scalars;CHAR, VARCHAR, TEXT, ENUM, and SET
Binary types:
BLOB: MySQL binary type scalars;BINARY, VARBINARY, BLOB;BIT: MySQL BIT scalars
All other types:
OPAQUE (raw bits)
9、JSON_UNQUOTE去掉前后特殊字符
JSON_UNQUOTE主要是用來去掉前后的特殊字符，格式化值，目前支持一下幾種字符：
三、JSON字段相關寫操作
1、JSON_ARRAY_APPEND數(shù)組類的追加
這個用法主要是處理JSON字段中值數(shù)組型的數(shù)據(jù)，主要是追加值，不是標準數(shù)組類型的，這個函數(shù)會自動轉化為數(shù)組形式，把之前的值作為數(shù)組的***個元素然后進行追加，有批量追加方法：
- mysql> SET @j = '["a", ["b", "c"], "d"]';
在字段中第二個數(shù)值的數(shù)組中追加一個元素1：
- mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
- +----------------------------------+
- | JSON_ARRAY_APPEND(@j, '$[1]', 1) |
- +----------------------------------+
- | ["a", ["b", "c", 1], "d"] |
- +----------------------------------+
在字段中***個數(shù)值的數(shù)組中追加一個元素2,如果不是數(shù)組形式的單個值會改變其為數(shù)組并追加：
- mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
- +----------------------------------+
- | JSON_ARRAY_APPEND(@j, '$[0]', 2) |
- +----------------------------------+
- | [["a", 2], ["b", "c"], "d"] |
- +----------------------------------+
在字段中第二個數(shù)值的***個數(shù)組中追加一個元素3，如果不是數(shù)組形式的單個值會改變其為數(shù)組并追加：
- mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
- +-------------------------------------+
- | JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
- +-------------------------------------+
- | ["a", [["b", 3], "c"], "d"] |
- +-------------------------------------+
在字段中key為”b”的value數(shù)組中追加一個元素”x”：
- mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
- mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
- +------------------------------------+
- | JSON_ARRAY_APPEND(@j, '$.b', 'x') |
- +------------------------------------+
- | {"a": 1, "b": [2, 3, "x"], "c": 4} |
- +------------------------------------+
在字段中key為”c”的value數(shù)組中追加一個元素”y”：
- mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
- +--------------------------------------+
- | JSON_ARRAY_APPEND(@j, '$.c', 'y') |
- +--------------------------------------+
- | {"a": 1, "b": [2, 3], "c": [4, "y"]} |
- +--------------------------------------+
如果JSON字段為標準的JSON類型，使用這個函數(shù)則會把其變化數(shù)組形式，并追加值：
- mysql> SET @j = '{"a": 1}';
- mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
- +---------------------------------+
- | JSON_ARRAY_APPEND(@j, '$', 'z') |
- +---------------------------------+
- | [{"a": 1}, "z"] |
- +---------------------------------+
如果JSON字段為標準的JSON類型，批量追加：
- mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z'，'$', 'w');
- +---------------------------------+
- | JSON_ARRAY_APPEND(@j, '$', 'z') |
- +---------------------------------+
- | [{"a": 1}, "z", "w"] |
- +---------------------------------+
2、JSON_ARRAY_INSERT數(shù)組類的插入
這個用法主要是處理JSON字段中值數(shù)組型的數(shù)據(jù)，主要是插入，不是標準數(shù)組類型的，這個函數(shù)會自動轉化為數(shù)組形式，把之前的值作為數(shù)組的***個元素然后進行追加：
- mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
在數(shù)組第1個值后面插入字符’x’ 元素:
- mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
- +------------------------------------+
- | JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
- +------------------------------------+
- | ["a", "x", {"b": [1, 2]}, [3, 4]] |
在數(shù)組第1個值前面插入字符’x’ 元素:
- mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x');
- +------------------------------------+
- | JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
- +------------------------------------+
- | ["x","a", {"b": [1, 2]}, [3, 4]] |
- +------------------------------------+
在數(shù)組第100個值后面插入字符’x’ 元素，如果超長，則在***一個值后面插入：
- mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
- +--------------------------------------+
- | JSON_ARRAY_INSERT(@j, '$[100]', 'x') |
- +--------------------------------------+
- | ["a", {"b": [1, 2]}, [3, 4], "x"] |
- +--------------------------------------+
在數(shù)組第1個值中key為’b’的value數(shù)組第1個值前面入字符元素’x’：
- mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
- +-----------------------------------------+
- | JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') |
- +-----------------------------------------+
- | ["a", {"b": ["x", 1, 2]}, [3, 4]] |
- +-----------------------------------------+
在數(shù)組第2個值后面數(shù)組第1個值后面入字符元素’y’：
- mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
- +---------------------------------------+
- | JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |
- +---------------------------------------+
- | ["a", {"b": [1, 2]}, [3, "y", 4]] |
- +---------------------------------------+
數(shù)組元素的批量插入：
- mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');
- +----------------------------------------------------+
- | JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') |
- +----------------------------------------------------+
- | ["x", "a", {"b": [1, 2]}, [3, 4]] |
- +----------------------------------------------------+
3、JSON_INSERT標準JSON值的插入
如果這個key已經(jīng)存在，則不會去覆蓋舊值，不存在的插入：
- mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
- mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
- +----------------------------------------------------+
- | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
- +----------------------------------------------------+
- | {"a": 1, "b": [2, 3], "c": "[true, false]"} |
- +----------------------------------------------------+
4、JSON_MERGE合并操作
JSON_MERGE這個主要用于合并：如果兩個是數(shù)組的，則合并為一個數(shù)組;如果兩個是JSON的，則合并為一個JSON，如果兩個JSON有相同的key，則value合并為數(shù)組;如果是兩個單值的，則合并為一個數(shù)組;如果是一個數(shù)組，一個是JSON，則合并為一個數(shù)組：
- mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
- +---------------------------------------+
- | JSON_MERGE('[1, 2]', '[true, false]') |
- +---------------------------------------+
- | [1, 2, true, false] |
- +---------------------------------------+
- mysql> SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}');
- +-------------------------------------------+
- | JSON_MERGE('{"name": "x"}', '{"id": 47}') |
- +-------------------------------------------+
- | {"id": 47, "name": "x"} |
- +-------------------------------------------+
- mysql> SELECT JSON_MERGE('{"id": "x"}', '{"id": 47}');
- +-------------------------------------------+
- | JSON_MERGE('{"id": "x"}', '{"id": 47}') |
- +-------------------------------------------+
- | {"id": ["x",47]} |
- +-------------------------------------------+
- mysql> SELECT JSON_MERGE('1', 'true');
- +-------------------------+
- | JSON_MERGE('1', 'true') |
- +-------------------------+
- | [1, true] |
- +-------------------------+
- mysql> SELECT JSON_MERGE('[1, 2]', '{"id": 47}');
- +------------------------------------+
- | JSON_MERGE('[1, 2]', '{"id": 47}') |
- +------------------------------------+
- | [1, 2, {"id": 47}] |
- +------------------------------------+
5、JSON_REMOVE刪除操作
JSON_REMOVE刪除操作，這個比較簡單，刪除字段中某個值，數(shù)組的或是JSON格式的都可以：
- mysql> SET @j = '["a", ["b", "c"], "d"]';
- mysql> SELECT JSON_REMOVE(@j, '$[1]');
- +-------------------------+
- | JSON_REMOVE(@j, '$[1]') |
- +-------------------------+
- | ["a", "d"] |
- +-------------------------+
- mysql> SET @j = '{"name": "x","id": 47}';
- mysql> SELECT JSON_REMOVE(@j, '$.id');
- +-------------------------+
- | JSON_REMOVE(@j, '$.id') |
- +-------------------------+
- | {"name": "x"} |
- +-------------------------+
- mysql> SELECT JSON_REMOVE(@j, '$.id', '$.name');
- +-----------------------------------+
- | JSON_REMOVE(@j, '$.id', '$.name') |
- +-----------------------------------+
- | {} |
- +-----------------------------------+
- mysql> SET @j = '{"name": "x","id": [47,48]}';
- mysql> SELECT JSON_REMOVE(@j, '$.id[1]');
- +-------------------------+
- | JSON_REMOVE(@j, '$.id') |
- +-------------------------+
- | {"id": [47],"name": "x"} |
- +-------------------------+
6、JSON_REPLACE操作
JSON_REPLACE替換操作，有則替換，無則忽略。
- mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
- mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
- +-----------------------------------------------------+
- | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
- +-----------------------------------------------------+
- | {"a": 10, "b": [2, 3]} |
- +-----------------------------------------------------+
- mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.b[1]',99);
- +-----------------------------------------------------+
- | JSON_REPLACE(@j, '$.a', 10, '$.b[1]',99') |
- +-----------------------------------------------------+
- | {"a": 10, "b": [2, 99]} |
- +-----------------------------------------------------+
7、JSON_SET操作
JSON_SET操作為更新和插入操作，已經(jīng)存在的去更新;不存在則插入，以下是JSON_SET、JSON_INSERT、JSON_REPLACE執(zhí)行結果的區(qū)別：
四、JSON字段索引以及Generated字段
JSON字段類型在當前的版本中自身沒有索引，那么在生產(chǎn)中是非?？膳碌?，JSON字段的增、刪、改、查效率可想而知，基本沒法用，也許是基于此，MySQL5.7中提供了Generated字段類型，網(wǎng)上有叫生成列或是計算列的。這里先來了解一下什么是Generated Column。
1、Generated Column介紹
Generated Column是MySQL 5.7.6引入的新特性，所謂Cenerated Column，就是數(shù)據(jù)庫中這一列由其他列計算而得。引用官方參考手冊中的例子予以說明：
- CREATE TABLE triangle (
- sidea DOUBLE,
- sideb DOUBLE,
- sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
- );
- INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
- mysql> SELECT * FROM triangle;
- +-------+-------+--------------------+
- | sidea | sideb | sidec |
- +-------+-------+--------------------+
- | 1 | 1 | 1.4142135623730951 |
- | 3 | 4 | 5 |
- | 6 | 8 | 10 |
- +-------+-------+--------------------+
Generated Column有兩種，即Virtual Generated Column和Stored Generated Column，前者只將Generated Column保存在數(shù)據(jù)字典中(表的元數(shù)據(jù))，并不會將這一列數(shù)據(jù)持久化到磁盤上;后者會將Generated Column持久化到磁盤上，而不是每次讀取的時候計算所得。很明顯，后者存放了可以通過已有數(shù)據(jù)計算而得到的數(shù)據(jù)，需要更多的磁盤空間，與Virtual Column相比并沒有優(yōu)勢，因此，MySQL 5.7中，不指定Generated Column的類型，默認是Virtual Column。雖然一般情況下都應該使用Virtal Generated Column，但是，目前使用Virtual Generated Column還有很多限制：不能用作主鍵、不能作為主鍵、不能創(chuàng)建全文索引和空間索引等，但是在后續(xù)的版本中可能支持，所以如果使用Generated Column字段做索引的話，還是使用Stored Generated Column吧，在使用Generated Column做索引上，JSON字段索引的解決方案，官方也是推薦使用Stored Generated Column。使用Stored Generated Column建表語句如下，只是在加了個單詞：
- CREATE TABLE triangle (
- sidea DOUBLE,
- sideb DOUBLE,
- sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb) STORED)
- );
2、Generated Column注意事項
Generated Column是不能進行寫操作的，它是自動生成的;在創(chuàng)建的時候要考慮這個列的計算公式是否合理，不合理的話創(chuàng)建的時候不會報錯，使用時插入值就會報錯;Generated Column依賴的列在刪除的時候會提示報錯，必須先刪除Generated Column才能再去刪除它依賴的列;Generated Column定義不合法，如我們將generated column定義為 "x列+y列"，很明顯，x列或y列都是數(shù)值型，如果我們將x列或y列定義(或修改)為字符型，則預期會報錯，但實際上我們可以正常創(chuàng)建，但是在插入的時候是會報錯的。
3、利用Generated Column給JSON字段添加索引
正常情況下，JSON字段的相關查詢是掃描全表的，因為JSON字段本身不能創(chuàng)建索引的，我們利用Generated Column特性，對JSON字段中相關key作為Generated Column來做生成列，然后對Generated Column做索引：
- ALTER TABLE json_test ADD COLUMN age INT AS (JSON_EXTRACT(user_info,'$.age')) STORED, ADD KEY idx_age (age);
前后對比圖如下：
可以很明顯的看出，使用Generated Column并添加索引后，查詢JSON字段中的值使用索引。
五、結束語
MySQL5.7中Generated Column和JSON Column的出現(xiàn)，使一些場景替代MongoDB等NoSQL提供了可能，雖然整體上還沒有MongoDB等做的那么強大，但相信以后使用這兩種類型的場景會越來越多， 同時對DBA的挑戰(zhàn)也越來越大，希望密集使用JSON類型業(yè)務使用獨立的MySQL實例來運行，以免JSON成為大字段(存儲在JSON文檔的大小JSON 列被限制為值 max_allowed_packet的系統(tǒng)變量)時對其他業(yè)務帶來影響。
【本文是51CTO專欄作者王偉的原創(chuàng)文章，轉載請聯(lián)系作者本人獲取授權】
