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

吾爱主题阅读：173 2024-04-01 23:26:00 评论：0

前言

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