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

上一篇文章我们介绍了mysql数据存储过程参数实例详解,今天我们看看MySQL操作之JSON数据类型的相关内容。

概述

mysql自5.7.8版本开始,就支持了json结构的数据存储和查询,这表明了mysql也在不断的学习和增加nosql数据库的有点。但mysql毕竟是关系型数据库,在处理json这种非结构化的数据时,还是比较别扭的。

创建一个JSON字段的表

首先先创建一个表,这个表包含一个json格式的字段:

CREATE TABLE table_name (
  id INT NOT NULL AUTO_INCREMENT,
  json_col JSON,
  PRIMARY KEY(id)
);

上面的语句,主要注意json_col这个字段,指定的数据类型是JSON。

插入一条简单的JSON数据

INSERT INTO
  table_name (json_col)
VALUES
  ('{"City": "Galle", "Description": "Best damn city in the world"}');
  

上面这个SQL语句,主要注意VALUES后面的部分,由于json格式的数据里,需要有双引号来标识字符串,所以,VALUES后面的内容需要用单引号包裹。

插入一条复杂的JSON数据

INSERT INTO table(col)
VALUES('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}');

这地方,我们插入了一个json数组。主要还是注意单引号和双引号的问题。

修改JSON数据

之前的例子中,我们插入了几条JSON数据,但是如果我们想修改JSON数据里的某个内容,怎么实现了?比如我们向 variations 数组里增加一个元素,可以这样:

UPDATE myjson SET dict=JSON_ARRAY_APPEND(dict,'$.variations','scheveningen') WHERE id = 2;

这个SQL语句中,$符合代表JSON字段,通过.号索引到variations字段,然后通过JSON_ARRAY_APPEND函数增加一个元素。现在我们执行查询语句:

SELECT * FROM myjson

得到的结果是:

+----+-----------------------------------------------------------------------------------------+
| id | dict                                          |
+---+-----------------------------------------------------------------------------------------+
| 2 | {"opening": "Sicilian", "variations": ["pelikan", "dragon", "najdorf", "scheveningen"]} |
+----+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

关于MySQL中,JSON数据的获取方法,参照官方链接JSON Path Syntax

创建索引

MySQL的JSON格式数据不能直接创建索引,但是可以变通一下,把要搜索的数据单独拎出来,单独一个数据列,然后在这个字段上键一个索引。下面是官方的例子:

mysql> CREATE TABLE jemp (
  ->   c JSON,
  ->   g INT GENERATED ALWAYS AS (c->"$.id"),
  ->   INDEX i (g)
  -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
   >  ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
   >  ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT c->>"$.name" AS name
   >   FROM jemp WHERE g > 2;
+--------+
| name  |
+--------+
| Barney |
| Betty |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
   >  FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: jemp
  partitions: NULL
     type: range
possible_keys: i
     key: i
   key_len: 5
     ref: NULL
     rows: 2
   filtered: 100.00
    Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
 Level: Note
  Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

这个例子很简单,就是把JSON字段里的id字段,单独拎出来成字段g,然后在字段g上做索引,查询条件也是在字段g上。

字符串转JSON格式

把json格式的字符串转换成MySQL的JSON类型:

SELECT CAST('[1,2,3]' as JSON) ;
SELECT CAST('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' as JSON);

所有MYSQL JSON函数

Name Description
JSON_APPEND() Append data to JSON document
JSON_ARRAY() Create JSON array
JSON_ARRAY_APPEND() Append data to JSON document
JSON_ARRAY_INSERT() Insert into JSON array-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS() Whether JSON document contains specific object at path
JSON_CONTAINS_PATH() Whether JSON document contains any data at path
JSON_DEPTH() Maximum depth of JSON document
JSON_EXTRACT() Return data from JSON document->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT() Insert data into JSON document
JSON_KEYS() Array of keys from JSON document
JSON_LENGTH() Number of elements in JSON document
JSON_MERGE() Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
JSON_MERGE_PRESERVE() Merge JSON documents, preserving duplicate keys
JSON_OBJECT() Create JSON object
JSON_QUOTE() Quote JSON document
JSON_REMOVE() Remove data from JSON document
JSON_REPLACE() Replace values in JSON document
JSON_SEARCH() Path to value within JSON document
JSON_SET() Insert data into JSON document
JSON_TYPE() Type of JSON value
JSON_UNQUOTE() Unquote JSON value
JSON_VALID() Whether JSON value is valid

总结

以上就是本文关于MySQL操作之JSON数据类型操作详解的全部内容,希望对大家有所帮助。感兴趣的朋友可以继续参阅本站:mysql数据存储过程参数实例详解、简述Redis和MySQL的区别、几个比较重要的MySQL变量等,如有不足之处,欢迎留言指出,小编会及时回复大家并进行修改,努力为广大编程爱好及工作者提供更好的文章和阅读体验。下面推荐几本跟MySQL操作有关的书籍,供大家参考:

MySQL数据库应用从入门到精通(第2版) PDF扫描版

http://www.jb51.net/books/361239.html

MySQL5 权威指南(第3版)中文版 PDF扫描版

http://www.jb51.net/books/367031.html

希望大家能够喜欢,更多精彩内容尽在:http://www.jb51.net/

时间: 2017-10-18

MySQL如何查看元数据锁阻塞在哪里

MySQL如何查看元数据锁阻塞在哪里 操作步骤: 1.session 1 执行: start transaction; select *from t1; 2.session 2 在第1步执行完后执行: drop table t1; 此时session 2的drop语句被阻塞.那么怎么分析查看元数据锁呢? 方法: 1)执行show processlist;,可以看到drop语句在等待元数据锁 mysql> show processlist; +----+-------------+--------

