MySQL JSON查詢與索引詳解

更新時間：2025年11月10日 16:17:47 作者：憤怒的蘋果ext

本文給大家介紹了MySQL JSON查詢與索引的相關(guān)知識,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友參考下吧

前言

自MySQL 5.7.8開始引入原生JSON支持，可用于存儲動態(tài)的列。此時如果想要建立索引，要先建立JSON某一列的虛擬列，使用虛擬列查詢。從MySQL 8.0.17開始，InnoDB支持多值索引，相比老版本的查詢方式就更直接了。

準(zhǔn)備

創(chuàng)建一張配置表，建表語句如下。

CREATE TABLE `t_config` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `extras` json DEFAULT NULL COMMENT '擴(kuò)展列json字段',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='配置表';

測試數(shù)據(jù)

INSERT INTO `t_config` (`id`, `extras`) VALUES (1, '{\"color\": \"red\", \"phone\": [\"157\", \"153\"]}');
INSERT INTO `t_config` (`id`, `extras`) VALUES (2, '{\"color\": \"green\", \"phone\": [\"157\", \"154\"]}');

下面就開始介紹虛擬列和多值索引查詢與索引方式。

虛擬列

測試平臺5.7.26

查詢

SELECT * FROM `t_config` WHERE extras->'$.color' = 'red';
或
SELECT * FROM `t_config` WHERE json_contains(extras->'$.color','"red"');

現(xiàn)在是走全表掃描

下面創(chuàng)建虛擬列和索引

ALTER TABLE `t_config`
ADD COLUMN `v_color` VARCHAR(32) GENERATED ALWAYS AS
(JSON_UNQUOTE(JSON_EXTRACT(`extras`, _utf8mb4'$.color'))) VIRTUAL NULL;
CREATE INDEX idx_v_color on t_config(v_color);

查詢就能走索引了

EXPLAIN SELECT * FROM `t_config` WHERE v_color = 'red';

但是數(shù)組phone未找到合適的方式查詢。

多值索引

測試平臺8.0.43

查詢（第二條語句不能利用索引）

SELECT * FROM `t_config` WHERE json_contains(extras->'$.color','"red"');
 或者
SELECT * FROM `t_config` WHERE extras->'$.color' = 'red';

當(dāng)前是全表掃描

EXPLAIN SELECT * FROM `t_config` WHERE json_contains(extras->'$.color','"red"');

增加json里 color字段索引

alter table t_config add index json_color( (cast(extras->'$.color' as char(32) array)));

現(xiàn)在就能走索引了

對于數(shù)字?jǐn)?shù)組字段,查詢方式
要先創(chuàng)建索引，才能查到數(shù)據(jù)

alter table t_config add index phone( (cast(extras->'$.phone' as unsigned array)) );

 -- 查詢phone字段
 SELECT * FROM `t_config` WHERE json_contains(extras->'$.phone' , '157');
-- 查詢phone字段, 參數(shù)數(shù)組
 SELECT * FROM `t_config` WHERE json_contains(extras->'$.phone' , CAST('[157,153]' AS JSON));

能走索引