MySQL创建BEFORE INSERT触发器将Video表链接转iframe及解决语法报错

之前语句报错的核心原因 #

• 语法不符合MariaDB规范：行级触发器没有FOR ROW后跟WHERE子句筛选max(ID)的写法，标准行级触发器固定语法为FOR EACH ROW，操作当前触发记录直接用NEW关键字引用即可，额外套max(ID)子查询不仅语法报错，还会在并发插入场景下出现取值错乱，把其他用户提交的记录误修改。
• 逻辑时序不合理：如果使用AFTER INSERT时机触发，记录已经完成落库，需要额外执行UPDATE语句才能修改字段值，性能损耗高还可能触发其他更新类联动逻辑，完全没必要。
• 语句嵌套错误：把两个独立的CREATE TRIGGER语句嵌套写在同一个触发器定义块里，和把两个建表语句塞到一个建表流程里没有区别，必然触发#1064语法错误。
• 字符串拼接方式错误：MariaDB中字符串拼接需要用标准函数CONCAT()，直接用+号操作字符串会隐式转为数值运算，根本得不到预期的拼接结果。

可直接运行的正确触发器实现 #

注意：创建触发器前必须临时修改客户端的语句结束符，否则触发器内部的分号会被识别为语句结束，导致创建失败

-- 临时将语句结束符改为//，规避分号冲突
DELIMITER //

CREATE TRIGGER video_link_auto_embed
BEFORE INSERT ON video
FOR EACH ROW
BEGIN
    DECLARE cleaned_url VARCHAR(1024);

    -- 自动补全链接协议，避免iframe加载相对路径失效
    IF NEW.LINK NOT LIKE 'http://%'
        AND NEW.LINK NOT LIKE 'https://%'
        AND NEW.LINK != '' THEN
        SET cleaned_url = CONCAT('https://', TRIM(NEW.LINK));
    ELSE
        SET cleaned_url = TRIM(NEW.LINK);
    END IF;

    -- 落库前直接修改待插入的字段值
    -- 若需要保留用户输入的原始Link，可将赋值目标改为embed_code字段
    SET NEW.LINK = CONCAT('<iframe src="', cleaned_url, '"></iframe>');
END //

-- 恢复默认的分号语句结束符
DELIMITER ;

验证方式 #

创建完成后可执行测试插入语句，检查字段值是否符合预期：

INSERT INTO video(link) VALUES('www.google.com');
SELECT link FROM video ORDER BY id DESC LIMIT 1;

预期返回值为<iframe src="https://www.google.com"></iframe>

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