MySQL8之JSON_OVERLAPS、JSON_CONTAINS

表结构如下所示：

CREATE TABLE `test_json` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `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,
  `custinfo` json DEFAULT NULL COMMENT 'json数据',
  PRIMARY KEY (`id`),
  KEY `json_more_value_index` ((cast(json_extract(`custinfo`,_utf8mb4'$.zipcode') as unsigned array)),`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SELECT * FROM test_json WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94582,94536]' AS JSON)) and age = 18 and name = '张三';

explain语句如下所示：

explain SELECT * FROM test_json WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94582,94536]' AS JSON)) and age = 18 and name = '张三';

2、JSON_OVERLAPS查询语句如下所示：

SELECT  * FROM test_json WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94536,94582]' AS JSON)) and age = 18 and name = '张三';

JSON_OVERLAPS执行计划如下所示：

总结：JSON_CONTAINS和JSON_OVERLAPS是比较常用的。