mysql中json字段查询详解

发布时间：2023-05-20

MySQL提供了一种新特性——JSON类型。相比于传统的关系型数据库设计，使用JSON更加灵活、方便，是Web和移动应用程序的理想数据存储格式。本文将从多个方面对mysqljson字段查询做详细的阐述。

一、mysqljson字段查询基础

JSON类型是MySQL 5.7版本中引入的，主要是为了解决传统关系型数据库难以存储半结构化数据的问题。JSON字段类型支持读取和修改JSON文档中的数据，如果JSON格式不正确，MySQL会自动检测并返回错误。 JSON类型的字段可以使用标准的MySQL查询语句进行处理，包括SELECT、UPDATE、DELETE、JOIN和WHERE子句等等。JSON类型数据中的每一个键值对都可以作为查询条件。 下面举个例子，假设有个表名为users，其中有个名为info_json的JSON字段。我们需要根据JSON字段中的age属性查询：

SELECT * FROM users WHERE info_json->'$.age'=25;

通过 -> 操作符可以获取json对象中某个key的值，'$'表示查询整个json 如果想根据JSON中的数组查询，则可以使用 JSON_SEARCH() 函数：

SELECT JSON_SEARCH(info_json, 'one', 'green') FROM users;

JSON_SEARCH() 函数会返回数组中满足条件的键的路径。

二、mysqljson字段的查询操作

1、插入JSON对象

在mysql中，可以使用 JSON_OBJECT() 函数创建json对象，并将其插入到表中：

INSERT INTO users (info_json) VALUES (
   JSON_OBJECT(
       'name', 'Tom', 
       'age', 20, 
       'hometown', JSON_OBJECT('province', 'Guangdong', 'city', 'Shenzhen')
   )
);

还可以通过 JSON_ARRAY()函数直接插入JSON数组值：

INSERT INTO employees (info_json) VALUES (JSON_ARRAY('John', 'Doe', '20/01/1980'));

2、查询JSON对象里的属性

查询JSON对象里的属性可以使用 -> 操作符。例如，我们想要查询info_json中name属性的值，可以使用以下命令：

SELECT info_json->'$.name' FROM users WHERE id=10;

如果属性是嵌套在另一个属性中，则可以使用 ->> 操作符：

SELECT info_json->'$.hometown->>$.city' FROM users WHERE id=10;

它会将属性的值作为结果返回。

3、查询JSON数组里的元素

查询JSON数组里的元素可以使用 JSON_EXTRACT() 函数。例如，我们要查询 employees 表中第二个元素：

SELECT JSON_EXTRACT(info_json, '$[1]') FROM employees;

它将会返回 JSON 数组中的第二个元素

4、使用WHERE子句过滤JSON属性或数组元素

可以使用WHERE子句过滤JSON属性或数组元素，比如:

SELECT info_json->"$.type" as type, info_json->"$.value" as value 
FROM mytable 
WHERE info_json->"$.type" = "email"

MySQL 还提供了一些内置函数可以方便使用 JSON 数据类型：

  • JSON_CONTAINS(): 判断是否包含指定的 JSON 数据
  • JSON_MERGE(): 合并多个 JSON 数据
  • JSON_OBJECTAGG(): 聚合组成 JSON 对象

三、mysqljson字段的高级应用

1、JSON搜索引擎

MySQL还有一项高级功能——JSON搜索引擎（JSON Search Engine） 为了加快搜索速度，MySQL使用了基于搜索树的快速查找方式。这种方式可以根据给定的约束条件查找符合条件的JSON对象，然后保留JSON对象中的符合条件的搜索结果。 这种搜索引擎使用了一种结合了B树和B+树的算法来存储索引文件。这种算法既可以使用二分查找获取数据，又可以使用B树来获取数据。

SELECT * FROM user_info 
 WHERE MATCH(userInfoCmt)
 AGAINST('+java +php' IN BOOLEAN MODE);

2、JSON数据转换为MySQL的行

有时候，我们希望JSON数据可以转换成MySQL的行，这样就可以使用标准的SQL查询。MySQL提供了一个叫做generated的列类型，使得这样的转化变得简单。

CREATE TABLE mytable (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                      json_col JSON NOT NULL,
                      generated_col VARCHAR(255)
                      GENERATED ALWAYS AS (JSON_EXTRACT(json_col, '$.name')) VIRTUAL);

因此，在查询mytable时，就可以使用SELECT来查询数据，并且可以像标准列一样使用下面的WHERE子句：

SELECT * FROM mytable WHERE generated_col = 'test_value';

3、使用JSON数组可重复的key

JSON数组是一种灵活的数据类型，可以包含重复的键。当需要在JSON数组中存储一组键值对时，可以使用下面的语法：

[
  {"key1": value1, key2: value2},
  {"key1": anotherValue1, key2: anotherValue2}
]

这种表示方法有时候可以在爬虫中非常有用，因为可以使用JSON_STORAGE_FREE_FORM或者VIRTUAL列来处理JSON中的重复键：

CREATE TABLE my_table ( 
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 data JSON NOT NULL,
 PRIMARY KEY(id)
);
SELECT data->"$.[*].key" AS key,
       data->"$.[*].value" AS value
FROM my_table
WHERE JSON_CONTAINS(JSON_KEYS(data), 'key');

四、总结

本文对mysqljson字段查询进行了详细的阐述，包括基础操作、高级使用，以及使用JSON搜索引擎、使用JSON数组可重复的key等。JSON类型是mysql数据库一个强大的新特性，在存储非结构化数据方面表现十分优异，我们希望这篇文章对你有所帮助。

推荐文章