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数据库一个强大的新特性，在存储非结构化数据方面表现十分优异，我们希望这篇文章对你有所帮助。
原创文章，作者：DIHN，如若转载，请注明出处：https://www.506064.com/n/143328.html
微信扫一扫 
支付宝扫一扫 