MySQL优化：虚拟字段

前言

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

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

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

知识点

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

原表结构

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

1 2	SELECT JSON_EXTRACT(data, '$.data.sender_id') AS sneder_id FROM bqs_chat_message

虚拟字段创建

1	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。

1	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';