mysql8 json 索引

hao20863

已于 2022-09-10 17:25:19 修改

阅读量2k

点赞数 3

分类专栏： mysql 文章标签： json 数据库 sql

于 2022-09-10 17:24:55 首次发布

本文链接：https://blog.csdn.net/hao20863/article/details/126797971

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql8 json 索引

创建表

DROP TABLE `test_json`;

CREATE TABLE test_json ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	name varchar(32) not null,
    age int not null,
	test_json_array JSON NOT NULL,
	test_json_object JSON NOT NULL,
    test_json_array_object JSON NOT NULL
);



INSERT INTO `ry_cloud`.`test_json` (`name`, `age`, `test_json_array`, `test_json_object`, `test_json_array_object`) VALUES ('张三', '18', '[1,2,3]', '{\"height\":\"175cm\",\"weight\":\"50kg\"}', '[{\"hobby\":\"打篮球\",\"group\":1},{\"hobby\":\"睡觉\",\"group\":2}]');
INSERT INTO `ry_cloud`.`test_json` (`name`, `age`, `test_json_array`, `test_json_object`, `test_json_array_object`) VALUES ('赵四', '22', '[1,4,6]', '{\"height\":\"170cm\",\"weight\":\"57kg\"}', '[{\"hobby\":\"打游戏\",\"group\":3},{\"hobby\":\"睡觉\",\"group\":4}]');
INSERT INTO `ry_cloud`.`test_json` (`name`, `age`, `test_json_array`, `test_json_object`, `test_json_array_object`) VALUES ('王五', '36', '[4,5,7]', '{\"height\":\"180cm\",\"weight\":\"60kg\"}', '[{\"hobby\":\"看博客\",\"group\":9},{\"hobby\":\"睡觉\",\"group\":6}]');

test_json_array数组建立索引例如:

[1,2,3]


alter table test_json DROP INDEX  idx_test_json_test_json_array;


ALTER TABLE test_json
ADD INDEX idx_test_json_test_json_array ((cast(test_json_array->"$[*]" as unsigned array)));



explain select * from test_json t where JSON_OVERLAPS (test_json_array -> '$[*]',CAST( '[1,2]' AS JSON ));

以下方式建立索引方式不会生效

  ALTER TABLE test_json
ADD INDEX idx_test_json_test_json_array ((cast((test_json_array->"$") as unsigned array)));

json对象建立索引例如:

{“height”: “175cm”, “weight”: “50kg”}

# 普通索引
alter table test_json DROP INDEX  idx_test_json_test_json_object;

ALTER TABLE test_json  ADD INDEX idx_test_json_test_json_object((CAST(test_json_object->'$.height' AS CHAR(64) ARRAY )))

explain select * from test_json t where '175cm' member of (test_json_object->'$.height');


# 添加伪列
ALTER TABLE test_json ADD COLUMN height VARCHAR(128) AS (test_json_object->>"$.height");

ALTER TABLE test_json ADD  INDEX idx_test_json_height(height);

json数组对象建立索引例如:

[{“group”: 1, “hobby”: “打篮球”}, {“group”: 2, “hobby”: “睡觉”}]

ALTER TABLE test_json ADD INDEX idx_test_json_test_json_array_objec ((cast(test_json_array_object->"$[*].hobby" as CHAR(64) array)));


explain  select * from test_json where  JSON_OVERLAPS (test_json_array_object -> '$[*].hobby',CAST( '["打游戏","打篮球"]' AS JSON ));

建多值索引

alter table customers DROP INDEX idx_age_custinfo$list_modified ;


alter table test_json DROP INDEX test_json$list_modified ;
ALTER TABLE test_json ADD INDEX test_json$list_modified ( age, (CAST( test_json_array -> '$[*]' AS UNSIGNED ARRAY )),name );
ALTER TABLE test_json ADD INDEX test_json$list_modified ((CAST( test_json_array_object -> '$[*].hobby' AS   CHAR(64) ARRAY )), age  );