MySQL Declare Exit Handler statement-CodePudding

SELECT @x returns 1, when it should return 2. WHY? *Notice the DECLARE EXIT HANDLER

-- Paso 1
-- DROP DATABASE IF EXISTS test;
-- CREATE DATABASE test;
USE test;
-- Paso 2
CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
-- Paso 3
DELIMITER $$
CREATE PROCEDURE handlerdemo ()
   BEGIN
      DECLARE EXIT HANDLER FOR SQLSTATE '23000'
      SET @x = 1;
      SELECT @x;
      INSERT INTO test.t VALUES (1);
      SET @x = 2;
      SELECT @x;
      INSERT INTO test.t VALUES (1);
      SET @x = 3;
   END
$$
DELIMITER ;
CALL handlerdemo();
SELECT @x;

https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html

"Notice that @x is 3 after the procedure executes, which shows that execution continued to the end of the procedure after the error occurred. If the DECLARE ... HANDLER statement had not been present, MySQL would have taken the default action (EXIT) after the second INSERT failed due to the PRIMARY KEY constraint, and SELECT @x would have returned 2."

CodePudding user response：

Process flow:

CALL handlerdemo();

--      DECLARE EXIT HANDLER FOR SQLSTATE '23000'
--      SET @x = 1;
-- DECLARE, none executed

      SELECT @x;
-- output: NULL

      INSERT INTO test.t VALUES (1);
-- table contains 1 row

      SET @x = 2;
-- variable is set to 2

      SELECT @x;
-- output: 2

      INSERT INTO test.t VALUES (1);
-- duplicate error, handler call

--      DECLARE EXIT HANDLER FOR SQLSTATE '23000'
      SET @x = 1;
-- variable is set to 1
--      EXIT stored procedure

SELECT @x;
-- output: 1