MySql模糊查询json关键字检索方案示例

一、背景

在MySQL中，我们可以使用like关键字进行模糊查询，但是在JSON类型字段中，我们需要对其中的某个关键字进行模糊匹配，这时候like关键字就不再适用。本文将介绍一种解决方案，通过MySQL内置函数提供的json_extract()函数和regexp关键字实现json关键字检索。

二、方案

假设我们有一个表格为：

CREATE TABLE test (
  id int(11) NOT NULL AUTO_INCREMENT,
  msg json DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

其中，msg字段为json类型，存储着如下数据：

{"id":1, "name": "John", "desc": "hello world"}
{"id":2, "name": "Lucy", "desc": "nice to meet you"}
{"id":3, "name": "Sam", "desc": "good morning"}

我们需要检索出包含关键字"hello"的记录。

1. json_extract()函数

json_extract()函数是MySQL提供的用于提取json字段中指定key的值的函数。语法为：

json_extract(json_doc, path[, path] ...)

其中，json_doc表示要提取的json字段，path表示要提取的key路径。

以提取desc为例，语句为：

select json_extract(msg, '$.desc') from test;

结果为：

hello world
nice to meet you
good morning

2. regexp关键字

regexp关键字是MySQL提供的正则表达式匹配关键字，用于判断字符串是否符合正则表达式的模式。regex的语法为：

regexp pattern

其中，pattern为正则表达式模式。

以匹配包含"hello"的记录为例，语句为：

select * from test where json_extract(msg, '$.desc') regexp 'hello';

结果为：

{"id":1, "name": "John", "desc": "hello world"}

这个语句的实现原理是，先使用json_extract()函数提取msg中的desc值，再使用regexp关键字判断是否包含"hello"。

三、总结

本文介绍了一种使用json_extract()函数和regexp关键字实现json关键字检索的方案。虽然不能像like关键字一样直接匹配，但是这种方案更加灵活。