likes
comments
collection
share

MySQL也能当Mongo用？

作者站长头像
站长
· 阅读数 17

在 MySQL 5.7.8及以上版本中提供了一种新的字段格式 json，并且提供了大量操作json类型数据的函数，使得我们的MySQL也能向Mongo一样存储文档型数据，下面我们一起来看看我们应该如何应用这种类型。

1. 新建文档表

首先，我们创建一张保存文档的表，指定主键、文档名称、文档内容，其中文档内容字段我们可以设置为json格式，这样我们就可以通过这个字段去保存json格式的数据，和Mongo一样。

在没有 json 格式之前，创建时可能会使用BLOB、TEXT或者VARCHAR来存储，在项目中使用反序列化的方式转换为对应的对象。

CREATE TABLE `document` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) DEFAULT NULL COMMENT '文档名称',
  `content` json DEFAULT NULL COMMENT '文档内容',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意： json 列不能具有非 NULL 的默认值，占用空间与LONGBLOB或LONGTEXT大致相同。

小知识： 使用 json 格式存储比使用字符串序列存储有哪些优势？

  • 自动校验json文档格式，错误格式无法插入。
  • 优化的存储格式。存储在json 格式列中的 JSON 文档将会以BLOB格式存储，不需要展示时无需转化成文档格式，直接通过二进制格式传输。

2. 初始化文档表数据

我们向文档表中增加几条数据，选取具有代表型的数据，包含value是数组的，value是对象的，确认是否都可以添加。

insert into `document` VALUES(1,'log','{"ip": "127.0.0.1", "port": "8080", "thread": "thread01"}');
insert into `document` VALUES(2,'log','{"ip": "127.0.0.1", "port": "8080", "thread": "thread02"}');
insert into `document` VALUES(3,'log','{"ip": "127.0.0.1", "port": "8080", "thread": "thread03"}');
insert into `document` VALUES(4,'log_thread_arr','{"ip": "127.0.0.1", "port": "8080",  "threadGroup": ["thread01","thread02"]}');
insert into `document` VALUES(5,'log_thread_info','{"ip": "127.0.0.1", "port": "8080", "threadInfo": {"threadNo" : "01", "threadName" : "thread01"}}');

添加成功，如下图所示：

MySQL也能当Mongo用？

我们尝试添加一条不符合json格式的数据，确认一下是否可以保存。

insert into `document` VALUES(3,'log','{"ip": "127.0.0.1", "port": "8080", "thread": "t');

不符合格式的数据是无法保存的，错误信息如下：

Invalid JSON text: "Missing a closing quotation mark in string." at position 51 in value for column 'document.content'.

3. json相关的函数

以下为几组常用的json函数，除了下面列出的函数系统中还有许多，就不一一介绍了。

不是 json 格式的列，也可以使用以下函数。

3.1 JSON_EXTRACT

JSON_EXTRACT(json_doc, path[, path] ...)函数就是帮助我们提取json中的某个值，如果json中不含有该值会返回Null，我们也可以将其作为判断条件写在WHERE后，但是一般我们不这样去写，WHERE后一般都是用3.3的写法

-- 查询 json 中某个值
SELECT JSON_EXTRACT(document.content, '$.ip') FROM document;
SELECT JSON_EXTRACT(document.content, '$.thread') FROM document;
-- 可以作为 WHERE 后条件判断
SELECT
	JSON_EXTRACT( document.content, '$.thread' ) 
FROM
	document 
WHERE
  JSON_EXTRACT( document.content, '$.thread' )  IS NOT NULL;
-- 查询 json 下一级信息
SELECT JSON_EXTRACT(document.content, '$.threadInfo.threadNo') FROM document;

查询结果：

MySQL也能当Mongo用？ MySQL也能当Mongo用？

MySQL也能当Mongo用？

MySQL也能当Mongo用？

3.2 ->

-> JSON 列返回值；相当于 JSON_EXTRACT()

SELECT * from document WHERE document.content->'$.thread' IS NOT NULL;

查询结果：

MySQL也能当Mongo用？

3.3 JSON_SET

JSON_SET(json_doc, path, val[, path, val] ...) 将数据插入 JSON 文档

UPDATE document SET document.content = JSON_SET(document.content, '$.ip', '0.0.0.0') WHERE id = 3;

3.4 JSON_CONTAINS、JSON_CONTAINS_PATH

