MySQL 8.0.17 JSON数据类型的学习

CREATE TABLE Tags (
    tagId bigint auto_increment,
    tagName varchar(255) NOT NULL,
    primary key(tagId)
);

1,70后
2,80后
3,90后
4,00后
5,爱运动
6,高学历
7,小资
8,有房
9,有车
10,常看电影
11,爱网购
12,爱外卖


DROP TABLE IF EXISTS UserTag;
CREATE TABLE UserTag (
    userId bigint NOT NULL,
    userTags JSON,
    PRIMARY KEY (userId)
);

INSERT INTO UserTag VALUES (1,'[2,6,8,10]');
INSERT INTO UserTag VALUES (2,'[3,10,12]');


ALTER TABLE UserTag
ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));

EXPLAIN SELECT * FROM UserTag
WHERE 10 MEMBER OF(userTags->"$")

SELECT * FROM UserTag
WHERE 6 MEMBER OF(userTags->"$");

SELECT * FROM UserTag
WHERE JSON_CONTAINS(userTags->"$", '[2,3]')
-- where userTages = 2 and userTages = 3 
-- json_contains 这个相当于and;

SELECT * FROM UserTag
WHERE JSON_OVERLAPS(userTags->"$", '[2,3]')
-- where userTages = 2 or userTages = 3
-- JSON_OVERLAPS 这个相当于是or

大佬链接：
https://blog.csdn.net/java_pfx/article/details/116594654