關系型的結構化存盤存在一定的弊端，因為它需要預先定義好所有的列以及列對應的型別，但是業務在發展程序中，或許需要擴展單個列的描述功能，這時，如果能用好 JSON 資料型別，那就能打通關系型和非關系型資料的存盤之間的界限，為業務提供更好的架構選擇，
當然，很多同學在用 JSON 資料型別時會遇到各種各樣的問題，其中最容易犯的誤區就是將型別 JSON 簡單理解成字串型別，但當你看完這篇文章后，會真正認識到 JSON 資料型別的威力，從而在實際作業中更好地存盤非結構化的資料，
JSON 資料型別
JSON（JavaScript Object Notation）主要用于互聯網應用服務之間的資料交換，MySQL 支持RFC 7159定義的 JSON 規范，主要有 JSON 物件 和 JSON 陣列 兩種型別，下面就是 JSON 物件，主要用來存盤圖片的相關資訊：
{
"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 型別可以很好地描述資料的相關內容，比如這張圖片的寬度、高度、標題等（這里使用到的型別有整型、字串型別），
JSON物件除了支持字串、整型、日期型別，JSON 內嵌的欄位也支持陣列型別，如上代碼中的 IDs 欄位，
另一種 JSON 資料型別是陣列型別，如：
[
{
"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"
}
]
上面的示例演示的是一個 JSON 陣列，其中有 2 個 JSON 物件，
到目前為止，可能很多同學會把 JSON 當作一個很大的欄位串型別，從表面上來看，沒有錯，但本質上，JSON 是一種新的型別，有自己的存盤格式，還能在每個對應的欄位上創建索引，做特定的優化，這是傳統欄位串無法實作的，JSON 型別的另一個好處是無須預定義欄位，欄位可以無限擴展，而傳統關系型資料庫的列都需預先定義，想要擴展需要執行 ALTER TABLE ... ADD COLUMN ... 這樣比較重的操作，
需要注意是，JSON 型別是從 MySQL 5.7 版本開始支持的功能，而 8.0 版本解決了更新 JSON 的日志性能瓶頸，如果要在生產環境中使用 JSON 資料型別，強烈推薦使用 MySQL 8.0 版本，
講到這兒，你已經對 JSON 型別的基本概念有所了解了，接下來，我們進入實戰環節：如何在業務中用好JSON型別？
業務表結構設計實戰
用戶登錄設計
在資料庫中，JSON 型別比較適合存盤一些修改較少、相對靜態的資料，比如用戶登錄資訊的存盤如下：
DROP TABLE IF EXISTS UserLogin;
CREATE TABLE UserLogin (
userId BIGINT NOT NULL,
loginInfo JSON,
PRIMARY KEY(userId)
);
由于當前業務的登錄方式越來越多樣化，如同一賬戶支持手機、微信、QQ 賬號登錄，所以這里可以用 JSON 型別存盤登錄的資訊，
接著，插入下面的資料：
SET @a = '
{
"cellphone" : "13918888888",
"wxchat" : "破產碼農",
"QQ" : "82946772"
}
';
INSERT INTO UserLogin VALUES (1,@a);
SET @b = '
{
"cellphone" : "15026888888"
}
';
INSERT INTO UserLogin VALUES (2,@b);
從上面的例子中可以看到，用戶 1 登錄有三種方式：手機驗證碼登錄、微信登錄、QQ 登錄，而用戶 2 只有手機驗證碼登錄，
而如果不采用 JSON 資料型別，就要用下面的方式建表：
SELECT
userId,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;
+--------+-------------+--------------+
| userId | cellphone | wxchat |
+--------+-------------+--------------+
| 1 | 13918888888 | 破產碼農 |
| 2 | 15026888888 | NULL |
+--------+-------------+--------------+
2 rows in set (0.01 sec)
當然了，每次寫 JSON_EXTRACT、JSON_UNQUOTE 非常麻煩，MySQL 還提供了 ->> 運算式，和上述 SQL 效果完全一樣：
SELECT
userId,
loginInfo->>"$.cellphone" cellphone,
loginInfo->>"$.wxchat" wxchat
FROM UserLogin;
當 JSON 資料量非常大，用戶希望對 JSON 資料進行有效檢索時，可以利用 MySQL 的 函式索引 功能對 JSON 中的某個欄位進行索引，
比如在上面的用戶登錄示例中，假設用戶必須系結唯一手機號，且希望未來能用手機號碼進行用戶檢索時，可以創建下面的索引：
ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");
ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);
上述 SQL 首先創建了一個虛擬列 cellphone，這個列是由函式 loginInfo->>"$.cellphone" 計算得到的，然后在這個虛擬列上創建一個唯一索引 idx_cellphone，這時再通過虛擬列 cellphone 進行查詢，就可以看到優化器會使用到新創建的 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)
當然，我們可以在一開始創建表的時候，就完成虛擬列及函式索引的創建，如下表創建的列 cellphone 對應的就是 JSON 中的內容，是個虛擬列；uk_idx_cellphone 就是在虛擬列 cellphone 上所創建的索引，
CREATE TABLE UserLogin (
userId BIGINT,
loginInfo JSON,
cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
PRIMARY KEY(userId),
UNIQUE KEY uk_idx_cellphone(cellphone)
);
用戶畫像設計
某些業務需要做用戶畫像（也就是對用戶打標簽），然后根據用戶的標簽，通過資料挖掘技術，進行相應的產品推薦，比如：
- 在電商行業中，根據用戶的穿搭喜好，推薦相應的商品；
- 在音樂行業中，根據用戶喜歡的音樂風格和常聽的歌手，推薦相應的歌曲；
- 在金融行業，根據用戶的風險喜好和投資經驗，推薦相應的理財產品，
在這，我強烈推薦你用 JSON 型別在資料庫中存盤用戶畫像資訊，并結合 JSON 陣列型別和多值索引的特點進行高效查詢，假設有張畫像定義表：
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 | 愛運動 |
| 6 | 高學歷 |
| 7 | 小資 |
| 8 | 有房 |
| 9 | 有車 |
| 10 | 常看電影 |
| 11 | 愛網購 |
| 12 | 愛外賣 |
+-------+--------------+
可以看到，表 Tags 是一張畫像定義表，用于描述當前定義有多少個標簽，接著給每個用戶打標簽，比如用戶 David，他的標簽是 80 后、高學歷、小資、有房、常看電影；用戶 Tom，90 后、常看電影、愛外賣，
若不用 JSON 資料型別進行標簽存盤，通常會將用戶標簽通過字串，加上分割符的方式，在一個欄位中存取用戶所有的標簽：
+-------+---------------------------------------+
|用戶 |標簽 |
+-------+---------------------------------------+
|David |80后 ； 高學歷 ； 小資 ； 有房 ；常看電影 |
|Tom |90后 ；常看電影 ； 愛外賣 |
+-------+---------------------------------------
這樣做的缺點是：不好搜索特定畫像的用戶，另外分隔符也是一種自我約定，在資料庫中其實可以任意存盤其他資料，最終產生臟資料，
用 JSON 資料型別就能很好解決這個問題：
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 存盤的標簽就是表 Tags 已定義的那些標簽值，只是使用 JSON 陣列型別進行存盤，
MySQL 8.0.17 版本開始支持 Multi-Valued Indexes，用于在 JSON 陣列上創建索引，并通過函式 member of、json_contains、json_overlaps 來快速檢索索引資料，所以你可以在表 UserTag 上創建 Multi-Valued Indexes：
ALTER TABLE UserTag
ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));
如果想要查詢用戶畫像為常看電影的用戶，可以使用函式 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 后，且常看電影的用戶，可以使用函式 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 后，且常看電影的用戶，則可以使用函式 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)
總結
JSON 型別是 MySQL 5.7 版本新增的資料型別，用好 JSON 資料型別可以有效解決很多業務中實際問題，最后，我總結下今天的重點內容：
- 使用 JSON 資料型別，推薦用 MySQL 8.0.17 以上的版本，性能更好，同時也支持 Multi-Valued Indexes；
- JSON 資料型別的好處是無須預先定義列，資料本身就具有很好的描述性；
- 不要將有明顯關系型的資料用 JSON 存盤，如用戶余額、用戶姓名、用戶身份證等，這些都是每個用戶必須包含的資料；
- JSON 資料型別推薦使用在不經常更新的靜態資料存盤，
著作權宣告：本文為博主原創文章，遵循 CC 4.0 BY-SA 著作權協議，轉載請附上原文出處鏈接和本宣告，
本文鏈接：https://blog.csdn.net/java_pfx/article/details/116594654
近期熱文推薦：
1.1,000+ 道 Java面試題及答案整理(2022最新版)
2.勁爆！Java 協程要來了，，，
3.Spring Boot 2.x 教程，太全了！
4.別再寫滿屏的爆爆爆炸類了，試試裝飾器模式，這才是優雅的方式！！
5.《Java開發手冊（嵩山版）》最新發布，速速下載！
覺得不錯，別忘了隨手點贊+轉發哦！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/448062.html
標籤：Java