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

目录
  • 前言
  • JSON 数据类型推荐使用在不经常更新的静态数据存储
  • 查询
  • json数据 增加索引
  • 使用场景
  • 总结

前言

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格式内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • python将类似json的数据存储到MySQL中的实例

    由于之前对于爬取下来的数据都是存入MongoDB中,想起来还没有尝试存入MySQL,于是将一篇简单的文章爬取下来,存入MySQL试试 这里用到的python模块是pymysql,因为MySQLdb之前已经停止维护 首先在cmd中连接MySQL并且创建一个数据库json 在图形化界面workbench中可以看到 接下来就要在pycharm中写代码了,在pycharm中导入pymysql后即可 #建立python与MySQL之间的连接 mysql = pymysql.connect(host="lo

  • Mysql 直接查询存储的Json字符串中的数据

    我们平时使用mysql,出于项目需求,可能需要直接将Java对象或者一个大json,直接存到表中的某个字段中:使用的时候再查出来,反序列化到对象或者一个Map中,方便我们操作: 大多时候,我们可能并不需要所有的数据,只想使用这个对象或者json中的某一个值,来做逻辑判断而已 那我们可以这样做,例如:原来我们需要查出某个字段的数据,然后反序列化成对象再调用其中的一个属性 SELECT content FROM table_name WHERE id = 32; 查询结果:列名:content结果:

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

    目录 前言 JSON 数据类型推荐使用在不经常更新的静态数据存储 查询 json数据 增加索引 使用场景 总结 前言 Mysql5.7版本以后新增的功能,Mysql提供了一个原生的Json类型,Json值将不再以字符串的形式存储,而是采用一种允许快速读取文本元素(document elements)的内部二进制(internal binary)格式.在Json列插入或者更新的时候将会自动验证Json文本,未通过验证的文本将产生一个错误信息 JSON 数据类型推荐使用在不经常更新的静态数据存储 创

  • MySQL操作之JSON数据类型操作详解

    上一篇文章我们介绍了mysql数据存储过程参数实例详解,今天我们看看MySQL操作之JSON数据类型的相关内容. 概述 mysql自5.7.8版本开始,就支持了json结构的数据存储和查询,这表明了mysql也在不断的学习和增加nosql数据库的有点.但mysql毕竟是关系型数据库,在处理json这种非结构化的数据时,还是比较别扭的. 创建一个JSON字段的表 首先先创建一个表,这个表包含一个json格式的字段: CREATE TABLE table_name ( id INT NOT NULL

  • MySQL数据库使用mysqldump导出数据详解

    mysqldump是mysql用于转存储数据库的客户端程序.它主要产生一系列的SQL语句,可以封装到文件,该文件包含有所有重建您的数据库所需要的 SQL命令如CREATE DATABASE,CREATE TABLE,INSERT等等.可以用来实现轻量级的快速迁移或恢复数据库.是mysql数据库实现逻辑备份的一种方式. 在日常维护工作当中经常会需要对数据进行导出操作,而mysqldump是导出数据过程中使用非常频繁的一个工具:它自带的功能参数非常多,文章中会列举出一些常用的操作,在文章末尾会将所有

  • 对python requests发送json格式数据的实例详解

    requests是常用的请求库,不管是写爬虫脚本,还是测试接口返回数据等.都是很简单常用的工具. 这里就记录一下如何用requests发送json格式的数据,因为一般我们post参数,都是直接post,没管post的数据的类型,它默认有一个类型的,貌似是 application/x-www-form-urlencoded. 但是,我们写程序的时候,最常用的接口post数据的格式是json格式.当我们需要post json格式数据的时候,怎么办呢,只需要添加修改两处小地方即可. 详见如下代码: i

  • 对numpy中二进制格式的数据存储与读取方法详解

    使用save可以实现对numpy数据的磁盘存储,存储的方式是二进制.查看使用说明,说明专门提到了是未经压缩的二进制形式.存储后的数据可以进行加载或者读取,通过使用load方法. In [81]:np.save('demo',data1) 通过以上操作,数据data1被存储到了demo文件中,numpy会自动加上npy的文件后缀名. In [82]: a =np.load('demo.npy') In [83]: a Out[83]: array([0,1, 2, 3, 4, 5, 6, 7, 8

  • MySQL中查询json格式的字段实例详解

    工作开发过程遇到一个需求:需要动态存储客户的姓名.手机号码.身份证.证件类型,意思是可能前端会传一个人或二个人或者三个人的信息是动态的不固定人数的四个字段(姓名.手机号码.身份证.证件类型). 前端页面如下: 我是使用List来接收前端传过来 json,mysql 用 varchar 来保存这个 json 数组 [{ "cardId": "110101199003072316", "cstName": "张双儿1", &quo

  • 实例详解JSON数据格式及json格式数据域字符串相互转换

    JSON(JavaScript Object Notation)是一种轻量级的数据交换格式.JSON采用完全独立于语言的文本格式,这些特性使JSON成为理想的数据交换语言.易于人阅读和编写,同时也易于机器解析和生成. 基础结构 JSON建构于两种结构: 1. "名称/值"对的集合(A collection of name/value pairs).不同的语言中,它被理解为对象(object),记录(record),结构(struct),字典(dictionary),哈希表(hash t

  • javascript解析ajax返回的xml和json格式数据实例详解

    本文实例讲述了javascript解析ajax返回的xml和json格式数据.分享给大家供大家参考,具体如下: 写个例子,以备后用 一.JavaScript 解析返回的xml格式的数据: 1.javascript版本的ajax发送请求 (1).创建XMLHttpRequest对象,这个对象就是ajax请求的核心,是ajax请求和响应的信息载体,单是不同浏览器创建方式不同 (2).请求路径 (3).使用open方法绑定发送请求 (4).使用send() 方法发送请求 (5).获取服务器返回的字符串

  • PHP JSON格式数据交互实例代码详解

    在PHP中解析JSON主要用到json_encode和json_decode两个PHP JSON函数,比PHP解析XML方便很多,下面详细介绍下PHP JSON的使用.JSON基础介绍 JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式. JSON主要有两种结构: "名称/值"对的集合,在PHP中可以理解为关联数组 (associative array). 值的有序列表(An ordered list of values).在PHP中可以理解为

  • Python中json格式数据的编码与解码方法详解

    本文实例讲述了Python中json格式数据的编码与解码方法.分享给大家供大家参考,具体如下: python从2.6版本开始内置了json数据格式的处理方法. 1.json格式数据编码 在python中,json数据格式编码使用json.dumps方法. #!/usr/bin/env python #coding=utf8 import json users = [{'name': 'tom', 'age': 22}, {'name': 'anny', 'age': 18}] #元组对象也可以

随机推荐