-- 创建表
CREATE TABLE `newjsontable` (
`Id` int NOT NULL AUTO_INCREMENT,
`JData` json NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 插入数据
INSERT INTO newjsontable (JData) VALUES('{"city": ["北京", "上海"], "name": "zhangsan"}');
INSERT INTO newjsontable (JData) VALUES('{"city": ["天津", "唐山"], "name": "lisi"}');
-- 无索引查询
SELECT Id, JData->'$.name' FROM newjsontable WHERE JData->'$.name'='lisi'
-- 添加虚拟列
ALTER TABLE newjsontable ADD Name varchar(100) GENERATED ALWAYS AS (JData->'$.name') virtual;
-- 可以看到name字段
SELECT * FROM newjsontable;
-- 根据name创建索引
CREATE INDEX newjsontable_Name_IDX USING BTREE ON newjsontable (Name);
-- 此时可以根据name查询数据
SELECT Id, JData->'$.name' FROM newjsontable WHERE JData->'$.name'='lisi'