MySQL分区字段列有必要再单独建索引吗?

前言 大家都知道对于分区字段必须是主键的一部分,那么建了复合主键之后,是否需要对分许字段再单独添加一个索引呢?有没有效果?来验证一下,下面话不多说了,来一起看看详细的介绍吧. 1.新建表effect_new(以创建时间按月分区) CREATE TABLE `effect_new` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `type` tinyint(4) NOT NULL DEFAULT '0', `timezone` varchar(10) DEF

MySQL启用SSD存储的实例详解

MySQL启用SSD存储的实例详解 有时OS读写慢会降低MySQL服务器的性能,尤其是OS与MySQL使用同一磁盘时.故最好是让MySQL使用单独的磁盘,能使用SSD更好.要做到这一点,需要把SSD新磁盘挂载到服务器上,假定新磁盘在/dev/sdb. 1.准备新磁盘: # fdisk /dev/sdb 按下"n"将创建一个新分区:按下"p"将创建新的主分区.接着设置分区号(从1-4),再选择分区的尺寸,按下回车键. 如果不想使用整个磁盘作为一个分区,那么还需要继续创

MySQL 清除表空间碎片的实例详解

MySQL 清除表空间碎片的实例详解 碎片产生的原因 (1)表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白.被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大: (2)当执行插入操作时,MySQL会尝试使用空白空间,但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片: (3)当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分: 例如: 一个表有1万行

docker连接spring boot和mysql容器方法介绍

在之前使用docker部署运行了Spring Boot的小例子,但是没有使用数据库.在这一篇中,介绍docker如何启动mysql容器,以及如何将Spring Boot容器与mysql容器连接起来运行. docker基本命令 首先熟悉一下在操作过程中常用的docker基本命令: docker images:列出所有docker镜像 docker ps:列出所有运行中的容器,-a参数可以列出所有容器,包括停止的 docker stop container_id:停止容器 docker start

JDBC连接mysql处理中文时乱码解决办法详解

