前提:mysql版本 8.0.25
1、表如下
CREATE TABLE `app_tag` (
`pkg` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'package',
`name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '应用名称',
`tags` json DEFAULT NULL COMMENT 'tag',
PRIMARY KEY (`pkg`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='app包tag';
数据如下
INSERT INTO `app_tag` VALUES ('com.netease.cloudmusic', '\r\n\r\n网易云音乐', '[\"MV\", \"电台\", \"听歌\"]');
INSERT INTO `app_tag` VALUES ('com.p1.mobile.putong', '探探', '[\"交友\"]');
INSERT INTO `app_tag` VALUES ('com.smile.gifmaker', '\r\n\r\n快手', '[\"短视频\"]');
INSERT INTO `app_tag` VALUES ('com.xiaomi.youpin', '小米有品', '[\"电商\", \"小米\", \"智能家居\"]');
pkg |name |tags |
----------------------+-------+--------------------+
com.netease.cloudmusic|¶¶网易云音乐|["MV", "电台", "听歌"] |
com.p1.mobile.putong |探探 |["交友"] |
com.smile.gifmaker |¶¶快手 |["短视频"] |
com.xiaomi.youpin |小米有品 |["电商", "小米", "智能家居"]|
1、查询 tags 包含 交友 tag的数据
select *
from app_tag
where JSON_CONTAINS(tags->'$[*]','"交友"','$')
-- 模糊查询
select *
from app_tag
where tags -> '$' like "%游%"
结果如下:
pkg |name|tags |
--------------------+----+------+
com.p1.mobile.putong|探探 |["交友"]|
2、将tags每个标签一行进行查询
SELECT name,pkg,
JSON_EXTRACT(tags, CONCAT('$[', idx, ']')) AS fishes
FROM app_tag
JOIN (
SELECT 0 AS idx UNION
SELECT 1 AS idx UNION
SELECT 2 AS idx UNION
-- ... continue as needed to max length of JSON array
SELECT 3
) AS indexes
WHERE JSON_EXTRACT(tags, CONCAT('$[', idx, ']')) IS NOT NULL
ORDER BY pkg, idx;
查询结果如下
name |pkg |fishes|
-------+----------------------+------+
¶¶网易云音乐|com.netease.cloudmusic|"MV" |
¶¶网易云音乐|com.netease.cloudmusic|"电台" |
¶¶网易云音乐|com.netease.cloudmusic|"听歌" |
探探 |com.p1.mobile.putong |"交友" |
¶¶快手 |com.smile.gifmaker |"短视频" |
小米有品 |com.xiaomi.youpin |"电商" |
小米有品 |com.xiaomi.youpin |"小米" |
小米有品 |com.xiaomi.youpin |"智能家居"|

