MySQL插入触发器问题：自动填充Links表的Siteid字段

问题分析 #

你之前写的AFTER INSERT触发器存在两个核心问题：

• MySQL中，AFTER INSERT触发器里直接更新触发表（links）会引发死锁或权限限制，因为此时插入操作还未完成，表处于锁定状态
• 触发器逻辑错误：SELECT id FROM sites会取出所有站点ID，REGEXP(SELECT sitename FROM sites)会匹配任意站点名称，无法实现一对一的对应匹配

正确实现方案 #

改用BEFORE INSERT触发器，在插入数据之前直接给NEW.siteid赋值，无需更新表，完美规避无法访问插入表的问题。

触发器代码

DELIMITER //
CREATE TRIGGER auto_fill_siteid BEFORE INSERT ON links
FOR EACH ROW
BEGIN
    -- 匹配Article_link以Sitename开头的站点ID，赋值给Siteid
    SELECT id INTO NEW.siteid 
    FROM sites
    WHERE NEW.article_link LIKE CONCAT(sitename, '%')
    LIMIT 1; -- 若存在多个匹配的站点，取第一个（可根据业务需求调整）
END //
DELIMITER ;

逻辑说明 #

• BEFORE INSERT触发器在数据插入到links表之前执行，直接操作NEW对象（代表即将插入的行），给NEW.siteid赋值
• NEW.article_link LIKE CONCAT(sitename, '%')：匹配文章链接以站点地址开头的记录，比如示例中https://stackoverflow.com/questions/ask会匹配到sitename为https://stackoverflow.com/的站点
• LIMIT 1：防止同一文章链接匹配到多个站点的情况，可根据实际业务规则调整（比如优先匹配最长的站点地址）

测试验证 #

当你向links表插入(1, 'https://stackoverflow.com/questions/ask')时，触发器会自动从sites表找到id为3的记录，将NEW.siteid设为3，最终插入的行就是(1, 3, 'https://stackoverflow.com/questions/ask')，符合你的需求。

补充说明 #

如果需要处理无匹配站点的情况，可以在触发器中添加默认值逻辑，比如：

DELIMITER //
CREATE TRIGGER auto_fill_siteid BEFORE INSERT ON links
FOR EACH ROW
BEGIN
    SELECT id INTO NEW.siteid 
    FROM sites
    WHERE NEW.article_link LIKE CONCAT(sitename, '%')
    LIMIT 1;
    
    -- 若没有匹配的站点，设置默认Siteid为0（可自定义）
    IF NEW.siteid IS NULL THEN
        SET NEW.siteid = 0;
    END IF;
END //
DELIMITER ;

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