前言

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

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

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

知识点

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

原表结构

1
2
3
4
5
6
7
8
9
10

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

{
"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';

写在后面

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

前言

前两天有群友找到我想让我给他把一个美化插件改一下，插件本来只显示注册用户,浏览量,运行天数等信息。

他想给插件添加一个vip用户数量的信息，所以让我看看能不能搞。

过程

首先我想到的是用数据库来查询，但是需要一个关键词。

我在WordPress后台查看的时候发现虽然会员被区分为黄金会员和钻石会员，但是它们共同的点是都是永久的。

于是我打算从永久这一点来入手，而子比赋予永久会员的值为Permanent ，我就去数据库查找这个词。

在wp_usermeta这个数据表中有一个vip_exp_date 的参数的值就是Permanent 。

我就用AI帮我写了一个php代码，查询到Permanent 的数量一共为73个，而WordPress后台显示的黄金会员和钻石会员的总数为70个。

经过查询发现有三个普通会员也被赋予了永久会员这一身份，所以得出结论，无论是普通会员还是黄金会员亦或者钻石会员都可以被赋予永久会员这一身份。

看来只能找其他的值来区别这一点了，最后让我发现vip_level 这一参数的值要么没有要么就是1或2，于是我就认为1代表黄金会员2则代表钻石会员。

想做就做，我就用AI帮我写了一段PHP代码，贴在下面了。

<?php
global $wpdb;
// 查询用户数量
$vipusers = $wpdb->get_var(
    $wpdb->prepare(
        "SELECT COUNT(*) FROM {$wpdb->usermeta} WHERE meta_key = %s AND meta_value IN (%d, %d)",
        'vip_level', 1, 2
    )
);

可以看到是在wp_usermeta这个表中查询了关于vip_level为1或2的数量，最后显示的结果是70。

结语

搭建网站的时候不可避免的需要用到数据库，所以我们得学习好怎么操作数据库才行。

前言

因为之前安装了WPOPT，里面有个功能就是能够在控制台查看MYSQL查询次数以及页面创建的时间，之前一直都是高达四十几次的查询次数。

所以就决定安装redis将数据库查询次数降低下来。

重要提醒

如果服务器内有两个WordPress程序，请先将其中一个WordPress程序的数据库表开头从wp_改为其他的，否则会出现相互干扰的情况。

教程

本教材基于宝塔面板教学，特别方便。

1.首先到宝塔面板的软件商店找到redis 并且安装。

2.找到PHP版本，并且安装redis 扩展。

3.WordPress后台搜索并安装Redis Object Cache 。

4.设置插件启用对象存储，如出现下图则表示启用成功。

5.最后看一下MYSQL的查询次数。

只有2次查询次数，刷新了几次最多也就是15左右，大部分都是个位数，效果相当给力。

前言

迁移网站或者更改图片地址的时候需要更换数据库中域名或图片地址，对于小白来说有点困难，由子比主题作者老唐写的一款插件就排上了用场。

截图

下载地址