假设定义了一张表 School,其中字段 stages 为 JSON 类型
CREATE TABLE `schools` (
`id` bigint NOT NULL AUTO_INCREMENT,
`stages` json DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
插入测试数据
INSERT INTO `schools` ( `stages` ) VALUES ( '[2]' );
查询表中字段 stages 中包含数值 2 的所有记录
- JSON_EXTRACT & LIKE
使用 JSON_EXTRACT 函数,可以从 JSON 中提取出指定位置的值。但是它只能提取某个特定位置的值,不能查询是否包含某个值。所以如果需要查询 JSON 数组是否包含某个值,还需补充使用 LIKE。
-- 使用JSON_EXTRACT函数将数组的第一个元素提取出来, 然后使用LIKE声明匹配是否包含字符串"2"
-- 注意, 这种方式只能用于已知数组中元素位置的情况, 它无法用于查找一个值是否在任何位置上都存在
SELECT
*
FROM
`schools` AS `SchoolModel`
WHERE
JSON_EXTRACT( stages, '$[0]' ) LIKE '%2%'
- JSON_CONTAINS
MySQL 5.7 版本引入了 JSON_CONTAINS 函数,用于判断一个 JSON 数组是否包含某个元素。
-- 第一个参数是查询的JSON对象, 第二个参数是需要查找的JSON对象(在这里是包含一个字符串"2"的JSON数组), 第三个参数是$(表示查询应该从整个JSON对象开始)
-- 注意, 如果想搜索一个不是独立字符串的值, 例如数字或布尔值, 需要将值转换成JSON格
-- 例如, 如果想搜索数字2, 在JSON格式中, 需要写成'2'而不是原始数字2
SELECT
*
FROM
`schools` AS `SchoolModel`
WHERE
JSON_CONTAINS(
stages,
'2', -- '[2]'
'$'
)
JSON_CONTAINS 函数同样可以用于搜索嵌套在一个对象或数组中的元素。
-- 这个查询将返回空结果, 因为JSON数组中没有{"name":"小学"}
SELECT
*
FROM
`schools`
WHERE
JSON_CONTAINS( `stages`, '{"name":"小学"}', "$" );
参考资料
- https://deepinout.com/mysql/mysql-questions/568_mysql_select_where_json_array_contains.html