MySQL + JSON = 王炸?。?/h1>
來(lái)源：blog.csdn.net/java_pfx/article/details/116594654
關(guān)系型的結(jié)構(gòu)化存儲(chǔ)存在一定的弊端，因?yàn)樗枰A(yù)先定義好所有的列以及列對(duì)應(yīng)的類(lèi)型。但是業(yè)務(wù)在發(fā)展過(guò)程中，或許需要擴(kuò)展單個(gè)列的描述功能，這時(shí)，如果能用好 JSON 數(shù)據(jù)類(lèi)型，那就能打通關(guān)系型和非關(guān)系型數(shù)據(jù)的存儲(chǔ)之間的界限，為業(yè)務(wù)提供更好的架構(gòu)選擇。
當(dāng)然，很多同學(xué)在用 JSON 數(shù)據(jù)類(lèi)型時(shí)會(huì)遇到各種各樣的問(wèn)題，其中最容易犯的誤區(qū)就是將類(lèi)型 JSON 簡(jiǎn)單理解成字符串類(lèi)型。但當(dāng)你看完這篇文章后，會(huì)真正認(rèn)識(shí)到 JSON 數(shù)據(jù)類(lèi)型的威力，從而在實(shí)際工作中更好地存儲(chǔ)非結(jié)構(gòu)化的數(shù)據(jù)。
# JSON 數(shù)據(jù)類(lèi)型
JSON（JavaScript Object Notation）主要用于互聯(lián)網(wǎng)應(yīng)用服務(wù)之間的數(shù)據(jù)交換。MySQL 支持RFC 7159定義的 JSON 規(guī)范，主要有 JSON 對(duì)象 和 JSON 數(shù)組 兩種類(lèi)型。下面就是 JSON 對(duì)象，主要用來(lái)存儲(chǔ)圖片的相關(guān)信息：
{ "Image": { "Width": 800, "Height": 600, "Title": "View from 15th Floor", "Thumbnail": { "Url": "http://www.example.com/image/xx9943", "Height": 125, "Width": 100 }, "IDs": [116, 943, 234, 38793] }}
從中你可以看到， JSON 類(lèi)型可以很好地描述數(shù)據(jù)的相關(guān)內(nèi)容，比如這張圖片的寬度、高度、標(biāo)題等（這里使用到的類(lèi)型有整型、字符串類(lèi)型）。
JSON對(duì)象除了支持字符串、整型、日期類(lèi)型，JSON 內(nèi)嵌的字段也支持?jǐn)?shù)組類(lèi)型，如上代碼中的 IDs 字段。
另一種 JSON 數(shù)據(jù)類(lèi)型是數(shù)組類(lèi)型，如：
[ { "precision": "zip", "Latitude": 37.7668, "Longitude": -122.3959, "Address": "", "City": "SAN FRANCISCO", "State": "CA", "Zip": "94107", "Country": "US" }, { "precision": "zip", "Latitude": 37.371991, "Longitude": -122.026020, "Address": "", "City": "SUNNYVALE", "State": "CA", "Zip": "94085", "Country": "US" } ]
上面的示例演示的是一個(gè) JSON 數(shù)組，其中有 2 個(gè) JSON 對(duì)象。
到目前為止，可能很多同學(xué)會(huì)把 JSON 當(dāng)作一個(gè)很大的字段串類(lèi)型，從表面上來(lái)看，沒(méi)有錯(cuò)。但本質(zhì)上，JSON 是一種新的類(lèi)型，有自己的存儲(chǔ)格式，還能在每個(gè)對(duì)應(yīng)的字段上創(chuàng)建索引，做特定的優(yōu)化，這是傳統(tǒng)字段串無(wú)法實(shí)現(xiàn)的。JSON 類(lèi)型的另一個(gè)好處是無(wú)須預(yù)定義字段，字段可以無(wú)限擴(kuò)展。而傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)的列都需預(yù)先定義，想要擴(kuò)展需要執(zhí)行 ALTER TABLE … ADD COLUMN … 這樣比較重的操作。
需要注意是，JSON 類(lèi)型是從 MySQL 5.7 版本開(kāi)始支持的功能，而 8.0 版本解決了更新 JSON 的日志性能瓶頸。如果要在生產(chǎn)環(huán)境中使用 JSON 數(shù)據(jù)類(lèi)型，強(qiáng)烈推薦使用 MySQL 8.0 版本。
講到這兒，你已經(jīng)對(duì) JSON 類(lèi)型的基本概念有所了解了，接下來(lái)，我們進(jìn)入實(shí)戰(zhàn)環(huán)節(jié)：如何在業(yè)務(wù)中用好JSON類(lèi)型？
# 業(yè)務(wù)表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn)
用戶登錄設(shè)計(jì)
在數(shù)據(jù)庫(kù)中，JSON 類(lèi)型比較適合存儲(chǔ)一些修改較少、相對(duì)靜態(tài)的數(shù)據(jù)，比如用戶登錄信息的存儲(chǔ)如下：
DROP?TABLE?IF?EXISTS?UserLogin;
CREATE TABLE UserLogin ( userId BIGINT NOT NULL, loginInfo JSON, PRIMARY KEY(userId));
由于當(dāng)前業(yè)務(wù)的登錄方式越來(lái)越多樣化，如同一賬戶支持手機(jī)、微信、QQ 賬號(hào)登錄，所以這里可以用 JSON 類(lèi)型存儲(chǔ)登錄的信息。
接著，插入下面的數(shù)據(jù)：
SET @a = '{ "cellphone" : "1", "wxchat" : "碼農(nóng)", "77" : "1"}';
INSERT?INTO?UserLogin?VALUES?(1,@a);
SET @b = '{ "cellphone" : "1188"}';
INSERT INTO UserLogin VALUES (2,@b);
從上面的例子中可以看到，用戶 1 登錄有三種方式：手機(jī)驗(yàn)證碼登錄、微信登錄、QQ 登錄，而用戶 2 只有手機(jī)驗(yàn)證碼登錄。
而如果不采用 JSON 數(shù)據(jù)類(lèi)型，就要用下面的方式建表：
SELECT userId, JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone, JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchatFROM UserLogin;+--------+-------------+--------------+| userId | cellphone | wxchat |+--------+-------------+--------------+| 1 | 11| 碼農(nóng) || 2 | 11| NULL |+--------+-------------+--------------+2 rows in set (0.01 sec)
當(dāng)然了，每次寫(xiě) JSON_EXTRACT、JSON_UNQUOTE 非常麻煩，MySQL 還提供了 ->> 表達(dá)式，和上述 SQL 效果完全一樣：
SELECT userId, loginInfo->>"$.cellphone" cellphone, loginInfo->>"$.wxchat" wxchatFROM UserLogin;
當(dāng) JSON 數(shù)據(jù)量非常大，用戶希望對(duì) JSON 數(shù)據(jù)進(jìn)行有效檢索時(shí)，可以利用 MySQL 的 函數(shù)索引 功能對(duì) JSON 中的某個(gè)字段進(jìn)行索引。
比如在上面的用戶登錄示例中，假設(shè)用戶必須綁定唯一手機(jī)號(hào)，且希望未來(lái)能用手機(jī)號(hào)碼進(jìn)行用戶檢索時(shí)，可以創(chuàng)建下面的索引：
ALTER?TABLE?UserLogin?ADD?COLUMN?cellphone?VARCHAR(255)?AS?(loginInfo->>"$.cellphone");
ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);
上述 SQL 首先創(chuàng)建了一個(gè)虛擬列 cellphone，這個(gè)列是由函數(shù) loginInfo->>"$.cellphone" 計(jì)算得到的。然后在這個(gè)虛擬列上創(chuàng)建一個(gè)唯一索引 idx_cellphone。這時(shí)再通過(guò)虛擬列 cellphone 進(jìn)行查詢(xún)，就可以看到優(yōu)化器會(huì)使用到新創(chuàng)建的 idx_cellphone 索引：
EXPLAIN SELECT * FROM UserLogin WHERE cellphone = '11'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserLogin partitions: NULL type: constpossible_keys: idx_cellphone key: idx_cellphone key_len: 1023 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)
當(dāng)然，我們可以在一開(kāi)始創(chuàng)建表的時(shí)候，就完成虛擬列及函數(shù)索引的創(chuàng)建。如下表創(chuàng)建的列 cellphone 對(duì)應(yīng)的就是 JSON 中的內(nèi)容，是個(gè)虛擬列；uk_idx_cellphone 就是在虛擬列 cellphone 上所創(chuàng)建的索引。
CREATE TABLE UserLogin ( userId BIGINT, loginInfo JSON, cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"), PRIMARY KEY(userId), UNIQUE KEY uk_idx_cellphone(cellphone));
用戶畫(huà)像設(shè)計(jì)
某些業(yè)務(wù)需要做用戶畫(huà)像（也就是對(duì)用戶打標(biāo)簽），然后根據(jù)用戶的標(biāo)簽，通過(guò)數(shù)據(jù)挖掘技術(shù)，進(jìn)行相應(yīng)的產(chǎn)品推薦。比如：
在電商行業(yè)中，根據(jù)用戶的穿搭喜好，推薦相應(yīng)的商品；
在音樂(lè)行業(yè)中，根據(jù)用戶喜歡的音樂(lè)風(fēng)格和常聽(tīng)的歌手，推薦相應(yīng)的歌曲；
在金融行業(yè)，根據(jù)用戶的風(fēng)險(xiǎn)喜好和投資經(jīng)驗(yàn)，推薦相應(yīng)的理財(cái)產(chǎn)品。
在這，我強(qiáng)烈推薦你用 JSON 類(lèi)型在數(shù)據(jù)庫(kù)中存儲(chǔ)用戶畫(huà)像信息，并結(jié)合 JSON 數(shù)組類(lèi)型和多值索引的特點(diǎn)進(jìn)行高效查詢(xún)。假設(shè)有張畫(huà)像定義表：
CREATE TABLE Tags ( tagId bigint auto_increment, tagName varchar(255) NOT NULL, primary key(tagId));
SELECT * FROM Tags;+-------+--------------+| tagId | tagName |+-------+--------------+| 1 | 70后 || 2 | 80后 || 3 | 90后 || 4 | 00后 || 5 | 愛(ài)運(yùn)動(dòng) || 6 | 高學(xué)歷 || 7 | 小資 || 8 | 有房 || 9 | 有車(chē) || 10 | 常看電影 || 11 | 愛(ài)網(wǎng)購(gòu) || 12 | 愛(ài)外賣(mài) |+-------+--------------+
可以看到，表 Tags 是一張畫(huà)像定義表，用于描述當(dāng)前定義有多少個(gè)標(biāo)簽，接著給每個(gè)用戶打標(biāo)簽，比如用戶 David，他的標(biāo)簽是 80 后、高學(xué)歷、小資、有房、?？措娪?；用戶 Tom，90 后、?？措娪?、愛(ài)外賣(mài)。
若不用 JSON 數(shù)據(jù)類(lèi)型進(jìn)行標(biāo)簽存儲(chǔ)，通常會(huì)將用戶標(biāo)簽通過(guò)字符串，加上分割符的方式，在一個(gè)字段中存取用戶所有的標(biāo)簽：
+-------+---------------------------------------+|用戶 |標(biāo)簽 |+-------+---------------------------------------+|David |80后 ；高學(xué)歷 ；小資 ；有房 ；常看電影 ||Tom |90后 ；?？措娪?；愛(ài)外賣(mài) |+-------+---------------------------------------
這樣做的缺點(diǎn)是：不好搜索特定畫(huà)像的用戶，另外分隔符也是一種自我約定，在數(shù)據(jù)庫(kù)中其實(shí)可以任意存儲(chǔ)其他數(shù)據(jù)，最終產(chǎn)生臟數(shù)據(jù)。
用 JSON 數(shù)據(jù)類(lèi)型就能很好解決這個(gè)問(wèn)題：
DROP TABLE IF EXISTS UserTag;CREATE TABLE UserTag ( userId bigint NOT NULL, userTags JSON, PRIMARY KEY (userId));
INSERT INTO UserTag VALUES (1,'[2,6,8,10]');INSERT INTO UserTag VALUES (2,'[3,10,12]');
其中，userTags 存儲(chǔ)的標(biāo)簽就是表 Tags 已定義的那些標(biāo)簽值，只是使用 JSON 數(shù)組類(lèi)型進(jìn)行存儲(chǔ)。
MySQL 8.0.17 版本開(kāi)始支持 Multi-Valued Indexes，用于在 JSON 數(shù)組上創(chuàng)建索引，并通過(guò)函數(shù) member of、json_contains、json_overlaps 來(lái)快速檢索索引數(shù)據(jù)。所以你可以在表 UserTag 上創(chuàng)建 Multi-Valued Indexes：
ALTER TABLE UserTagADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));
如果想要查詢(xún)用戶畫(huà)像為?？措娪暗挠脩?，可以使用函數(shù) MEMBER OF：
EXPLAIN SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$")\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL type: refpossible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)
SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$");+--------+---------------+| userId | userTags |+--------+---------------+| 1 | [2, 6, 8, 10] || 2 | [3, 10, 12] |+--------+---------------+2 rows in set (0.00 sec)
如果想要查詢(xún)畫(huà)像為 80 后，且?？措娪暗挠脩?，可以使用函數(shù)
JSON_CONTAINS：
EXPLAIN SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]')\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL type: rangepossible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: NULL rows: 3 filtered: 100.00 Extra: Using where1?row?in?set,?1?warning?(0.00?sec)
SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]');+--------+---------------+| userId | userTags |+--------+---------------+| 1 | [2, 6, 8, 10] |+--------+---------------+1 row in set (0.00 sec)
如果想要查詢(xún)畫(huà)像為 80 后、90 后，且?？措娪暗挠脩?，則可以使用函數(shù) JSON_OVERLAP：
EXPLAIN SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]')\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL type: rangepossible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: NULL rows: 4 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)
SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]');+--------+---------------+| userId | userTags |+--------+---------------+| 1 | [2, 6, 8, 10] || 2 | [3, 10, 12] |+--------+---------------+2 rows in set (0.01 sec)
JSON 類(lèi)型是 MySQL 5.7 版本新增的數(shù)據(jù)類(lèi)型，用好 JSON 數(shù)據(jù)類(lèi)型可以有效解決很多業(yè)務(wù)中實(shí)際問(wèn)題。最后，我總結(jié)下今天的重點(diǎn)內(nèi)容：
使用 JSON 數(shù)據(jù)類(lèi)型，推薦用 MySQL 8.0.17 以上的版本，性能更好，同時(shí)也支持 Multi-Valued Indexes
JSON 數(shù)據(jù)類(lèi)型的好處是無(wú)須預(yù)先定義列，數(shù)據(jù)本身就具有很好的描述性；
不要將有明顯關(guān)系型的數(shù)據(jù)用 JSON 存儲(chǔ)，如用戶余額、用戶姓名、用戶身份證等，這些都是每個(gè)用戶必須包含的數(shù)據(jù)；
JSON 數(shù)據(jù)類(lèi)型推薦使用在不經(jīng)常更新的靜態(tài)數(shù)據(jù)存儲(chǔ)。
技術(shù)交流群
D哥也建了一個(gè)技術(shù)群，主要針對(duì)一些新的技術(shù)和開(kāi)源項(xiàng)目值不值得去研究和IDEA使用的“騷操作”，有興趣入群的同學(xué)，可以長(zhǎng)掃描區(qū)域二維碼，一定要注意事項(xiàng)：城市+昵稱(chēng)+技術(shù)方向，根據(jù)格式備注，可快速通過(guò)。
▲長(zhǎng)按掃描
瀏覽
43
來(lái)源：blog.csdn.net/java_pfx/article/details/116594654
關(guān)系型的結(jié)構(gòu)化存儲(chǔ)存在一定的弊端，因?yàn)樗枰A(yù)先定義好所有的列以及列對(duì)應(yīng)的類(lèi)型。但是業(yè)務(wù)在發(fā)展過(guò)程中，或許需要擴(kuò)展單個(gè)列的描述功能，這時(shí)，如果能用好 JSON 數(shù)據(jù)類(lèi)型，那就能打通關(guān)系型和非關(guān)系型數(shù)據(jù)的存儲(chǔ)之間的界限，為業(yè)務(wù)提供更好的架構(gòu)選擇。
當(dāng)然，很多同學(xué)在用 JSON 數(shù)據(jù)類(lèi)型時(shí)會(huì)遇到各種各樣的問(wèn)題，其中最容易犯的誤區(qū)就是將類(lèi)型 JSON 簡(jiǎn)單理解成字符串類(lèi)型。但當(dāng)你看完這篇文章后，會(huì)真正認(rèn)識(shí)到 JSON 數(shù)據(jù)類(lèi)型的威力，從而在實(shí)際工作中更好地存儲(chǔ)非結(jié)構(gòu)化的數(shù)據(jù)。
# JSON 數(shù)據(jù)類(lèi)型
JSON（JavaScript Object Notation）主要用于互聯(lián)網(wǎng)應(yīng)用服務(wù)之間的數(shù)據(jù)交換。MySQL 支持RFC 7159定義的 JSON 規(guī)范，主要有 JSON 對(duì)象 和 JSON 數(shù)組 兩種類(lèi)型。下面就是 JSON 對(duì)象，主要用來(lái)存儲(chǔ)圖片的相關(guān)信息：
{"Image": {"Width": 800,"Height": 600,"Title": "View from 15th Floor","Thumbnail": {"Url": "http://www.example.com/image/xx9943","Height": 125,"Width": 100},"IDs": [116, 943, 234, 38793]}}
從中你可以看到， JSON 類(lèi)型可以很好地描述數(shù)據(jù)的相關(guān)內(nèi)容，比如這張圖片的寬度、高度、標(biāo)題等（這里使用到的類(lèi)型有整型、字符串類(lèi)型）。
JSON對(duì)象除了支持字符串、整型、日期類(lèi)型，JSON 內(nèi)嵌的字段也支持?jǐn)?shù)組類(lèi)型，如上代碼中的 IDs 字段。
另一種 JSON 數(shù)據(jù)類(lèi)型是數(shù)組類(lèi)型，如：
[{"precision": "zip","Latitude": 37.7668,"Longitude": -122.3959,"Address": "","City": "SAN FRANCISCO","State": "CA","Zip": "94107","Country": "US"},{"precision": "zip","Latitude": 37.371991,"Longitude": -122.026020,"Address": "","City": "SUNNYVALE","State": "CA","Zip": "94085","Country": "US"}]
上面的示例演示的是一個(gè) JSON 數(shù)組，其中有 2 個(gè) JSON 對(duì)象。
到目前為止，可能很多同學(xué)會(huì)把 JSON 當(dāng)作一個(gè)很大的字段串類(lèi)型，從表面上來(lái)看，沒(méi)有錯(cuò)。但本質(zhì)上，JSON 是一種新的類(lèi)型，有自己的存儲(chǔ)格式，還能在每個(gè)對(duì)應(yīng)的字段上創(chuàng)建索引，做特定的優(yōu)化，這是傳統(tǒng)字段串無(wú)法實(shí)現(xiàn)的。JSON 類(lèi)型的另一個(gè)好處是無(wú)須預(yù)定義字段，字段可以無(wú)限擴(kuò)展。而傳統(tǒng)關(guān)系型數(shù)據(jù)庫(kù)的列都需預(yù)先定義，想要擴(kuò)展需要執(zhí)行 ALTER TABLE … ADD COLUMN … 這樣比較重的操作。
需要注意是，JSON 類(lèi)型是從 MySQL 5.7 版本開(kāi)始支持的功能，而 8.0 版本解決了更新 JSON 的日志性能瓶頸。如果要在生產(chǎn)環(huán)境中使用 JSON 數(shù)據(jù)類(lèi)型，強(qiáng)烈推薦使用 MySQL 8.0 版本。
講到這兒，你已經(jīng)對(duì) JSON 類(lèi)型的基本概念有所了解了，接下來(lái)，我們進(jìn)入實(shí)戰(zhàn)環(huán)節(jié)：如何在業(yè)務(wù)中用好JSON類(lèi)型？
# 業(yè)務(wù)表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn)
用戶登錄設(shè)計(jì)
在數(shù)據(jù)庫(kù)中，JSON 類(lèi)型比較適合存儲(chǔ)一些修改較少、相對(duì)靜態(tài)的數(shù)據(jù)，比如用戶登錄信息的存儲(chǔ)如下：
DROP?TABLE?IF?EXISTS?UserLogin;CREATE TABLE UserLogin (userId BIGINT NOT NULL,loginInfo JSON,PRIMARY KEY(userId));
由于當(dāng)前業(yè)務(wù)的登錄方式越來(lái)越多樣化，如同一賬戶支持手機(jī)、微信、QQ 賬號(hào)登錄，所以這里可以用 JSON 類(lèi)型存儲(chǔ)登錄的信息。
接著，插入下面的數(shù)據(jù)：
SET @a = '{"cellphone" : "1","wxchat" : "碼農(nóng)","77" : "1"}';INSERT?INTO?UserLogin?VALUES?(1,@a);SET @b = '{"cellphone" : "1188"}';INSERT INTO UserLogin VALUES (2,@b);
從上面的例子中可以看到，用戶 1 登錄有三種方式：手機(jī)驗(yàn)證碼登錄、微信登錄、QQ 登錄，而用戶 2 只有手機(jī)驗(yàn)證碼登錄。
而如果不采用 JSON 數(shù)據(jù)類(lèi)型，就要用下面的方式建表：
SELECTuserId,JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchatFROM UserLogin;+--------+-------------+--------------+| userId | cellphone | wxchat |+--------+-------------+--------------+| 1 | 11| 碼農(nóng) || 2 | 11| NULL |+--------+-------------+--------------+2 rows in set (0.01 sec)
當(dāng)然了，每次寫(xiě) JSON_EXTRACT、JSON_UNQUOTE 非常麻煩，MySQL 還提供了 ->> 表達(dá)式，和上述 SQL 效果完全一樣：
SELECTuserId,loginInfo->>"$.cellphone" cellphone,loginInfo->>"$.wxchat" wxchatFROM UserLogin;
當(dāng) JSON 數(shù)據(jù)量非常大，用戶希望對(duì) JSON 數(shù)據(jù)進(jìn)行有效檢索時(shí)，可以利用 MySQL 的 函數(shù)索引 功能對(duì) JSON 中的某個(gè)字段進(jìn)行索引。
比如在上面的用戶登錄示例中，假設(shè)用戶必須綁定唯一手機(jī)號(hào)，且希望未來(lái)能用手機(jī)號(hào)碼進(jìn)行用戶檢索時(shí)，可以創(chuàng)建下面的索引：
ALTER?TABLE?UserLogin?ADD?COLUMN?cellphone?VARCHAR(255)?AS?(loginInfo->>"$.cellphone");ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);
上述 SQL 首先創(chuàng)建了一個(gè)虛擬列 cellphone，這個(gè)列是由函數(shù) loginInfo->>"$.cellphone" 計(jì)算得到的。然后在這個(gè)虛擬列上創(chuàng)建一個(gè)唯一索引 idx_cellphone。這時(shí)再通過(guò)虛擬列 cellphone 進(jìn)行查詢(xún)，就可以看到優(yōu)化器會(huì)使用到新創(chuàng)建的 idx_cellphone 索引：
EXPLAIN SELECT * FROM UserLoginWHERE cellphone = '11'\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: UserLoginpartitions: NULLtype: constpossible_keys: idx_cellphonekey: idx_cellphonekey_len: 1023ref: constrows: 1filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)
當(dāng)然，我們可以在一開(kāi)始創(chuàng)建表的時(shí)候，就完成虛擬列及函數(shù)索引的創(chuàng)建。如下表創(chuàng)建的列 cellphone 對(duì)應(yīng)的就是 JSON 中的內(nèi)容，是個(gè)虛擬列；uk_idx_cellphone 就是在虛擬列 cellphone 上所創(chuàng)建的索引。
CREATE TABLE UserLogin (userId BIGINT,loginInfo JSON,cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),PRIMARY KEY(userId),UNIQUE KEY uk_idx_cellphone(cellphone));
用戶畫(huà)像設(shè)計(jì)
某些業(yè)務(wù)需要做用戶畫(huà)像（也就是對(duì)用戶打標(biāo)簽），然后根據(jù)用戶的標(biāo)簽，通過(guò)數(shù)據(jù)挖掘技術(shù)，進(jìn)行相應(yīng)的產(chǎn)品推薦。比如：
在電商行業(yè)中，根據(jù)用戶的穿搭喜好，推薦相應(yīng)的商品；
在音樂(lè)行業(yè)中，根據(jù)用戶喜歡的音樂(lè)風(fēng)格和常聽(tīng)的歌手，推薦相應(yīng)的歌曲；
在金融行業(yè)，根據(jù)用戶的風(fēng)險(xiǎn)喜好和投資經(jīng)驗(yàn)，推薦相應(yīng)的理財(cái)產(chǎn)品。
在這，我強(qiáng)烈推薦你用 JSON 類(lèi)型在數(shù)據(jù)庫(kù)中存儲(chǔ)用戶畫(huà)像信息，并結(jié)合 JSON 數(shù)組類(lèi)型和多值索引的特點(diǎn)進(jìn)行高效查詢(xún)。假設(shè)有張畫(huà)像定義表：
CREATE TABLE Tags (tagId bigint auto_increment,tagName varchar(255) NOT NULL,primary key(tagId));SELECT * FROM Tags;+-------+--------------+| tagId | tagName |+-------+--------------+| 1 | 70后 || 2 | 80后 || 3 | 90后 || 4 | 00后 || 5 | 愛(ài)運(yùn)動(dòng) || 6 | 高學(xué)歷 || 7 | 小資 || 8 | 有房 || 9 | 有車(chē) || 10 | 常看電影 || 11 | 愛(ài)網(wǎng)購(gòu) || 12 | 愛(ài)外賣(mài) |+-------+--------------+
可以看到，表 Tags 是一張畫(huà)像定義表，用于描述當(dāng)前定義有多少個(gè)標(biāo)簽，接著給每個(gè)用戶打標(biāo)簽，比如用戶 David，他的標(biāo)簽是 80 后、高學(xué)歷、小資、有房、?？措娪?；用戶 Tom，90 后、?？措娪?、愛(ài)外賣(mài)。
若不用 JSON 數(shù)據(jù)類(lèi)型進(jìn)行標(biāo)簽存儲(chǔ)，通常會(huì)將用戶標(biāo)簽通過(guò)字符串，加上分割符的方式，在一個(gè)字段中存取用戶所有的標(biāo)簽：
+-------+---------------------------------------+|用戶 |標(biāo)簽 |+-------+---------------------------------------+|David |80后 ；高學(xué)歷 ；小資 ；有房 ；常看電影 ||Tom |90后 ；?？措娪?；愛(ài)外賣(mài) |+-------+---------------------------------------
這樣做的缺點(diǎn)是：不好搜索特定畫(huà)像的用戶，另外分隔符也是一種自我約定，在數(shù)據(jù)庫(kù)中其實(shí)可以任意存儲(chǔ)其他數(shù)據(jù)，最終產(chǎn)生臟數(shù)據(jù)。
用 JSON 數(shù)據(jù)類(lèi)型就能很好解決這個(gè)問(wèn)題：
DROP TABLE IF EXISTS UserTag;CREATE TABLE UserTag (userId bigint NOT NULL,userTags JSON,PRIMARY KEY (userId));INSERT INTO UserTag VALUES (1,'[2,6,8,10]');INSERT INTO UserTag VALUES (2,'[3,10,12]');
其中，userTags 存儲(chǔ)的標(biāo)簽就是表 Tags 已定義的那些標(biāo)簽值，只是使用 JSON 數(shù)組類(lèi)型進(jìn)行存儲(chǔ)。
MySQL 8.0.17 版本開(kāi)始支持 Multi-Valued Indexes，用于在 JSON 數(shù)組上創(chuàng)建索引，并通過(guò)函數(shù) member of、json_contains、json_overlaps 來(lái)快速檢索索引數(shù)據(jù)。所以你可以在表 UserTag 上創(chuàng)建 Multi-Valued Indexes：
ALTER TABLE UserTagADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));
如果想要查詢(xún)用戶畫(huà)像為?？措娪暗挠脩?，可以使用函數(shù) MEMBER OF：
EXPLAIN SELECT * FROM UserTagWHERE 10 MEMBER OF(userTags->"$")\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: UserTagpartitions: NULLtype: refpossible_keys: idx_user_tagskey: idx_user_tagskey_len: 9ref: constrows: 1filtered: 100.00Extra: Using where1 row in set, 1 warning (0.00 sec)SELECT * FROM UserTagWHERE 10 MEMBER OF(userTags->"$");+--------+---------------+| userId | userTags |+--------+---------------+| 1 | [2, 6, 8, 10] || 2 | [3, 10, 12] |+--------+---------------+2 rows in set (0.00 sec)
如果想要查詢(xún)畫(huà)像為 80 后，且?？措娪暗挠脩?，可以使用函數(shù)
JSON_CONTAINS：EXPLAIN SELECT * FROM UserTagWHERE JSON_CONTAINS(userTags->"$", '[2,10]')\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: UserTagpartitions: NULLtype: rangepossible_keys: idx_user_tagskey: idx_user_tagskey_len: 9ref: NULLrows: 3filtered: 100.00Extra: Using where1?row?in?set,?1?warning?(0.00?sec)SELECT * FROM UserTagWHERE JSON_CONTAINS(userTags->"$", '[2,10]');+--------+---------------+| userId | userTags |+--------+---------------+| 1 | [2, 6, 8, 10] |+--------+---------------+1 row in set (0.00 sec)
如果想要查詢(xún)畫(huà)像為 80 后、90 后，且?？措娪暗挠脩?，則可以使用函數(shù) JSON_OVERLAP：
EXPLAIN SELECT * FROM UserTagWHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]')\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: UserTagpartitions: NULLtype: rangepossible_keys: idx_user_tagskey: idx_user_tagskey_len: 9ref: NULLrows: 4filtered: 100.00Extra: Using where1 row in set, 1 warning (0.00 sec)SELECT * FROM UserTagWHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]');+--------+---------------+| userId | userTags |+--------+---------------+| 1 | [2, 6, 8, 10] || 2 | [3, 10, 12] |+--------+---------------+2 rows in set (0.01 sec)
JSON 類(lèi)型是 MySQL 5.7 版本新增的數(shù)據(jù)類(lèi)型，用好 JSON 數(shù)據(jù)類(lèi)型可以有效解決很多業(yè)務(wù)中實(shí)際問(wèn)題。最后，我總結(jié)下今天的重點(diǎn)內(nèi)容：
使用 JSON 數(shù)據(jù)類(lèi)型，推薦用 MySQL 8.0.17 以上的版本，性能更好，同時(shí)也支持 Multi-Valued Indexes
JSON 數(shù)據(jù)類(lèi)型的好處是無(wú)須預(yù)先定義列，數(shù)據(jù)本身就具有很好的描述性；
不要將有明顯關(guān)系型的數(shù)據(jù)用 JSON 存儲(chǔ)，如用戶余額、用戶姓名、用戶身份證等，這些都是每個(gè)用戶必須包含的數(shù)據(jù)；
JSON 數(shù)據(jù)類(lèi)型推薦使用在不經(jīng)常更新的靜態(tài)數(shù)據(jù)存儲(chǔ)。
D哥也建了一個(gè)技術(shù)群，主要針對(duì)一些新的技術(shù)和開(kāi)源項(xiàng)目值不值得去研究和IDEA使用的“騷操作”，有興趣入群的同學(xué)，可以長(zhǎng)掃描區(qū)域二維碼，一定要注意事項(xiàng)：城市+昵稱(chēng)+技術(shù)方向，根據(jù)格式備注，可快速通過(guò)。
▲長(zhǎng)按掃描