Mysql 5.7 新特性之 json 类型的增删改查操作和用法

目录
  • 插入数据 :insert操作
  • 更新记录:update 追加内容:
  • 更新记录:追加内容到数组:
  • 更新记录:update 修改json属性:
  • 删除记录:
  • 查询记录:
  • 扩展:
  • 一、插入一条数据:
    • 1、查找 JSON 中的某个字段:
    • 2、根据JSON中的某个字段查询表中记录:
  • 二、修改 JSON
    • 1、修改 JSON 中的某个字段:
    • 2、往 JSON 中插入一个新的字段:

近几年来,nosql大行其道,json更是火的一塌糊涂,作为数据库的元老,mysql在5.7版本中添加了对json数据的支持。这片博客主要介绍mysql json类型的增删改查等基本操作的用法。

首先创建一个数据库learnjson,并创建一张表json_start:

create table json_start (
	id int auto_increment PRIMARY KEY not null,
	person json not NULL
);

插入数据 :insert操作

在json_start表中我们添加了一个json类型的字段:person;下面我们就来插入一些数据,

json类型的数据插入方式和普通数据插入方式一样,不同的是插入的是json字符串:

INSERT INTO json_start VALUES(null,'{"coder":[{"name":"张三","age":"18","gender":"男"},{"name":"李四","age":"14","gender":"男"},{"name":"王五","age":"15","gender":"女"}],
	"teacher":[{"name":"刘二","age":"22","gender":"女"},{"name":"赵三","age":"23","gender":"男"},{"name":"王五","age":"15","gender":"女"}],
	"student":[{"name":"张三","age":"18","gender":"男"},{"name":"李四","age":"14","gender":"男"},{"name":"王五","age":"15","gender":"女"}]
}');

需要注意的是,在插入json类型的字符串时,mysql会对json字符串进行格式校验,如果格式有误,就无法插入,并且默认情况下,json数据的大小也是有限制的,默认大小和LONGTEXT ,LONGBLOB 一样,同时他还受限于mysql配置项:max_allowed_packet. 的限制(默认为4M).

我们观察数据库的结果:

数据已经添加到数据库中了,我们试着在插入几条数据:

INSERT INTO json_start VALUES(null,'{"handsome":[{"name":"山山","age":"18","gender":"男"},{"name":"张一山","age":"26","gender":"男"},{"name":"bug4j","age":"18","gender":"男"}],
	"beauty":[{"name":"刘亦菲","age":"18","gender":"女"},{"name":"xixi","age":"23","gender":"女"},{"name":"周冬雨","age":"15","gender":"女"}],
	"fatty":[{"name":"zhangqi","age":"38","gender":"男"},{"name":"pangci","age":"30","gender":"男"},{"name":"王五","age":"15","gender":"女"}]
}');
INSERT INTO json_start VALUES(null,
'[{"name":"奔驰","model":"C260","price":"200RMB"},
{"name":"劳斯莱斯","model":"定制版","price":"166RMB"},
{"name":"奥迪","model":"A6","price":"200000000RMB"}]'
);

结果:

你会发现,虽然最后一条记录的json内容和其他两条记录的内容完全不一样甚至结构都不一样,但是依然能插入成功,因为mysql在插入时只对格式进行校验,格式通过了,就当成字符串存储,并不会考虑与其他记录是否保持一致,事实上这么做没必要。但这同时引入了一个使用时需要注意的事项,就是在定义表的时候就必须尽可能的表以及字段的功能,数据结构明确清楚,并且避免一个字段多种数据混合存储。

更新记录:update 追加内容:

mysqljson数据跟新操作根据json数据的类型范围分为列表操作和对象操作两种类型,都可以直接说那个merge操作。

下面我们试着给第一条记录追加一个manager属性,很显然这是一个对象操作,执行语句如下:

UPDATE json_start set person = json_MERGE(person,'{"manager":[{"name":"laoda","age":"38","gender":"男"}]}') where id=1;

原理就是将原来的json对象和要添加的对象合并,稍后将会描述这其中的合并规则。执行结果如下:

再给第三条记录追加一个对象:

UPDATE json_start set person = json_merge(person,'[{"name":"大众","model":"k1","price":"600000000RMB"}]') where id=3;

更新记录:追加内容到数组:

追加到数组可以使用JSON_APPEND()(mysql 5.7.9或更早的版本) 或者JSON_ARRAY_APPEND()方法,该操作将指定的对象添加到数组末尾,例如:

