MySQL5.6下创建带全文搜索的person表更新触发器实现咨询

前置说明 #

MySQL 5.6 存在限制：不允许在AFTER触发器中对触发触发器的同一张表执行DML操作，否则会报1442错误，因此我们用BEFORE UPDATE触发器直接修改NEW对象的字段值，无需额外执行UPDATE语句，同时创建一个辅助表解决单个字符串的全文匹配得分计算问题。

步骤1：创建计算全文得分的辅助表 #

该表仅用来临时存储待计算的字符串，获取其自身的全文匹配得分：

CREATE TABLE ft_score_helper (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(512),
    FULLTEXT KEY idx_ft_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入固定id的占位记录，后续仅更新该条即可
INSERT INTO ft_score_helper(id, name) VALUES (1, '');

步骤2：创建UPDATE触发器 #

DELIMITER //
CREATE TRIGGER trg_person_update_name BEFORE UPDATE ON person
FOR EACH ROW
BEGIN
    -- 仅当name字段被更新时才执行逻辑
    IF OLD.name <> NEW.name THEN
        DECLARE _new_name VARCHAR(512);
        DECLARE _self_score FLOAT DEFAULT 0;
        DECLARE _id_most_relevant INT DEFAULT NULL;
        
        SET _new_name = NEW.name;
        -- 更新辅助表，计算新name的自身全文得分
        UPDATE ft_score_helper SET name = _new_name WHERE id = 1;
        SELECT MATCH(name) AGAINST(_new_name) INTO _self_score FROM ft_score_helper WHERE id = 1;
        
        -- 排除当前行，查询相似度大于95%的其他记录，取得分最高的第一条
        SELECT id INTO _id_most_relevant 
        FROM person
        WHERE id <> NEW.id
          AND MATCH(name) AGAINST(_new_name) / _self_score > 0.95
        ORDER BY MATCH(name) AGAINST(_new_name) DESC
        LIMIT 1;
        
        -- 直接赋值给NEW字段，无需额外UPDATE当前表
        SET NEW.id_most_relevant = _id_most_relevant;
    END IF;
END //
DELIMITER ;

注意事项 #

• 如果没有符合相似度要求的记录，id_most_relevant会自动设为NULL，符合预期
• 若存储的是中文内容，需要提前配置InnoDB全文索引使用ngram分词器，同时调整最小词长参数
• 全文匹配得分基于全表词频计算，新增记录不会自动更新老记录的id_most_relevant，仅更新当前行时会刷新值

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