JDBC连接mysql处理中文时乱码解决办法详解 近日,整合的项目需要跟一个比较老版本的mysql服务器连接,使用navicat查看,发现此mysql服务器貌似没有设置默认编码,而且从操作此mysql的部分php文件看,应该是使用的gb2312的编码,但是,直接使用jdbc操作,从库中读取出来的中文全都是乱码. 一开始,使用类似entity.setDepartName(new String(rs.getString("hg").getBytes("gbk"), &q

MySQL数据类型中DECIMAL的用法实例详解

MySQL数据类型中DECIMAL的用法实例详解 在MySQL数据类型中,例如INT,FLOAT,DOUBLE,CHAR,DECIMAL等,它们都有各自的作用,下面我们就主要来介绍一下MySQL数据类型中的DECIMAL类型的作用和用法. 一般赋予浮点列的值被四舍五入到这个列所指定的十进制数.如果在一个FLOAT(8, 1)的列中存储1. 2 3 4 5 6,则结果为1. 2.如果将相同的值存入FLOAT(8, 4) 的列中,则结果为1. 2 3 4 6. 这表示应该定义具有足够位数的浮点列以便

Python中optparser库用法实例详解

本文研究的主要是Python中optparser库的相关内容,具体如下. 一直以来对optparser不是特别的理解,今天就狠下心,静下心研究了一下这个库.当然了,不敢说理解的很到位,但是足以应付正常的使用了.废话不多说,开始今天的分享吧. 简介 optparse模块主要用来为脚本传递命令参数功能. 引入 在IDE中引入optparser是很方便的. from optparser import OptionParser 初始化 相对而言,初始化需要我们多注意一点点了. 因为我们有两种不同的方式来

python中字典(Dictionary)用法实例详解

本文实例讲述了python中字典(Dictionary)用法.分享给大家供大家参考.具体分析如下: 字典(Dictionary)是一种映射结构的数据类型,由无序的"键-值对"组成.字典的键必须是不可改变的类型,如:字符串,数字,tuple:值可以为任何python数据类型. 1.新建字典 >>> dict1={} #建立一个空字典 >>> type(dict1) <type 'dict'> 2.增加字典元素:两种方法 >>&g

Laravel中Trait的用法实例详解

本文实例讲述了Laravel中Trait的用法.分享给大家供大家参考,具体如下: 看看PHP官方手册对Trait的定义: 自 PHP 5.4.0 起,PHP 实现了代码复用的一个方法,称为 traits. Traits 是一种为类似 PHP 的单继承语言而准备的代码复用机制.Trait 为了减少单继承语言的限制,使开发人员能够自由地在不同层次结构内独立的类中复用方法集.Traits 和类组合的语义是定义了一种方式来减少复杂性,避免传统多继承和混入类(Mixin)相关的典型问题. Trait 和一

Jquery中基本选择器用法实例详解

本文实例讲述了Jquery中基本选择器用法.分享给大家供大家参考.具体如下: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head

java中Calendar类用法实例详解

本文实例讲述了java中Calendar类用法.分享给大家供大家参考,具体如下: java中的Calendar在开发中经常被忽略,这篇博客总结一下这个类,对后面项目中使用时期的时候有帮助. Calendar常量(field)的作用 Calendar cal = Calendar.getInstance(); cal.get(Calendar.DATE);//-----------------------当天 1-31 cal.get(Calendar.DAY_OF_MONTH);//------

Python3.2中Print函数用法实例详解

本文实例讲述了Python3.2中Print函数用法.分享给大家供大家参考.具体分析如下: 1. 输出字符串 >>> strHello = 'Hello World' >>> print (strHello) Hello World 2. 格式化输出整数 支持参数格式化,与C语言的printf类似 >>> strHello = "the length of (%s) is %d" %('Hello World',len('Hello

python中urllib模块用法实例详解

本文实例讲述了python中urllib模块用法.分享给大家供大家参考.具体分析如下: 一.问题: 近期公司项目的需求是根据客户提供的api,我们定时去获取数据, 之前的方案是用php收集任务存入到redis队列,然后在linux下做一个常驻进程跑某一个php文件, 该php文件就一个无限循环,判断redis队列,有就执行,没有就break. 二.解决方法: 最近刚好学了一下python, python的urllib模块或许比php的curl更快,而且简单. 贴一下代码 复制代码 代码如下: #

Python中subprocess模块用法实例详解

本文实例讲述了Python中subprocess模块用法.分享给大家供大家参考.具体如下: 执行命令: >>> subprocess.call(["ls", "-l"]) 0 >>> subprocess.call("exit 1", shell=True) 1 测试调用系统中cmd命令,显示命令执行的结果: x=subprocess.check_output(["echo", "

python中argparse模块用法实例详解

本文实例讲述了python中argparse模块用法.分享给大家供大家参考.具体分析如下: 平常在写命令行工具的时候,经常会带参数,所以用python中的argparse来实现. # -*- coding: utf-8 -*- import argparse args = "-f hello.txt -n 1 2 3 -x 100 -y b -z a -q hello @args.txt i_am_bar -h".split() # 使用@args.txt要求fromfile_pref