Mysql怎麼儲存json格式資料詳解

2022-06-02 14:06:37

前言

Mysql5.7版本以後新增的功能，Mysql提供了一個原生的Json型別，Json值將不再以字串的形式儲存，而是採用一種允許快速讀取文字元素（document elements）的內部二進位制（internal binary）格式。在Json列插入或者更新的時候將會自動驗證Json文字，未通過驗證的文字將產生一個錯誤資訊

JSON 資料型別推薦使用在不經常更新的靜態資料儲存

建立表 t_user

CREATE TABLE `t_user_tag` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '標籤名稱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

登入方式欄位使用json格式，分為phone,wechat,qq,email,zhifubao等等

插入資料：

insert into t_user values (1,'tom', 25, '{"email": "1324@qq.com", "phone": "13200001111", "wechat": "147258369"}');
insert into t_user values (2,'jack', 30, '{"phone": "13500001111"}');
insert into t_user values (3,'lily', 18, '{"qq": "147258369", "phone": "13600001111"}');
insert into t_user values (4,'lily', 45, '{"wechat":"1884875663"}');

查詢

使用者名稱，手機號，微訊號

select name,
(JSON_EXTRACT(login_info, '$.phone')) phone,
JSON_UNQUOTE(JSON_EXTRACT(login_info, '$.wechat')) wechat
from t_user;

可以看出

JSON_UNQUOTE 函數作用是去除json字串的引號，將值轉成string型別

JSON_EXTRACT 函數作用是提取json值

簡潔的寫法作用等同於上面的

select name,
login_info ->> '$.phone' phone,
login_info ->> '$.wechat' wechat
from t_user;

->> 表示式 等同於 JSON_UNQUOTE(JSON_EXTRACT(login_info, '$.wechat'))

-- 使用json中的欄位作為查詢條件
select name,
login_info ->> '$.phone' phone,
login_info ->> '$.wechat' wechat
from t_user
where login_info ->> '$.phone' = '13200001111';

json資料增加索引

給login_info欄位中的手機號增加索引

-- 給login_info這個json中的phone增加索引
alter table t_user add COLUMN phone varchar(11) as (login_info ->> '$.phone');
alter table t_user add UNIQUE INDEX idx_uq_phone(phone);

上述 SQL 首先建立了一個虛擬列 phone，這個列是由函數 login_info->>"$.phone" 計算得到的。然後在這個虛擬列上建立一個唯一索引 idx_uq_phone。這時再通過虛擬列 phone進行查詢，就可以看到優化器會使用到新建立的 idx_uq_phone 索引

-- 檢視索引
EXPLAIN
select *
from t_user
where phone = '13200001111';

我們檢視表結構，發現索引增加上去了

使用場景

某些業務需要做人物誌（也就是對使用者打標籤），然後根據使用者的標籤，通過資料探勘技術，進行相應的產品推薦。比如：

在電商行業中，根據使用者的穿搭喜好，推薦相應的商品；

在音樂行業中，根據使用者喜歡的音樂風格和常聽的歌手，推薦相應的歌曲；

在金融行業，根據使用者的風險喜好和投資經驗，推薦相應的理財產品。

在這，我強烈推薦你用 JSON 型別在資料庫中儲存人物誌資訊，並結合 JSON 陣列型別和多值索引的特點進行高效查詢。

建立人物誌定義表：

CREATE TABLE `t_tag` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '標籤名稱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

insert into t_tag values (null, '70後');
insert into t_tag values (null, '80後');
insert into t_tag values (null, '90後');
insert into t_tag values (null, '00後');
insert into t_tag values (null, '10後');
insert into t_tag values (null, '愛運動');
insert into t_tag values (null, '愛聽歌');
insert into t_tag values (null, '愛看電影');
insert into t_tag values (null, '高學歷');
insert into t_tag values (null, '小資');
insert into t_tag values (null, '有車');
insert into t_tag values (null, '有小孩');
insert into t_tag values (null, '喜歡網購');
insert into t_tag values (null, '喜歡點外賣');
insert into t_tag values (null, '蘿莉');

建立使用者標籤中間表

CREATE TABLE `t_user_tag` (
  `user_id` int NOT NULL COMMENT '使用者id',
  `tag_id` json NOT NULL COMMENT '使用者標籤id',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

插入資料，使用陣列的形式儲存

insert into t_user_tag values (1,'[2,4,6]');
insert into t_user_tag values (2,'[1,3,7]');
insert into t_user_tag values (3,'[8,10,12]');

MySQL 8.0.17 版本開始支援 Multi-Valued Indexes，用於在 JSON 陣列上建立索引，並通過函數 member of、json_contains、json_overlaps 來快速檢索索引資料。所以你可以在表 UserTag 上建立 Multi-Valued Indexes：

ALTER TABLE t_user_tag
ADD INDEX idx_user_tags ((cast((tag_id->"$") as unsigned array)));

查詢愛看電影的

select * from t_user_tag
where 8 MEMBER OF(tag_id -> '$');

查詢愛看電影，且有小孩的

select * from t_user_tag
where JSON_CONTAINS(tag_id -> '$', '[8,10]');

使用 JSON 資料型別，推薦用 MySQL 8.0.17 以上的版本，效能更好，同時也支援 Multi-Valued Indexes；

JSON 資料型別的好處是無須預先定義列，資料本身就具有很好的描述性；

不要將有明顯關係型的資料用 JSON 儲存，如使用者餘額、使用者姓名、使用者身份證等，這些都是每個使用者必須包含的資料；

JSON 資料型別推薦使用在不經常更新的靜態資料儲存。