update json_start set person = json_ARRAY_APPEND(person,"$",JSON_OBJECT("name","玛莎拉蒂","model","ah","price","168RMB")) where id=3;

执行结果:

更新记录:update 修改json属性:

mysql提供一个非常灵活的本地方法:json_set(doc,path,value),其中doc为要修改的json列,path为要修改的属性的对象定位表达式,value为新值,例如:

UPDATE json_start set person = json_set(person,'$.coder[0].name','张三三三三三') where id = 1

结果为:

需要注意的是,当使用json_set()方法去修改一整个对象或者数组时,mysql或将value当成字符串来解析保存,当你的value中包含”时,或自动进行转义,这样存储起来很浪费空间,同时也会产生问题,如果需要修改整个对象,建议使用Json_object(key,value,key2,value2....)来格式化你的对象,例如:

UPDATE json_start set person = json_set(person,'$.coder[0]',JSON_OBJECT("age",18,"name","张sisisisi","gender","男")) where id = 1;
UPDATE json_start set person = json_set(person,"$[0]",JSON_OBJECT("age",18,"name","张wuwuwuuwu","gender","男")) where id = 3;

执行结果为:

删除记录:

删除记录使用JSON_REMOVE(doc,path1,path2,...)方法,例如:

update json_start set person = json_REMOVE(person,"$[0]") where id = 3;
update json_start set person = json_REMOVE(person,"$.coder[0]") where id = 1;

执行结果为:

查询记录:

查询所有的属性key,使用json_keys(doc)方法:

select id,json_keys(person) from json_start

结果如下:

查询指定的记录中的某个属性或者值,使用Json_extract(doc,path)方法,例如:

select id,json_extract(person,"$.coder[0].name") from json_start where id = 1

结果如下:

或者:

select id,json_extract(person,"$.coder[0]") from json_start where id = 1

当然,你也可以使用通配符‘*’来实现多值查询,例如:

select id,json_extract(person,"$.coder[*].name") from json_start where id = 1
UNION All
select id,json_extract(person,"$.*[*].name") from json_start where id = 1

结果如下:

需要注意的是,查询返回的结果始终是一个包含查询结果的数组!

扩展:

创建一个表 t1,basic_info 字段为JSON类型:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `basic_info` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

一、插入一条数据:

INSERT INTO `t1`(`id`, `basic_info`) VALUES (1, '{\"age\": 9, \"name\": \"小明\", \"class\": 3}');

1、查找 JSON 中的某个字段:

比如查询 id=1 的 basic_info 中的 name 字段,可以用以下两种方式:

select basic_info->'$.name' from t1 where id =1;
//或者
select JSON_EXTRACT(basic_info, '$.name') from t1 where id =1;

mysql> select basic_info->’$.name’ from t1 where id =1;
+———————————+
| basic_info->’$.name’ |
+———————————+
| “小明” |
+———————————+

或者:

mysql> select JSON_EXTRACT(basic_info, ‘$.name’) from t1 where id =1;
+——————————————————+
| JSON_EXTRACT(basic_info, ‘$.name’) |
+——————————————————+
| “小明” |
+——————————————————+
1 row in set (0.04 sec)

2、根据JSON中的某个字段查询表中记录:

比如查询name为小明的记录:

select * from t1 where basic_info->'$.name' = '小明';
//或者
select * from t1 where JSON_EXTRACT(basic_info,'$.name') = '小明';

检索JSON数据的方法,文档:https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

二、修改 JSON

修改JSON中字段的方法,文档:https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html

1、修改 JSON 中的某个字段:

比如我们修改 id =1 的 basic_info 中的age为 10 岁:

可以使用JSON_REPLACE() 或者 JSON_SET() 函数:

update t1 set basic_info = JSON_REPLACE(basic_info, '$.age', 10) where id =1;
//或者
update t1 set basic_info = JSON_SET(basic_info, '$.age', 10) where id =1;

2、往 JSON 中插入一个新的字段:

比如往 basic_info 中插入一个性别“gender”字段:

可以使用JSON_INSERT() 或者 JSON_SET() 函数:

update t1 set basic_info = JSON_INSERT(basic_info, '$.gender', '男') where id =1;
//或者
update t1 set basic_info = JSON_SET(basic_info, '$.gender', '男') where id =1;

JSON_SET(),JSON_INSERT() 和JSON_REPLACE() 函数的区别:

  • JSON_SET() 支持替换现有值,并且支持添加不存在的值。
  • JSON_INSERT() 插入值而不替换现有值。
  • JSON_REPLACE() 仅替换现有值。

