MySQL + JSON = 王炸?。?/h1>
點(diǎn)擊關(guān)注公眾號，Java干貨及時(shí)送達(dá)
關(guān)系型的結(jié)構(gòu)化存儲存在一定的弊端，因?yàn)樗枰A(yù)先定義好所有的列以及列對應(yīng)的類型。但是業(yè)務(wù)在發(fā)展過程中，或許需要擴(kuò)展單個(gè)列的描述功能，這時(shí)，如果能用好 JSON 數(shù)據(jù)類型，那就能打通關(guān)系型和非關(guān)系型數(shù)據(jù)的存儲之間的界限，為業(yè)務(wù)提供更好的架構(gòu)選擇。
當(dāng)然，很多同學(xué)在用 JSON 數(shù)據(jù)類型時(shí)會(huì)遇到各種各樣的問題，其中最容易犯的誤區(qū)就是將類型 JSON 簡單理解成字符串類型。但當(dāng)你看完這篇文章后，會(huì)真正認(rèn)識到 JSON 數(shù)據(jù)類型的威力，從而在實(shí)際工作中更好地存儲非結(jié)構(gòu)化的數(shù)據(jù)。
JSON 數(shù)據(jù)類型
{
"Image": {
"Width": 800,
"Height": 600,
"Title": "View from 15th Floor",
"Thumbnail": {
"Url": "http://www.example.com/image/481989943",
"Height": 125,
"Width": 100
},
"IDs": [116, 943, 234, 38793]
}
}
從中你可以看到， JSON 類型可以很好地描述數(shù)據(jù)的相關(guān)內(nèi)容，比如這張圖片的寬度、高度、標(biāo)題等（這里使用到的類型有整型、字符串類型）。
JSON對象除了支持字符串、整型、日期類型，JSON 內(nèi)嵌的字段也支持?jǐn)?shù)組類型，如上代碼中的 IDs 字段。
最新面試題整理：https://www.javastack.cn/mst/
另一種 JSON 數(shù)據(jù)類型是數(shù)組類型，如：
[
{
"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 對象。
講到這兒，你已經(jīng)對 JSON 類型的基本概念有所了解了，接下來，我們進(jìn)入實(shí)戰(zhàn)環(huán)節(jié)：如何在業(yè)務(wù)中用好JSON類型？
點(diǎn)擊關(guān)注公眾號，Java干貨及時(shí)送達(dá)
業(yè)務(wù)表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn)
用戶登錄設(shè)計(jì)
在數(shù)據(jù)庫中，JSON 類型比較適合存儲一些修改較少、相對靜態(tài)的數(shù)據(jù)，比如用戶登錄信息的存儲如下：
DROP TABLE IF EXISTS UserLogin;
CREATE TABLE UserLogin (
userId BIGINT NOT NULL,
loginInfo JSON,
PRIMARY KEY(userId)
);
由于當(dāng)前業(yè)務(wù)的登錄方式越來越多樣化，如同一賬戶支持手機(jī)、微信、QQ 賬號登錄，所以這里可以用 JSON 類型存儲登錄的信息。
最新面試題整理好了，大家可以在Java面試庫小程序在線刷題。
接著，插入下面的數(shù)據(jù)：
SET @a = '
{
"cellphone" : "13918888888",
"wxchat" : "破產(chǎn)碼農(nóng)",
"QQ" : "82946772"
}
';
INSERT INTO UserLogin VALUES (1,@a);
SET @b = '
{
"cellphone" : "15026888888"
}
';
INSERT INTO UserLogin VALUES (2,@b);
從上面的例子中可以看到，用戶 1 登錄有三種方式：手機(jī)驗(yàn)證碼登錄、微信登錄、QQ 登錄，而用戶 2 只有手機(jī)驗(yàn)證碼登錄。
而如果不采用 JSON 數(shù)據(jù)類型，就要用下面的方式建表：
SELECT
userId,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;
+--------+-------------+--------------+
| userId | cellphone | wxchat |
+--------+-------------+--------------+
| 1 | 13918888888 | 破產(chǎn)碼農(nóng) |
| 2 | 15026888888 | NULL |
+--------+-------------+--------------+
2 rows in set (0.01 sec)
當(dāng)然了，每次寫 JSON_EXTRACT、JSON_UNQUOTE 非常麻煩，MySQL 還提供了 ->> 表達(dá)式，和上述 SQL 效果完全一樣：
SELECT
userId,
loginInfo->>"$.cellphone" cellphone,
loginInfo->>"$.wxchat" wxchat
FROM UserLogin;
當(dāng) JSON 數(shù)據(jù)量非常大，用戶希望對 JSON 數(shù)據(jù)進(jìn)行有效檢索時(shí)，可以利用 MySQL 的 函數(shù)索引 功能對 JSON 中的某個(gè)字段進(jìn)行索引。
最新 MySQL 面試題整理好了，大家可以在Java面試庫小程序在線刷題。
比如在上面的用戶登錄示例中，假設(shè)用戶必須綁定唯一手機(jī)號，且希望未來能用手機(jī)號碼進(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í)再通過虛擬列 cellphone 進(jìn)行查詢，就可以看到優(yōu)化器會(huì)使用到新創(chuàng)建的 idx_cellphone 索引：
EXPLAIN SELECT * FROM UserLogin
WHERE cellphone = '13918888888'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: UserLogin
partitions: NULL
type: const
possible_keys: idx_cellphone
key: idx_cellphone
key_len: 1023
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
當(dāng)然，我們可以在一開始創(chuàng)建表的時(shí)候，就完成虛擬列及函數(shù)索引的創(chuàng)建。如下表創(chuàng)建的列 cellphone 對應(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)
);
用戶畫像設(shè)計(jì)
某些業(yè)務(wù)需要做用戶畫像（也就是對用戶打標(biāo)簽），然后根據(jù)用戶的標(biāo)簽，通過數(shù)據(jù)挖掘技術(shù)，進(jìn)行相應(yīng)的產(chǎn)品推薦。這份架構(gòu)師圖譜建議看看，少走彎路。
比如：
在電商行業(yè)中，根據(jù)用戶的穿搭喜好，推薦相應(yīng)的商品； 在音樂行業(yè)中，根據(jù)用戶喜歡的音樂風(fēng)格和常聽的歌手，推薦相應(yīng)的歌曲； 在金融行業(yè)，根據(jù)用戶的風(fēng)險(xiǎn)喜好和投資經(jīng)驗(yàn)，推薦相應(yīng)的理財(cái)產(chǎn)品。
在這，我強(qiáng)烈推薦你用 JSON 類型在數(shù)據(jù)庫中存儲用戶畫像信息，并結(jié)合 JSON 數(shù)組類型和多值索引的特點(diǎn)進(jìn)行高效查詢。假設(shè)有張畫像定義表：
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 | 愛運(yùn)動(dòng) |
| 6 | 高學(xué)歷 |
| 7 | 小資 |
| 8 | 有房 |
| 9 | 有車 |
| 10 | ?？措娪?nbsp; |
| 11 | 愛網(wǎng)購 |
| 12 | 愛外賣 |
+-------+--------------+
可以看到，表 Tags 是一張畫像定義表，用于描述當(dāng)前定義有多少個(gè)標(biāo)簽，接著給每個(gè)用戶打標(biāo)簽，比如用戶 David，他的標(biāo)簽是 80 后、高學(xué)歷、小資、有房、?？措娪埃挥脩?Tom，90 后、?？措娪?、愛外賣。
點(diǎn)擊關(guān)注公眾號，Java干貨及時(shí)送達(dá)
若不用 JSON 數(shù)據(jù)類型進(jìn)行標(biāo)簽存儲，通常會(huì)將用戶標(biāo)簽通過字符串，加上分割符的方式，在一個(gè)字段中存取用戶所有的標(biāo)簽：
+-------+---------------------------------------+
|用戶 |標(biāo)簽 |
+-------+---------------------------------------+
|David |80后 ； 高學(xué)歷 ； 小資 ； 有房 ；常看電影 |
|Tom |90后 ；?？措娪?nbsp;； 愛外賣 |
+-------+---------------------------------------
這樣做的缺點(diǎn)是：不好搜索特定畫像的用戶，另外分隔符也是一種自我約定，在數(shù)據(jù)庫中其實(shí)可以任意存儲其他數(shù)據(jù)，最終產(chǎn)生臟數(shù)據(jù)。
用 JSON 數(shù)據(jù)類型就能很好解決這個(gè)問題：
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 存儲的標(biāo)簽就是表 Tags 已定義的那些標(biāo)簽值，只是使用 JSON 數(shù)組類型進(jìn)行存儲。
另外，MySQL 系列面試題和答案全部整理好了，微信搜索Java技術(shù)棧，在后臺發(fā)送：面試，可以在線閱讀。
MySQL 8.0.17 版本開始支持 Multi-Valued Indexes，用于在 JSON 數(shù)組上創(chuàng)建索引，并通過函數(shù) member of、json_contains、json_overlaps 來快速檢索索引數(shù)據(jù)。所以你可以在表 UserTag 上創(chuàng)建 Multi-Valued Indexes：
ALTER TABLE UserTag
ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));
如果想要查詢用戶畫像為?？措娪暗挠脩?，可以使用函數(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: ref
possible_keys: idx_user_tags
key: idx_user_tags
key_len: 9
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 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)
如果想要查詢畫像為 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: range
possible_keys: idx_user_tags
key: idx_user_tags
key_len: 9
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where
1 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)
如果想要查詢畫像為 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: range
possible_keys: idx_user_tags
key: idx_user_tags
key_len: 9
ref: NULL
rows: 4
filtered: 100.00
Extra: Using where
1 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)
總結(jié)
JSON 類型是 MySQL 5.7 版本新增的數(shù)據(jù)類型，用好 JSON 數(shù)據(jù)類型可以有效解決很多業(yè)務(wù)中實(shí)際問題。
最新面試題整理：https://www.javastack.cn/mst/
最后，我總結(jié)下今天的重點(diǎn)內(nèi)容：
使用 JSON 數(shù)據(jù)類型，推薦用 MySQL 8.0.17 以上的版本，性能更好，同時(shí)也支持 Multi-Valued Indexes； JSON 數(shù)據(jù)類型的好處是無須預(yù)先定義列，數(shù)據(jù)本身就具有很好的描述性； 不要將有明顯關(guān)系型的數(shù)據(jù)用 JSON 存儲，如用戶余額、用戶姓名、用戶身份證等，這些都是每個(gè)用戶必須包含的數(shù)據(jù)； JSON 數(shù)據(jù)類型推薦使用在不經(jīng)常更新的靜態(tài)數(shù)據(jù)存儲。
版權(quán)聲明：本文為博主原創(chuàng)文章，遵循 CC 4.0 BY-SA 版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接和本聲明。
本文鏈接：https://blog.csdn.net/java_pfx/article/details/116594654
開工大吉！再發(fā) 10,000 個(gè)紅包封面 2021 年發(fā)生的 10 件技術(shù)大事！！
23 種設(shè)計(jì)模式實(shí)戰(zhàn)（很全）
換掉 Log4j2！tinylog 橫空出世 一款基于 Spring Boot 的神仙接私活項(xiàng)目 勁爆！Java 協(xié)程要來了！
重磅官宣：Redis 對象映射框架來了??！ 推薦一款代碼神器，代碼量至少省一半！ 程序員精通各種技術(shù)體系，45歲求職難！
Spring Boot 3.0 M1 發(fā)布，正式棄用 Java 8 Spring Boot 學(xué)習(xí)筆記，這個(gè)太全了！
關(guān)注Java技術(shù)?？锤喔韶?/strong>
獲取 Spring Boot 實(shí)戰(zhàn)筆記！
點(diǎn)擊關(guān)注公眾號，Java干貨及時(shí)送達(dá)
關(guān)系型的結(jié)構(gòu)化存儲存在一定的弊端，因?yàn)樗枰A(yù)先定義好所有的列以及列對應(yīng)的類型。但是業(yè)務(wù)在發(fā)展過程中，或許需要擴(kuò)展單個(gè)列的描述功能，這時(shí)，如果能用好 JSON 數(shù)據(jù)類型，那就能打通關(guān)系型和非關(guān)系型數(shù)據(jù)的存儲之間的界限，為業(yè)務(wù)提供更好的架構(gòu)選擇。
當(dāng)然，很多同學(xué)在用 JSON 數(shù)據(jù)類型時(shí)會(huì)遇到各種各樣的問題，其中最容易犯的誤區(qū)就是將類型 JSON 簡單理解成字符串類型。但當(dāng)你看完這篇文章后，會(huì)真正認(rèn)識到 JSON 數(shù)據(jù)類型的威力，從而在實(shí)際工作中更好地存儲非結(jié)構(gòu)化的數(shù)據(jù)。
JSON 數(shù)據(jù)類型
{
"Image": {
"Width": 800,
"Height": 600,
"Title": "View from 15th Floor",
"Thumbnail": {
"Url": "http://www.example.com/image/481989943",
"Height": 125,
"Width": 100
},
"IDs": [116, 943, 234, 38793]
}
}
從中你可以看到， JSON 類型可以很好地描述數(shù)據(jù)的相關(guān)內(nèi)容，比如這張圖片的寬度、高度、標(biāo)題等（這里使用到的類型有整型、字符串類型）。
JSON對象除了支持字符串、整型、日期類型，JSON 內(nèi)嵌的字段也支持?jǐn)?shù)組類型，如上代碼中的 IDs 字段。
最新面試題整理：https://www.javastack.cn/mst/
另一種 JSON 數(shù)據(jù)類型是數(shù)組類型，如：
[
{
"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 對象。
講到這兒，你已經(jīng)對 JSON 類型的基本概念有所了解了，接下來，我們進(jìn)入實(shí)戰(zhàn)環(huán)節(jié)：如何在業(yè)務(wù)中用好JSON類型？
點(diǎn)擊關(guān)注公眾號，Java干貨及時(shí)送達(dá)
業(yè)務(wù)表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn)
用戶登錄設(shè)計(jì)
在數(shù)據(jù)庫中，JSON 類型比較適合存儲一些修改較少、相對靜態(tài)的數(shù)據(jù)，比如用戶登錄信息的存儲如下：
DROP TABLE IF EXISTS UserLogin;
CREATE TABLE UserLogin (
userId BIGINT NOT NULL,
loginInfo JSON,
PRIMARY KEY(userId)
);
由于當(dāng)前業(yè)務(wù)的登錄方式越來越多樣化，如同一賬戶支持手機(jī)、微信、QQ 賬號登錄，所以這里可以用 JSON 類型存儲登錄的信息。
最新面試題整理好了，大家可以在Java面試庫小程序在線刷題。
接著，插入下面的數(shù)據(jù)：
SET @a = '
{
"cellphone" : "13918888888",
"wxchat" : "破產(chǎn)碼農(nóng)",
"QQ" : "82946772"
}
';
INSERT INTO UserLogin VALUES (1,@a);
SET @b = '
{
"cellphone" : "15026888888"
}
';
INSERT INTO UserLogin VALUES (2,@b);
從上面的例子中可以看到，用戶 1 登錄有三種方式：手機(jī)驗(yàn)證碼登錄、微信登錄、QQ 登錄，而用戶 2 只有手機(jī)驗(yàn)證碼登錄。
而如果不采用 JSON 數(shù)據(jù)類型，就要用下面的方式建表：
SELECT
userId,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;
+--------+-------------+--------------+
| userId | cellphone | wxchat |
+--------+-------------+--------------+
| 1 | 13918888888 | 破產(chǎn)碼農(nóng) |
| 2 | 15026888888 | NULL |
+--------+-------------+--------------+
2 rows in set (0.01 sec)
當(dāng)然了，每次寫 JSON_EXTRACT、JSON_UNQUOTE 非常麻煩，MySQL 還提供了 ->> 表達(dá)式，和上述 SQL 效果完全一樣：
SELECT
userId,
loginInfo->>"$.cellphone" cellphone,
loginInfo->>"$.wxchat" wxchat
FROM UserLogin;
當(dāng) JSON 數(shù)據(jù)量非常大，用戶希望對 JSON 數(shù)據(jù)進(jìn)行有效檢索時(shí)，可以利用 MySQL 的 函數(shù)索引 功能對 JSON 中的某個(gè)字段進(jìn)行索引。
最新 MySQL 面試題整理好了，大家可以在Java面試庫小程序在線刷題。
比如在上面的用戶登錄示例中，假設(shè)用戶必須綁定唯一手機(jī)號，且希望未來能用手機(jī)號碼進(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í)再通過虛擬列 cellphone 進(jìn)行查詢，就可以看到優(yōu)化器會(huì)使用到新創(chuàng)建的 idx_cellphone 索引：
EXPLAIN SELECT * FROM UserLogin
WHERE cellphone = '13918888888'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: UserLogin
partitions: NULL
type: const
possible_keys: idx_cellphone
key: idx_cellphone
key_len: 1023
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
當(dāng)然，我們可以在一開始創(chuàng)建表的時(shí)候，就完成虛擬列及函數(shù)索引的創(chuàng)建。如下表創(chuàng)建的列 cellphone 對應(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)
);
用戶畫像設(shè)計(jì)
某些業(yè)務(wù)需要做用戶畫像（也就是對用戶打標(biāo)簽），然后根據(jù)用戶的標(biāo)簽，通過數(shù)據(jù)挖掘技術(shù)，進(jìn)行相應(yīng)的產(chǎn)品推薦。這份架構(gòu)師圖譜建議看看，少走彎路。
比如：
在電商行業(yè)中，根據(jù)用戶的穿搭喜好，推薦相應(yīng)的商品； 在音樂行業(yè)中，根據(jù)用戶喜歡的音樂風(fēng)格和常聽的歌手，推薦相應(yīng)的歌曲； 在金融行業(yè)，根據(jù)用戶的風(fēng)險(xiǎn)喜好和投資經(jīng)驗(yàn)，推薦相應(yīng)的理財(cái)產(chǎn)品。
在這，我強(qiáng)烈推薦你用 JSON 類型在數(shù)據(jù)庫中存儲用戶畫像信息，并結(jié)合 JSON 數(shù)組類型和多值索引的特點(diǎn)進(jìn)行高效查詢。假設(shè)有張畫像定義表：
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 | 愛運(yùn)動(dòng) |
| 6 | 高學(xué)歷 |
| 7 | 小資 |
| 8 | 有房 |
| 9 | 有車 |
| 10 | ?？措娪?nbsp; |
| 11 | 愛網(wǎng)購 |
| 12 | 愛外賣 |
+-------+--------------+
可以看到，表 Tags 是一張畫像定義表，用于描述當(dāng)前定義有多少個(gè)標(biāo)簽，接著給每個(gè)用戶打標(biāo)簽，比如用戶 David，他的標(biāo)簽是 80 后、高學(xué)歷、小資、有房、?？措娪埃挥脩?Tom，90 后、?？措娪?、愛外賣。
點(diǎn)擊關(guān)注公眾號，Java干貨及時(shí)送達(dá)
若不用 JSON 數(shù)據(jù)類型進(jìn)行標(biāo)簽存儲，通常會(huì)將用戶標(biāo)簽通過字符串，加上分割符的方式，在一個(gè)字段中存取用戶所有的標(biāo)簽：
+-------+---------------------------------------+
|用戶 |標(biāo)簽 |
+-------+---------------------------------------+
|David |80后 ； 高學(xué)歷 ； 小資 ； 有房 ；常看電影 |
|Tom |90后 ；?？措娪?nbsp;； 愛外賣 |
+-------+---------------------------------------
這樣做的缺點(diǎn)是：不好搜索特定畫像的用戶，另外分隔符也是一種自我約定，在數(shù)據(jù)庫中其實(shí)可以任意存儲其他數(shù)據(jù)，最終產(chǎn)生臟數(shù)據(jù)。
用 JSON 數(shù)據(jù)類型就能很好解決這個(gè)問題：
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 存儲的標(biāo)簽就是表 Tags 已定義的那些標(biāo)簽值，只是使用 JSON 數(shù)組類型進(jìn)行存儲。
另外，MySQL 系列面試題和答案全部整理好了，微信搜索Java技術(shù)棧，在后臺發(fā)送：面試，可以在線閱讀。
MySQL 8.0.17 版本開始支持 Multi-Valued Indexes，用于在 JSON 數(shù)組上創(chuàng)建索引，并通過函數(shù) member of、json_contains、json_overlaps 來快速檢索索引數(shù)據(jù)。所以你可以在表 UserTag 上創(chuàng)建 Multi-Valued Indexes：
ALTER TABLE UserTag
ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));
如果想要查詢用戶畫像為?？措娪暗挠脩?，可以使用函數(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: ref
possible_keys: idx_user_tags
key: idx_user_tags
key_len: 9
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 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)
如果想要查詢畫像為 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: range
possible_keys: idx_user_tags
key: idx_user_tags
key_len: 9
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where
1 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)
如果想要查詢畫像為 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: range
possible_keys: idx_user_tags
key: idx_user_tags
key_len: 9
ref: NULL
rows: 4
filtered: 100.00
Extra: Using where
1 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)
總結(jié)
JSON 類型是 MySQL 5.7 版本新增的數(shù)據(jù)類型，用好 JSON 數(shù)據(jù)類型可以有效解決很多業(yè)務(wù)中實(shí)際問題。
最新面試題整理：https://www.javastack.cn/mst/
最后，我總結(jié)下今天的重點(diǎn)內(nèi)容：
使用 JSON 數(shù)據(jù)類型，推薦用 MySQL 8.0.17 以上的版本，性能更好，同時(shí)也支持 Multi-Valued Indexes； JSON 數(shù)據(jù)類型的好處是無須預(yù)先定義列，數(shù)據(jù)本身就具有很好的描述性； 不要將有明顯關(guān)系型的數(shù)據(jù)用 JSON 存儲，如用戶余額、用戶姓名、用戶身份證等，這些都是每個(gè)用戶必須包含的數(shù)據(jù)； JSON 數(shù)據(jù)類型推薦使用在不經(jīng)常更新的靜態(tài)數(shù)據(jù)存儲。
本文鏈接：https://blog.csdn.net/java_pfx/article/details/116594654
關(guān)注Java技術(shù)?？锤喔韶?/strong>