如何实现SQL触发器获取INSERT查询的额外user_id参数写入日志表

现有两张表shelfs（货架表）和shelfs_log（货架操作日志表），表结构如下：

CREATE TABLE `shelfs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `shelf_name` VARCHAR(50) NULL DEFAULT NULL
    `storage_id` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`) USING BTREE
);

CREATE TABLE `shelfs_log` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `shelf_name` VARCHAR(50) NULL DEFAULT NULL
    `storage_id` INT(11) NOT NULL,
    `user_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`) USING BTREE
);

原本通过以下PHP函数向shelfs表插入数据：

public static function createNewShelf($shelf_name, $storage_id)
{
    global $conn;
    $sql = "INSERT INTO shelfs (shelf_name, storage_id)
    VALUES ('$shelf_name', '$storage_id')";

    $result = $conn->query($sql);

    $newShelf = array();

    if ($result) {
        $newShelf['success'] = true;
    } else {
        $newShelf['success'] = false;
    }

    return $newShelf;

    $conn->close();
}

需求是：创建触发器，在向shelfs插入数据后，自动将相关数据写入shelfs_log表，同时要传递操作人的user_id，但该字段不需要写入shelfs表。

最初失败的触发器尝试 #

最初写的触发器无法实现需求，原因是NEW.user_id不存在（shelfs表没有这个字段）：

CREATE DEFINER=`root`@`localhost` TRIGGER `shelfs` 
AFTER INSERT ON `shelfs` 
FOR EACH ROW 
    INSERT INTO shelfs_log (shelf_name, storage_id, user_id) 
    VALUES (NEW.shelf_name, NEW.storage_id, NEW.user_id);

可行解决方案 #

通过MySQL会话变量传递user_id，分两步修改：

1. 修改PHP代码，新增user_id参数，使用multi_query()执行包含设置会话变量和插入操作的复合SQL：

public static function createNewShelf($shelf_name, $storage_id, $user_id)
{
    global $conn;
    // 先设置会话变量@user_id，再执行插入操作
    $sql = "SET @user_id := $user_id; INSERT INTO shelfs (shelf_name, storage_id) VALUES ('$shelf_name', '$storage_id');";
    
    // 用multi_query执行多语句
    $result = $conn->multi_query($sql);

    $newShelf = array();

    if ($result) {
        $newShelf['success'] = true;
    } else {
        $newShelf['success'] = false;
    }

    return $newShelf;

    $conn->close();
}

2. 修改触发器，从会话变量@user_id中获取操作人ID：

CREATE DEFINER=`root`@`localhost` TRIGGER `shelfs_after_insert` 
AFTER INSERT ON `shelfs` 
FOR EACH ROW 
    INSERT INTO shelfs_log (shelf_name, storage_id, user_id) 
    VALUES (NEW.shelf_name, NEW.storage_id, @user_id);

注意：会话变量@user_id仅在当前数据库连接会话中有效，确保插入操作和触发器执行在同一个会话内。

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