MySQL触发器编写问题：messages表触发时如何插入同表新消息行

问题背景 #

现有两张表结构如下：

messages表结构 #

msg_id   outgoing_msg_id incoming_msg_id    msg_date          msg
501        10001000         123011111       a timestamps    "hello"

Queue表结构 #

queue_id    unique_id   msg_id
1          12310012      501

需求规则：customer角色用户给SERVICE角色发消息时，校验是否为当日首条消息，是则执行两个操作：1. 向Queue表插入新行；2. 向messages表插入SERVICE回复给该用户的自动消息。
现有触发器代码：

BEGIN
    DECLARE v_count int DEFAULT 0;
    
    SELECT COUNT(*) INTO v_count from messages inner join users on outgoing_msg_id = unique_id WHERE outgoing_msg_id = new.outgoing_msg_id and date(msg_date) = date(CURRENT_TIMESTAMP()) and role like "customer"
     GROUP by outgoing_msg_id;
     
     if v_count = 1 THEN -- if this is his first message
     INSERT into queue(unique_id) values(new.outgoing_msg_id);
     end if;
     

   -- insert new message from SERVICE to CUSTOMER to wait?!
END

当前问题：无法在messages表的触发器中插入同表的新消息行。

核心原因 #

MySQL原生限制：不允许在表的触发器（INSERT/UPDATE/DELETE）中直接对该表执行写操作，直接插入会触发报错Can't update table 'messages' in stored function/trigger because it is already used by statement which invoked this stored function/trigger，目的是避免无限递归触发触发器。

解决方法 #

方案1：迁移逻辑到存储过程（最稳妥） #

将消息插入、校验、自动回复的所有逻辑封装到存储过程中，通过事务保证操作原子性，放弃用触发器实现同表插入。
示例代码：

DELIMITER //
CREATE PROCEDURE send_customer_msg(
    IN p_outgoing_id INT,
    IN p_incoming_id INT,
    IN p_msg_content VARCHAR(255)
)
BEGIN
    DECLARE v_count INT DEFAULT 0;
    DECLARE v_service_id INT DEFAULT 替换为实际SERVICE角色的用户ID;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

    START TRANSACTION;
    -- 插入用户发送的消息
    INSERT INTO messages(outgoing_msg_id, incoming_msg_id, msg_date, msg) 
    VALUES(p_outgoing_id, p_incoming_id, NOW(), p_msg_content);

    -- 校验是否为当日首条消息
    SELECT COUNT(1) INTO v_count 
    FROM messages 
    INNER JOIN users ON outgoing_msg_id = unique_id
    WHERE outgoing_msg_id = p_outgoing_id 
    AND DATE(msg_date) = DATE(NOW())
    AND role = 'customer';

    IF v_count = 1 THEN
        -- 插入Queue表
        INSERT INTO queue(unique_id, msg_id) VALUES(p_outgoing_id, 2322727);
        -- 插入自动回复消息
        INSERT INTO messages(outgoing_msg_id, incoming_msg_id, msg_date, msg)
        VALUES(v_service_id, p_outgoing_id, NOW(), '你好，客服将很快为你服务');
    END IF;
    COMMIT;
END //
DELIMITER ;

后续用户发消息时直接调用该存储过程即可。

方案2：用中间表过渡（必须用触发器场景） #

1. 新增中间表存储待插入的自动回复消息

CREATE TABLE pending_auto_reply(
    id INT AUTO_INCREMENT PRIMARY KEY,
    outgoing_id INT,
    incoming_id INT,
    msg_content VARCHAR(255),
    create_time DATETIME
);

2. 修改原messages表的AFTER INSERT触发器，将待插入的自动回复写入中间表

BEGIN
    DECLARE v_count int DEFAULT 0;
    DECLARE v_service_id INT DEFAULT 替换为实际SERVICE角色的用户ID;
    
    -- 去掉GROUP BY避免无数据时返回NULL
    SELECT COUNT(1) INTO v_count 
    FROM messages 
    INNER JOIN users ON outgoing_msg_id = unique_id 
    WHERE outgoing_msg_id = new.outgoing_msg_id 
    AND date(msg_date) = date(CURRENT_TIMESTAMP()) 
    AND role = "customer";
     
    IF v_count = 1 THEN 
        INSERT into queue(unique_id, msg_id) values(new.outgoing_msg_id, new.msg_id);
        -- 自动回复写入中间表
        INSERT INTO pending_auto_reply(outgoing_id, incoming_id, msg_content, create_time)
        VALUES(v_service_id, new.outgoing_msg_id, '你好，客服将很快为你服务', NOW());
    END IF;
END

3. 新增MySQL事件定时消费中间表数据写入messages表

SET GLOBAL event_scheduler = ON;
CREATE EVENT event_process_auto_reply
ON SCHEDULE EVERY 1 SECOND
DO
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    START TRANSACTION;
    -- 批量插入未处理的自动回复
    INSERT INTO messages(outgoing_msg_id, incoming_msg_id, msg_date, msg)
    SELECT outgoing_id, incoming_id, create_time, msg_content 
    FROM pending_auto_reply;
    -- 清空已处理的中间表数据
    TRUNCATE TABLE pending_auto_reply;
    COMMIT;
END;

原有逻辑优化点 #

• 原COUNT查询带GROUP BY，当用户当日无消息时会返回NULL而非0，导致v_count=1的判断永远不触发，建议去掉GROUP BY直接查COUNT(1)
• 角色匹配建议用=而不是LIKE，避免模糊匹配导致的逻辑错误

内容的提问来源于stack exchange，提问作者EXE-CUTE