如何存储含逻辑运算的消息目标并实现SQL查询匹配

一、数据库表设计 #

要实现这种复杂的群组匹配，必须把消息的目标规则结构化存储，不能直接存字符串，否则查询时无法高效解析。以下是核心表结构：

1. 群组类型表（group_types） #

维护所有群组类型：

CREATE TABLE group_types (
    type_id INT PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(20) UNIQUE NOT NULL -- 示例值：'A'、'B'、'C'
);

2. 群表层（groups） #

维护所有具体群组，关联到对应类型：

CREATE TABLE groups (
    group_id INT PRIMARY KEY AUTO_INCREMENT,
    type_id INT NOT NULL,
    group_name VARCHAR(20) UNIQUE NOT NULL -- 示例值：'A1'、'B2'
);

3. 用户群组关联表（user_groups） #

记录每个用户在各类型下所属的唯一群组：

CREATE TABLE user_groups (
    user_id INT NOT NULL,
    group_id INT NOT NULL,
    PRIMARY KEY (user_id, group_id)
);
-- 约束：每个user_id对应不同type_id的group_id，确保一个类型下只有一条记录

4. 消息表（messages） #

存储消息基础内容：

CREATE TABLE messages (
    msg_id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

5. 消息目标规则组表（message_target_rules） #

每个记录对应消息目标中的一个“条件组”（比如[A1,B1,C2]或[A2,B2,C1]，多个条件组用OR连接）：

CREATE TABLE message_target_rules (
    rule_id INT PRIMARY KEY AUTO_INCREMENT,
    msg_id INT NOT NULL,
    FOREIGN KEY (msg_id) REFERENCES messages(msg_id)
);

6. 规则组明细（rule_group_details） #

每个条件组下，对应各类型的匹配规则（比如类型A的A1|A2、类型B的*）：

CREATE TABLE rule_group_details (
    detail_id INT PRIMARY KEY AUTO_INCREMENT,
    rule_id INT NOT NULL,
    type_id INT NOT NULL,
    match_value VARCHAR(100) NOT NULL, -- 可选值：'*' 或 逗号分隔的群组名称，比如'A1,A2'
    UNIQUE KEY (rule_id, type_id), -- 一个规则组下每个类型只能有一条规则
    FOREIGN KEY (rule_id) REFERENCES message_target_rules(rule_id)
);

二、数据插入示例 #

以消息目标([A1,B1,C2] | [A2,B2,C1])为例：

1. 插入消息到messages，得到msg_id=1
2. 插入两个规则组到message_target_rules：rule_id=1（对应[A1,B1,C2]）、rule_id=2（对应[A2,B2,C1]），均关联msg_id=1
3. 为rule_id=1插入三条明细：
   • type_id=A的ID，match_value='A1'
   • type_id=B的ID，match_value='B1'
   • type_id=C的ID，match_value='C2'
4. 为rule_id=2插入三条明细：
   • type_id=A的ID，match_value='A2'
   • type_id=B的ID，match_value='B2'
   • type_id=C的ID，match_value='C1'

再比如消息目标[A1|A2, *, *]，插入一个规则组，三条明细：

• type_id=A的ID，match_value='A1,A2'
• type_id=B的ID，match_value='*'
• type_id=C的ID，match_value='*'

三、查询匹配用户的消息 #

以查询user_id=123的匹配消息为例：

1. 先获取用户各类型的所属群组 #

SELECT 
    gt.type_id,
    g.group_name
FROM user_groups ug
JOIN groups g ON ug.group_id = g.group_id
JOIN group_types gt ON g.type_id = gt.type_id
WHERE ug.user_id = 123;

2. 关联查询匹配的消息 #

核心逻辑：找到至少一个规则组，该规则组下所有类型的匹配规则都符合用户的群组

SELECT DISTINCT m.msg_id, m.content, m.create_time
FROM messages m
JOIN message_target_rules mtr ON m.msg_id = mtr.msg_id
JOIN rule_group_details rgd ON mtr.rule_id = rgd.rule_id
-- 关联用户的群组信息
JOIN (
    SELECT 
        gt.type_id,
        g.group_name
    FROM user_groups ug
    JOIN groups g ON ug.group_id = g.group_id
    JOIN group_types gt ON g.type_id = gt.type_id
    WHERE ug.user_id = 123
) user_grps ON rgd.type_id = user_grps.type_id
-- 判断当前类型的规则是否匹配用户群组
WHERE 
    (rgd.match_value = '*' OR FIND_IN_SET(user_grps.group_name, rgd.match_value) > 0)
-- 确保规则组覆盖所有类型且全部匹配
GROUP BY m.msg_id, mtr.rule_id
HAVING COUNT(DISTINCT rgd.type_id) = (SELECT COUNT(*) FROM group_types);

关键说明 #

• FIND_IN_SET用于判断用户群组是否在规则的可选列表中（比如A1是否在A1,A2中）
• HAVING子句确保规则组覆盖所有类型，且每个类型的规则都匹配成功
• DISTINCT避免同一条消息被多个匹配规则组重复返回

四、优化建议 #

• 给rule_group_details的type_id和match_value建立联合索引，提升匹配速度
• 如果群组类型数量固定，可将规则明细改为宽表（每个类型一列），但扩展性较差，适合类型少且固定的场景
• 缓存频繁查询用户的各类型群组信息，减少关联查询开销

内容的提问来源于stack exchange，提问作者NiwdEE