CREATE TABLE user(
user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(10),
last_name VARCHAR(10),
credit_info JSON,
INDEX mx_creditscores( (CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY)) )
);
INSERT INTO user VALUES (1, 'Matt', 'Lee', '{"credit_scores":[360, 353, 351]}');
MEMBER OF()
JSON_CONTAINS()
JSON_OVERLAPS()
SELECT * FROM user WHERE 360 MEMBER OF(credit_info->'$.credit_scores');
user_id | first_name | last_name | credit_info |
---|---|---|---|
1 | Matt | Lee | {”credit_scores”: [360, 353, 351} |
MySQL 8.021 버전에선 DECIMAL, INTEGER, DATETIME 등에는 멀티 벨류 인덱스를 지원한다고 하지만 CHAR/VARCHAR 타입엔 아직 지원하지 않는다.