Mysql怎么存储json格式数据详解

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

前言

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 数据类型推荐使用在不经常更新的静态数据存储。