MySQL + JSON = 王炸！！
關系型的結構化存儲存在一定的弊端，因為它需要預先定義好所有的列以及列對應的類型。但是業務在發展過程中，或許需要擴展單個列的描述功能，這時，如果能用好 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 系列面試題和答案全部整理好了，微信搜索Java技術棧，在后臺發送：面試，可以在線閱讀。
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 數據類型推薦使用在不經常更新的靜態數據存儲。
來源：https://blog.csdn.net/java_pfx/article/details/116594654