JSON_CONTAINS(target, candidate[, path])是否包含特定对象

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)是否包含对应字段

-- 是否包含特定对象
SELECT * from document WHERE JSON_CONTAINS(document.content,JSON_OBJECT("thread","thread01"));
-- 是否包含字段
SELECT * from document WHERE 
JSON_CONTAINS_PATH(document.content, 'one', '$.thread');

查询结果：

MySQL也能当Mongo用？

MySQL也能当Mongo用？

3.5 JSON_OBJECT、JSON_ARRAY

JSON_OBJECT([key, val[, key, val] ...])是将一个键值对转换成json对象，用法如3.4

JSON_ARRAY([val[, val] ...])将一个组value转化成一个json数组

SELECT * from document WHERE JSON_CONTAINS(document.content->'$.threadGroup', JSON_ARRAY("thread01"));

查询结果：

MySQL也能当Mongo用？

3.6 JSON_INSERT

JSON_INSERT(json_doc, path, val[, path, val] ...) 插入字段

UPDATE document set document.content=JSON_INSERT(document.content, '$.id', '1','$.name','log01') 
WHERE id=1

3.7 JSON_REPLACE

JSON_REPLACE(json_doc, path, val[, path, val] ...)

UPDATE document SET document.content = JSON_REPLACE(document.content, '$.ip', '0.0.0.0') WHERE id = 1;

3.8 JSON_REMOVE

JSON_REMOVE(json_doc, path[, path] ...)是从JSON文档中删除数据

UPDATE document SET document.content = JSON_REMOVE(document.content, '$.name') WHERE id = 1;

3.9 JSON_TYPE

JSON_TYPE(json_val)查询某个json字段属性类型

SELECT JSON_TYPE(document.content->'$.ip') from document ;

一般类型就是INTEGER、STRING、ARRAY、OBJECT

3.10 JSON_KEYS

JSON_KEYS(json_doc[, path])返回文档中所有键组成的数组

SELECT JSON_KEYS(document.content) from document ;

查询结果：

MySQL也能当Mongo用？

3.11 JSON_UNQUOTE

JSON_UNQUOTE(json_val) 解除引用，直接将数据提取成string类型

3.12 JSON_LENGTH

JSON_LENGTH(json_doc[, path]) json 最外层或者指定 path 的长度，标量的长度为1。数组的长度是数组元素的数量，对象的长度是对象成员的数量。

4. 如何优化json文本的检索效率

如果我们通过json格式存储数据，数据量大的时候搜索性能会急剧下降，那么我们该如何优化呢？

json格式是无法直接建立索引的，我们需要对json中常用于查询的字段构建虚拟生成列，通过对虚拟生成列加索引，来优化查询性能。

生成列存在两种类型：存储（stored）生成列和虚拟（virtual）生成列。

  • 存储生成列

    存储生成列和普通列类似，在插入或者更新数据时自动计算并且存储该列的值，需要占用存储空间。

  • 虚拟生成列

    虚拟生成列不需要占用存储空间，只在读取时进行计算（类似于视图）。

    虚拟列除了支持索引之外，还支持NOT NULL、UNIQUE、主键、CHECK以及外键约束，但是不支持DEFAULT默认值。

-- 生成列语法  
  col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

-- 生成虚拟列
ALTER TABLE document ADD COLUMN 
v_log_id VARCHAR(32) 
GENERATED ALWAYS 
AS (JSON_UNQUOTE(JSON_EXTRACT(document.content, '$.id'))) VIRTUAL NULL;
-- 为生成列添加索引
CREATE INDEX idx_v_log_id ON document(v_log_id);
-- 查询通过虚拟列查询即可使用索引
SELECT * FROM document WHERE v_log_id = '01'

查询结果：

MySQL也能当Mongo用？

5. 总结

当我们有json文档存储需求，项目中又没有必要引入MongoDB时，就可以考虑使用MySQL的json格式，其内置函数强大的功能，足以支撑我们平时的使用，当我们出现查询瓶颈时，也可以通过MySQL虚拟生成列的方式去增加索引，优化查询效率。此外，由于我们对生产环境中的表扩展字段是极为不方便的，json格式的字段又拥有极强的扩展性，因此它非常适合被用于扩展字段，这样我们一些不是很重要的，但又需要的数据都可以放入扩展字段中。

感谢大家的阅读，如果感觉有帮助，帮点个赞，万分感谢！！！