mysql优化：虚拟字段 | 数据库论坛-大发黄金版app下载

前言

最近我们一个二手应用的即时通讯模块马上就用不了了，因为当时对接的是腾讯的im服务，现在组织里说不想再续费了，功能还得保留。

那就能手写聊天模块了，所有都写得差不多的时候，在聊天记录表格里却整了一出尬尴的事儿。建表时根据消息推送方式，只存储了接收者id，发送者id却放在消息包的json中。

但是后面很多时候的查询需要两个id字段联合，也就是要从json的多层路径中找到发送者id，并作为条件。于是就有了下面通过虚拟字段解决的方法了。

知识点

1. 多层json路径标量读取
2. 虚拟字段的创建
3. 聊天消息的查询优化

原表结构

create table `bqs_chat_message` (
  `id` int(11) unsigned not null auto_increment,
  `user_id` int(11) default '0' comment '用户id',
  `data` json default null,
  `is_get` tinyint(1) default '0',
  `delete_time` int(11) default '0',
  `create_time` int(11) default '0',
  primary key (`id`),
  key `user_id` (`user_id`) using btree,
) engine=innodb auto_increment=39 default charset=utf8mb4;

data字段(json)

{
    "type":"message",
    "commit_id": 16,
    "message":"success",
    "data":{
        "type":"receive",
        "id":"1111",
        "sender_id":1110,
        "self":true,
        "time":"2020年06月01日 14:34",
        "message":{
            "type":"text",     
            "content":"好"
        },
        "group_id":0,
        "cache_key":"u1111",
        "timestamp":1590993240,
        "receiver_id":"1111"
    }
}

多层json路径标量读取

json_extract()函数可以使用json路径表达式来提取json数据中的值，比如下面的查询将返回data字段中data.sender_id路径下的值，也就是发送者id

select json_extract(data, '$.data.sender_id') as sneder_id
from bqs_chat_message

虚拟字段创建

alter table bqs_chat_message add column sender_id int(11) as (cast(json_extract(`data`,'$.data.sender_id') as signed),0) virtual comment '发送者id';

查询优化

虽然上面的方法已经实现了虚拟字段创建，但如果data字段的json路径下不存在sender_id时。数据库里显示和查询的结果出来都是null，为了让虚拟字段的类型统一为int，我们需要对不规则的json数据做处理。

也就是如果json中没有sender_id，我们就以0为默认值，这里我们可以通过ifnull和if等sql函数处理，但我这里推荐coalesce函数。

coalesce() 函数允许我们从一系列提供的参数中返回第一个非空（非null）的值，这个函数可以接受两个或更多的参数，并逐一检查它们，直到找到一个非空值为止，然后立即返回该值。如果所有的参数都是null，那么coalesce() 函数也会返回null。

alter table bqs_chat_message add column sender_id int(11) as (coalesce(cast(json_extract(`data`,'$.data.sender_id') as signed),0)) virtual comment '发送者id';

写在后面

以上的三个操作已经完全解决了建表上的缺陷，但是要想让查询变得更快，我们还可以将虚拟字段添加为索引，在查询上就不会圈表检索了。

本作品采用《cc 协议》，转载必须注明作者和本文链接