亲宝软件园·资讯

展开

Mysql存储json格式

lockie_zou 人气:2

前言

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 存储,如用户余额、用户姓名、用户SFZ等,这些都是每个用户必须包含的数据;

JSON 数据类型推荐使用在不经常更新的静态数据存储。

总结 

加载全部内容

相关教程
猜你喜欢
用户评论