广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >Mysql怎么存储json格式数据详解
  • 474

0
分享到

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

2024-04-02 19:04:59 474人浏览 泡泡鱼
摘要

目录前言JSON 数据类型推荐使用在不经常更新的静态数据存储查询json数据 增加索引使用场景总结 前言 Mysql5.7版本以后新增的功能，mysql提供了一个原生的Js

前言

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

总结 

到此这篇关于Mysql怎么存储json格式数据的文章就介绍到这了,更多相关Mysql存储json格式内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: Mysql怎么存储json格式数据详解

本文链接: https://www.lsjlt.com/news/150454.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • Mysql怎么存储json格式数据详解
    目录前言JSON 数据类型推荐使用在不经常更新的静态数据存储查询json数据 增加索引使用场景总结 前言 Mysql5.7版本以后新增的功能，Mysql提供了一个原生的Js...
    99+
    2022-11-13
  • Mysql存储json格式数据需要掌握的
    目录 一、前言二、什么是 JSON三、Mysql当中json函数四、JSON值部分更新4.1.使用 Partial Updates 的条件4.2.如何在 binlog 中开启 Partial ...
    99+
    2023-10-11
    json mysql 数据库
  • mysql怎么存json格式
    mysql中存储json格式数据的方法具体方法如下：#新建表 create table json_user ( uid int auto_increment, ...
    99+
    2022-10-24
  • MYSQL中解析json格式数据
    MYSQL中解析json格式数据 1、判断是否是json格式数据 JSON_VALID(requestbody) 注：requestbody为指定字段 2、解析json中的指定字段（可多层嵌套） JSON_EXTRACT(requestbo...
    99+
    2023-08-19
    json mysql java 数据库
  • Android中怎么解析JSON格式数据
    这期内容当中小编将会给大家带来有关Android中怎么解析JSON格式数据，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。JSON格式 : { "name_A"&nb...
    99+
    2023-05-30
    android json
  • 详解php怎么将json数据转为字符串格式
    PHP是一种非常流行的服务器端编程语言，它可以用来开发各种不同类型的Web应用程序。其中，JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，它可以被用来在不同的应用程序之间传递和存储数据。在PHP中...
    99+
    2023-05-14
  • Ajax与用户交互的JSON数据存储格式是什么
    本篇内容介绍了“Ajax与用户交互的JSON数据存储格式是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成...
    99+
    2022-10-19
  • MYSQL中解析json格式数据方法示例
    目录1、判断是否是json格式数据2、解析json中的指定字段（可多层嵌套）3、计算json中指定数组的长度4、完整示例总结 1、判断是否是json格式数据 JSON_VALID(requestbody) 注：...
    99+
    2023-08-19
    MySQL json mysql解析json字符串 mysql处理json
  • redis储存数据格式是什么
    Redis支持多种不同的数据格式来存储数据，每种格式都有其特定的用途和优势。1. 字符串（String）：最简单的数据结构，可以存储...
    99+
    2023-08-24
    redis
  • mysql处理json格式的字段，一文搞懂mysql解析json数据
    文章目录 一、概述1、什么是JSON2、MySQL的JSON3、varchar、text、json类型字段的区别 二、JSON类型的创建1、建表指定2、修改字段 三、JSON类型的插入...
    99+
    2023-10-01
    mysql json adb
  • SpringBoot怎么返回Json数据格式
    这篇文章主要介绍“SpringBoot怎么返回Json数据格式”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“SpringBoot怎么返回Json数据格式”文章能帮助大家解决问题。一、@RestCon...
    99+
    2023-07-05
  • Delphi怎么处理JSON格式数据
    本篇内容主要讲解“Delphi怎么处理JSON格式数据”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Delphi怎么处理JSON格式数据”吧! ...
    99+
    2022-10-19
  • Python怎么读写JSON格式数据
    今天小编给大家分享一下Python怎么读写JSON格式数据的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。JSON格式数据简介...
    99+
    2023-07-05
  • python怎么读取和存储dict()与.json格式文件
    本文小编为大家详细介绍“python怎么读取和存储dict()与.json格式文件”，内容详细，步骤清晰，细节处理妥当，希望这篇“python怎么读取和存储dict()与.json格式文件”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入...
    99+
    2023-07-02
  • MySQL怎么存储无模式的数据
    MySQL是关系型数据库管理系统，不支持直接存储无模式的数据。无模式的数据通常指的是非结构化或半结构化数据，例如文本文件、图像、音频...
    99+
    2023-08-23
    MySQL
  • Ruby怎么解析处理YAML和json格式数据
    今天小编给大家分享一下Ruby怎么解析处理YAML和json格式数据的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。Ruby处...
    99+
    2023-06-30
  • table表数据怎么转为Json格式
    小编给大家分享一下table表数据怎么转为Json格式，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 　　<table&...
    99+
    2022-10-19
  • Python中json格式数据的编码与解码方法详解
    本文实例讲述了Python中json格式数据的编码与解码方法。分享给大家供大家参考，具体如下： python从2.6版本开始内置了json数据格式的处理方法。 1、json格式数据编码 在python中，j...
    99+
    2022-06-04
    详解 格式 方法
  • php中怎么将数据转为json格式
    这篇文章主要讲解了“php中怎么将数据转为json格式”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“php中怎么将数据转为json格式”吧！一、json格式简介json全称为JavaScri...
    99+
    2023-07-05
  • vue怎么实现Json格式数据展示
    本文小编为大家详细介绍“vue怎么实现Json格式数据展示”，内容详细，步骤清晰，细节处理妥当，希望这篇“vue怎么实现Json格式数据展示”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。Json格式数据展示vue...
    99+
    2023-06-29
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作