MySQL5.7中JSON字段虚拟列的使用

前言

看了IT老齐的视频学习到的干货，记录一下，视频地址：https://www.bilibili.com/video/BV1WP4y1K7xB/?p=133&spm_id_from=pageDriver

准备

MySQL至少需要5.7版本，因为5.7之后才支持json字段

1.创建表

CREATE TABLE `weiyiji` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `extra` json DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`) COMMENT '年龄索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `weiyiji` (`id`, `name`, `extra`) VALUES (1, '张三', '{\"age\": 100, \"name\": \"abc\"}');
INSERT INTO `weiyiji` (`id`, `name`, `extra`) VALUES (2, '李四', '{\"age\": 105, \"name\": \"abc\"}');

2.根据指定属性查询数据

SELECT
	name,
	extra ->'$.age',
	extra ->'$.name'
FROM
	`weiyiji`
	WHERE  extra ->'$.age'= 10;

3.根据JSON函数生成虚拟列

ALTER TABLE weiyiji ADD COLUMN age INT4 GENERATED ALWAYS AS (
	JSON_UNQUOTE(
	JSON_EXTRACT( extra, _utf8mb4 '$.age' ))) VIRTUAL NULL;

4.给生成的虚拟列创建索引

5.根据索引查询数据

SELECT
	name,
	extra ->'$.age',
	extra ->'$.name'
FROM weiyiji WHERE age = 10;

小结

这样通过单独给指定要查询的属性生成虚拟列并且添加上索引后，就可以大大的提高数据的查询效率，并且如果修改指定属性的值时，生成的虚拟列也可以自动生成。