mysql 5.7 json效率 mysql json操作

MySql中json类型的使用

MySQL从5.7.8起开始支持JSON字段，这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能，至少没有直接对其字段进行索引的方法。本文将介绍利用MySQL 5.7中的虚拟字段的功能来对JSON对象中的字段进行索引。

---

一、使用json的目的

1、可以直接过滤记录
2、可以直接update，而无须先读取
3、可以在一条SQL中完成多条纪录的修改！
4、通过json类型，完美的实现了表结构的动态变化
5、通过计算生成列且在该列上建立索引。提高查询效率

二、开始使用

1.建表

建表语句如下：

CREATE TABLE `msg_info` (
  `id` int(10) unsigned NOT NULL,
  `message` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入数据

insert into msg_info values (1,'{"name":"zhangsan","phone":"13752763211"}');
insert into msg_info values (2,'{"name":"lisi","phone":"13752763222"}');

3.查询更新操作

1、过滤记录

select * from	msg_info where message->'$.phone'='13752763211';

2、查询json内指定字段

select message->'$.name' from	msg_info;
# 这样查询出来的字段是带双引号的，使用如下语句可去除双引号，也可以使用关键字JSON_UNQUOTE
select message->>'$.name' from	msg_info;

3、直接更新json串内的字段内容

UPDATE msg_info set message = JSON_SET(message, '$.name', 'lili') WHERE id = 1;
# 为json串添加字段
update msg_info set message = JSON_INSERT(message, '$.age', 30) WHERE id = 1;

3.动态扩展字段

1、为json添加虚拟字段

ALTER TABLE msg_info  ADD v_phone  varchar (12) GENERATED ALWAYS  AS (JSON_UNQUOTE(message->'$.phone' ));

2、为虚拟字段创建索引，提高查询效率

# 通过执行计划可以查看创建索引前后的变化
ALTER TABLE msg_info ADD INDEX idx_phone(v_phone);