还有一些其他的函数,详见对应官方文档。

到此这篇关于Mysql 5.7 新特性之 json 类型的增删改查的文章就介绍到这了,更多相关Mysql json 类型的增删改查内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL5.7中的JSON基本操作指南

    前言 因为项目需要,存储字段存储成了JSON格式,在项目中是将查询出来的值通过jackson转成相应的bean进行处理的,觉得不够简单方便. MySQL从5.7版本开始就支持JSON格式的数据,操作用起来挺方便的. 建表 在新建表时字段类型可以直接设置为json类型,比如我们创建一张表: mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `info`

  • 简单谈谈MySQL5.7 JSON格式检索

    MySQL5.7版本开始支持JSON格式,在创建表时,可以指定列表的数据类型为JSON,但是如何在JSON格式上创建索引呢?? 本人做了一个简单测试. 第一步:建立一个包含JSON类型的表: CREATE TABLE json_test` ( id` int (8) NOT NULL AUTO_INCREMENT, content` json NOT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 第二步:初始化数据

  • mysql5.7 新增的json字段类型用法实例分析

    本文实例讲述了mysql5.7 新增的json字段类型用法.分享给大家供大家参考,具体如下: 一.我们先创建一个表,准备点数据 CREATE TABLE `json_test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `json` json DEFAULT NULL COMMENT 'json数据', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8m

  • 教你用springboot连接mysql并实现增删改查

    1.数据库与数据表的创建 创建名为mybatis的数据库: create database mybatis; 创建名为user2的数据表: use mybatis; create table user2( id integer not null primary key, name varchar(20) not null, pwd varchar(10) not null, perms varchar(100) null) 生成如下表结构: (已经插入了两行数据的) 2.数据库的连接 注意点:u

  • MySQL5.7 JSON类型使用详解

    JSON是一种轻量级的数据交换格式,采用了独立于语言的文本格式,类似XML,但是比XML简单,易读并且易编写.对机器来说易于解析和生成,并且会减少网络带宽的传输. JSON的格式非常简单:名称/键值.之前MySQL版本里面要实现这样的存储,要么用VARCHAR要么用TEXT大文本. MySQL5.7发布后,专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析. 我们先看看MySQL老版本的JSON存取. 示例表结构: CREATE TABLE json_test( id INT, p

  • Mysql5.7中JSON操作函数使用说明

    前言 JSON是一种轻量级的数据交换格式,采用了独立于语言的文本格式,类似XML,但是比XML简单,易读并且易编写.对机器来说易于解析和生成,并且会减少网络带宽的传输. JSON的格式非常简单: 名称/键值 .之前MySQL版本里面要实现这样的存储,要么用VARCHAR要么用TEXT大文本. MySQL5.7发布后,专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析. 下面一起来实际操作一下. 创建带有 JSON 字段的表 比如一个'文章'表,字段包括 id.标题 title.标签

  • Python中tkinter+MySQL实现增删改查

    一.设置主窗口 # -*- coding: utf-8 -*- import tkinter from tkinter import ttk import pymysql # 导入消息对话框子模块 import tkinter.messagebox # 创建主窗口 root = tkinter.Tk() root.title('告警查询') # 设置窗口大小 root.minsize(500,500) tabControl = ttk.Notebook(root) tab1 = ttk.Fram

  • Mysql 5.7 新特性之 json 类型的增删改查操作和用法

    目录 插入数据 :insert操作 更新记录:update 追加内容: 更新记录:追加内容到数组: 更新记录:update 修改json属性: 删除记录: 查询记录: 扩展: 一.插入一条数据: 1.查找 JSON 中的某个字段: 2.根据JSON中的某个字段查询表中记录: 二.修改 JSON 1.修改 JSON 中的某个字段: 2.往 JSON 中插入一个新的字段: 近几年来,nosql大行其道,json更是火的一塌糊涂,作为数据库的元老,mysql在5.7版本中添加了对json数据的支持.这

  • mysql视图之管理视图实例详解【增删改查操作】

    本文实例讲述了mysql视图之管理视图操作.分享给大家供大家参考,具体如下: mysql提供了用于显示视图定义的SHOW CREATE VIEW语句,我们来看下语法结构: SHOW CREATE VIEW [database_name].[view_ name]; 要显示视图的定义,需要在SHOW CREATE VIEW子句之后指定视图的名称,我们先来根据employees表创建一个简单的视图用来显示公司组织结构,完事在进行演示: CREATE VIEW organization AS SELE

  • MySQL筑基篇之增删改查操作详解

    目录 一.增加表中数据 1.无自增列时 2.有自增列时 二.删除表中数据 1.使用delete 2.使用truncate 三.修改表中数据 四.*查询操作 1.简单查询 2.条件查询 3.排序 一.增加表中数据 1.无自增列时 1.指定字段添加数据 给表中的部分列添加数据:值的顺序必须跟指定列的顺序保持一致 语法:insert into 表名(列1,列2,...) values(值1,值2,...) 2.默认添加数据 向表中的所有列添加数据:值的顺序必须跟字段顺序保持一致 语法:insert i

  • MySQL中增删改查操作与常见陷阱详解

    目录 本文导读 一.MySQL的增删改查 1.insert语句 2.delete语句 3.update语句原理 4.select 二.15种MySQL数据操作语句 1.REPLACE语句 2.CALL语句 3.TABLE语句 4.WITH语句 三.MySQL查询陷阱 总结 本文导读 本文作为MySQL系列第二篇文章,详细讲解了MySQL的增删改查的语句.语义和一些我们经常在开发工作中暴露的问题,MySQL的增删改查又叫数据操作语句,本文有讲些了一些常用的数据操作语句,select语句后续将作为一

  • node.js中 mysql 增删改查操作及async,await处理实例分析

    本文实例讲述了node.js中 mysql 增删改查操作及async,await处理.分享给大家供大家参考,具体如下: 要对mysql进行操作,我们需要安装一个mysql的库. 一.安装mysql库 npm install mysql --save 二.对mysql进行简单查询操作 const mysql = require('mysql'); //创建数据库连接 let conn = mysql.createConnection({ //主机地址 host: '127.0.0.1', //用户

  • mysql触发器之触发器的增删改查操作示例

    本文实例讲述了mysql触发器之触发器的增删改查操作.分享给大家供大家参考,具体如下: 我们在创建触发器后,可以在包含触发器定义文件的数据文件夹中显示其定义.触发器作为纯文本文件存储在以下数据库文件夹中: /data_folder/database_name/table_name.trg 我们还可通过查询information_schema数据库中的triggers表来显示触发器,如下所示: SELECT * FROM information_schema.triggers WHERE trig

  • MySQL与PHP的基础与应用专题之增删改查

    概述 从今天开始, 小白我将带领大家一起来补充一下 数据库的知识. 添加数据 SQL 语句: # 插入单条数据 INSERT INTO 表名 (列名1, 列名2, -) VALUES (值1, 值2, -) # 插入多条数据 INSERT INTO 表名 (列名1, 列名2, -) VALUES (值1, 值2, -) PHP 代码实现: <?php # 创建连接 $conn = mysqli_connect("localhost", "root", &quo

  • Vue项目通过node连接MySQL数据库并实现增删改查操作的过程详解

    目录 Vue项目通过node连接MySQL数据库 1.创建Vue项目 2.下载安装需要的插件 3.在项目中创建server文件夹,用于搭建本地服务器 4.Vue项目访问接口获取数据 数据表的增删改查操作 1.服务器配置 2.前端配置 页面样式 总结 Vue项目通过node连接MySQL数据库 1.创建Vue项目 vue create 项目名 Vue项目创建的详细步骤,有需要的可移步这里 2.下载安装需要的插件 下载express npm install express 下载cors,用于处理接口

  • Java语言实现对MySql数据库中数据的增删改查操作的代码

    简单说操作的步骤: 1.连接数据库 2.将SQL语句发送到数据库 3.执行SQL语句 这里举个例子: 在一个数据库中有个students表,表中有学号(Id),姓名(Name),性别(Sex),地址(Address),电话(Phone),专业(Dept). 这里把这个表写成一个学生信息类(Info_student) (请先确保看了例子说明,不然代码有的地方可能看不明白) 要实现操纵我们首先得连接数据库,因为每个操作都要进行连接操作,所以我们直接把连接的操作封装在一个类中,需要连接的时候直接调用可

  • python django 增删改查操作 数据库Mysql

    下面介绍一下django增删改查操作: 1.view.py # -*- coding: utf-8 -*- from __future__ import unicode_literals from django.http import HttpResponse from polls.models import Test from django.shortcuts import render # Create your views here. # 解决乱码 import sys reload(sy

随机推荐