PHP8.1+MySQL5.7存储过程错误处理器失效异常排查

我编写了一个MySQL存储过程insert_profilo_staff，用于向两张表插入数据，为避免重复键冲突，针对错误码1062（重复键）设置了EXIT HANDLER，执行ROLLBACK并抛出自定义SQL信号。但使用PHP 8.1的mysqli在try-catch块中调用该过程时，即使插入不存在的记录，也总是触发catch块中的异常，请问问题出在哪里？

存储过程代码 #

DELIMITER $$
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `insert_profilo_staff`(IN `id_persona` INT(11) UNSIGNED, IN `id_staff` VARCHAR(15) CHARSET utf8, IN `id_ruolo` VARCHAR(30) CHARSET utf8, IN `id_zona` VARCHAR(15) CHARSET utf8, IN `id_territorio` VARCHAR(15) CHARSET utf8, IN `gerarchia` INT(5) UNSIGNED, IN `ambito_giurisdizionale` VARCHAR(15) CHARSET utf8)
    MODIFIES SQL DATA
BEGIN
 
  
  DECLARE EXIT HANDLER FOR 1062 ROLLBACK;
  BEGIN
     SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = "Il profilo che si sta cercando di inserire e' già associato all'utente.";

  START TRANSACTION; 
  
  IF gerarchia < 10000 THEN
    
      INSERT INTO tb_utenti_profili SET 
        id = id_persona,
        staff = id_staff, 
        ruolo = id_ruolo, 
        id_zona = id_zona;
        
     ELSE
     
      INSERT INTO tb_utenti_profili SET 
        id = id_persona,
        staff = id_staff, 
        ruolo = id_ruolo, 
        id_zona = id_zona;        
        
       INSERT INTO tb_utenti_territori_supervisori SET 
        id = id_persona,
        staff = id_staff, 
        id_territorio = id_territorio; 
        
     END IF;    
      -- SET @erroreOut = '00000';
     COMMIT;
     
   END;   
END$$
DELIMITER;

PHP调用代码 #

$db_connection = mysqli_connect($db_server, $db_username, $db_password, $db_name);
$is_exception = 0;

try {
    $query = "CALL insert_profilo_staff( ?, ?, ?, ?, ?, ?, ? ) ";
    $stmt = $db_connection->prepare($query);
    $stmt->bind_param("issssis", $inp_id_persona, $inp_id_staff, $inp_id_ruolo, $inp_id_zona, $inp_id_territorio, $inp_gerarchia, $inp_ambito_giurisdizionale);

    $inp_id_persona =  intval(trim($_POST['id_persona'] ?? ''));
    $inp_id_staff = trim($_POST['id_staff'] ?? '');
    $inp_id_ruolo = trim($_POST['id_ruolo'] ?? '');
    $inp_id_zona = trim($_POST['id_zona'] ?? '');
    $inp_id_territorio = trim($_POST['id_territorio'] ?? '');
    $inp_gerarchia = intval(trim($_POST['gerarchia'] ?? ''));
    $inp_ambito_giurisdizionale = trim($_SESSION['kaikan'] ?? '');

    $stmt->execute();
}
catch(Exception $e)
{
    error_log( "l eccezione: " . $e->getMessage() );
    $is_exception = 1;
    error_log( "Call procedure insert_profilo_staff failed ");
    error_log( "Stato errore: " . $db_connection->sqlstate . " ");
    error_log( "N.ro errore: ". $db_connection->errno . " ");
    error_log( "Errore: " . $db_connection->error . " ");

    $result = 'error';
    $message= "" . $db_connection->error . " ";
    $mysql_data = [];
}
finally {
    $stmt->close();
    $db_connection->close();
    $query = "";
}

if ($is_exception == 0)
{
    $result='success';
    $message='OK';
    $mysql_data = [];
}

$data = array(
    "result"  => $result,
    "message" => $message,
    "data"    => $mysql_data
    );

// Convert PHP array to JSON array
$json_data = json_encode($data);

问题分析与解决方案 #

核心问题：存储过程语法逻辑错误 #

1. DECLARE语句位置违规：MySQL要求存储过程中的DECLARE语句必须放在所有其他可执行语句的最顶部，原代码中DECLARE EXIT HANDLER之后直接添加了一个多余的BEGIN块，破坏了语法结构。
2. SIGNAL无条件执行：原代码中SIGNAL语句不在EXIT HANDLER的处理块内，而是直接放在外层，导致无论是否触发1062错误，这个异常信号都会被抛出，这就是PHP每次调用都会进入catch块的根本原因。
3. EXIT HANDLER定义不完整：原HANDLER只写了ROLLBACK，没有将异常抛出逻辑包含在内，无法正确处理重复键错误。

修正后的存储过程 #

DELIMITER $$
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `insert_profilo_staff`(IN `id_persona` INT(11) UNSIGNED, IN `id_staff` VARCHAR(15) CHARSET utf8, IN `id_ruolo` VARCHAR(30) CHARSET utf8, IN `id_zona` VARCHAR(15) CHARSET utf8, IN `id_territorio` VARCHAR(15) CHARSET utf8, IN `gerarchia` INT(5) UNSIGNED, IN `ambito_giurisdizionale` VARCHAR(15) CHARSET utf8)
    MODIFIES SQL DATA
BEGIN
    -- DECLARE必须放在所有语句最前面
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = "Il profilo che si sta cercando di inserire e' già associato all'utente.";
    END;

    START TRANSACTION; 
    
    IF gerarchia < 10000 THEN
        INSERT INTO tb_utenti_profili SET 
            id = id_persona,
            staff = id_staff, 
            ruolo = id_ruolo, 
            id_zona = id_zona;
    ELSE
        INSERT INTO tb_utenti_profili SET 
            id = id_persona,
            staff = id_staff, 
            ruolo = id_ruolo, 
            id_zona = id_zona;        
        INSERT INTO tb_utenti_territori_supervisori SET 
            id = id_persona,
            staff = id_staff, 
            id_territorio = id_territorio; 
    END IF;    
    COMMIT;
END$$
DELIMITER;

PHP代码优化点 #

1. 开启严格异常模式：默认mysqli不会主动抛出异常，添加以下代码可让数据库错误转为Exception，便于精准捕获：

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db_connection = mysqli_connect($db_server, $db_username, $db_password, $db_name);

2. 调整变量赋值顺序：原代码先绑定参数再赋值变量，属于错误写法，应先完成变量赋值再绑定：

// 先赋值所有变量
$inp_id_persona =  intval(trim($_POST['id_persona'] ?? ''));
$inp_id_staff = trim($_POST['id_staff'] ?? '');
$inp_id_ruolo = trim($_POST['id_ruolo'] ?? '');
$inp_id_zona = trim($_POST['id_zona'] ?? '');
$inp_id_territorio = trim($_POST['id_territorio'] ?? '');
$inp_gerarchia = intval(trim($_POST['gerarchia'] ?? ''));
$inp_ambito_giurisdizionale = trim($_SESSION['kaikan'] ?? '');

// 再准备语句并绑定参数
$stmt = $db_connection->prepare($query);
$stmt->bind_param("issssis", $inp_id_persona, $inp_id_staff, $inp_id_ruolo, $inp_id_zona, $inp_id_territorio, $inp_gerarchia, $inp_ambito_giurisdizionale);

总结 #

修正存储过程的语法结构后，只有当触发1062重复键错误时，才会执行ROLLBACK并抛出异常，PHP的catch块也只会在真正出现数据库错误时被触发。

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