调用MySQL存储过程后会话变量为NULL的问题求助

我编写了一个用于设置数据库会话变量的MySQL存储过程NEWWEEK：

CREATE PROCEDURE NEWWEEK()
BEGIN
    SET @FECHA = (date_sub(curdate(), interval WEEKDAY(curdate()) DAY));
    SET @WEEKNAME = REGEXP_REPLACE(concat('w', @FECHA), '[^0-9a-zA-Z ]', '');
    INSERT INTO ARCHIVO(FECHA) VALUES (@FECHA);
    SET @ARCHIVOID = (SELECT ID FROM ARCHIVO WHERE FECHA = @FECHA);
END//

在MySQL终端中调用该存储过程后，查询会话变量能得到正确值：

mysql> SELECT @FECHA, @WEEKNAME, @ARCHIVOID;
+------------+-----------+------------+
| @FECHA     | @WEEKNAME | @ARCHIVOID |
+------------+-----------+------------+   /*From Terminal */
| 2022-09-26 | w20220926 |          1 |
+------------+-----------+------------+
1 row in set (0,00 sec)

但通过PHP的MySQLi调用该存储过程时，虽然存储过程执行正常（ARCHIVO表成功新增行），但在终端查询这些会话变量却全部为NULL：
PHP代码：

$sql = $conn->query("Select * from ARCHIVO where FECHA BETWEEN DATE_SUB(now(), INTERVAL 1 WEEK) AND now()");
$row = $sql->fetch_assoc();
if($sql->num_rows < 1){
    $conn->query("CALL NEWWEEK()");
}

终端查询结果：

mysql> SELECT @FECHA, @WEEKNAME, @ARCHIVOID;
+----------------+----------------------+------------------------+
| @FECHA         | @WEEKNAME            | @ARCHIVOID             |
+----------------+----------------------+------------------------+    /* PHP */
| NULL           | NULL                 | NULL                   |
+----------------+----------------------+------------------------+
1 row in set (0,00 sec)

原因分析 #

• MySQL中@开头的会话变量是绑定到当前数据库连接会话的，PHP的MySQLi连接和终端使用的是完全独立的会话，彼此的变量互不干扰。
• PHP通常使用短连接，请求执行完毕后会自动关闭连接，存储过程设置的会话变量会随着连接关闭而销毁，终端自然查不到。

解决办法 #

方法1：在PHP代码内直接查询会话变量 #

既然变量绑定到PHP的连接会话，就在调用存储过程后立即在当前连接中查询变量，而不是去终端查看：

$sql = $conn->query("Select * from ARCHIVO where FECHA BETWEEN DATE_SUB(now(), INTERVAL 1 WEEK) AND now()");
$row = $sql->fetch_assoc();
if($sql->num_rows < 1){
    $conn->query("CALL NEWWEEK()");
    // 在当前连接会话内查询变量
    $varResult = $conn->query("SELECT @FECHA, @WEEKNAME, @ARCHIVOID");
    $vars = $varResult->fetch_assoc();
    // 输出或使用变量
    print_r($vars);
}

方法2：修改存储过程，直接返回结果集 #

依赖会话变量不够可靠，更稳妥的方式是让存储过程直接返回需要的值：

CREATE PROCEDURE NEWWEEK()
BEGIN
    SET @FECHA = (date_sub(curdate(), interval WEEKDAY(curdate()) DAY));
    SET @WEEKNAME = REGEXP_REPLACE(concat('w', @FECHA), '[^0-9a-zA-Z ]', '');
    INSERT INTO ARCHIVO(FECHA) VALUES (@FECHA);
    SET @ARCHIVOID = (SELECT ID FROM ARCHIVO WHERE FECHA = @FECHA);
    -- 直接返回变量结果
    SELECT @FECHA AS FECHA, @WEEKNAME AS WEEKNAME, @ARCHIVOID AS ARCHIVOID;
END//

然后在PHP中调用并获取结果：

$sql = $conn->query("Select * from ARCHIVO where FECHA BETWEEN DATE_SUB(now(), INTERVAL 1 WEEK) AND now()");
$row = $sql->fetch_assoc();
if($sql->num_rows < 1){
    $procResult = $conn->query("CALL NEWWEEK()");
    $vars = $procResult->fetch_assoc();
    print_r($vars);
    // 处理存储过程可能产生的多结果集
    $conn->next_result();
}

方法3：保持会话一致性（不推荐） #

如果一定要在终端查看变量，需要让PHP和终端使用同一个数据库连接，但这在Web应用场景下几乎无法实现，因为PHP的短连接特性会导致请求结束后连接关闭，所以不推荐这种方式。

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