The use of the handler function is skipping the ids. How do I prevent this from happening?

Question

The use of the handler function is skipping the ids. How do I prevent this from happening?

Navigation

#1 by (0 votes)

2

See the example:

create table time(
id int not null primary key auto_increment,
nome varchar(50) unique
)engine=innodb;

delimiter $$
CREATE PROCEDURE insertteam (nometime varchar(50))
begin
declare continue handler for 1062
select 'vc ja inseriu esse nome anteriormente';
insert into time (id, nome)
values(null, nometime);
end$$
delimiter ;

call insertteam ('BRASIL');
select * from time;

If I "call" and insert brazil again it locks up correctly, so everything is ok. But when I insert a team that does not yet exist, it inserts correctly, but skips the ids in the respective times that I tried to enter Brazil again and it went wrong.

For example: if I try to insert Brazil 5 times it inserts the first and the error in the others. But then when I enter "Mexico" it inserts correctly but not id = 6.

mysql handler

asked by anonymous 05.09.2017 / 21:39

1 answer

Angular 2 - Change the separator of the comma values to point How to make selections through the Workbench Diagrams?

score 0 · Answer 1

When an error occurs when trying to insert MySQL burns the id and arrow the autoincrement +1 you can check if it has error in the execution and set the autoincrement by changing the table, but I do not think it feasible, I believe that verifying which next value being inserted before performing the insert is more viable:

DROP PROCEDURE IF EXISTS inseretime;
DELIMITER |
CREATE PROCEDURE inseretime(nometime VARCHAR(50))
BEGIN

    DECLARE error INT DEFAULT 0;
    DECLARE v_autoincrement INT;
    DECLARE CONTINUE HANDLER FOR 1062 SET error = 1;

    SET @@autocommit = 0;

    START TRANSACTION;

        SELECT 
                MAX(id) + 1
        INTO v_autoincrement
        FROM time;
        INSERT INTO time VALUES(v_autoincrement, nometime);

        IF error = 1 THEN

            SELECT 'vc ja inseriu esse nome anteriormente';
            ROLLBACK;
            /** CASO QUEIRA ALTERAR O AUTO_INCREMENT DA TABELA **/
            -- ALTER TABLE tbl AUTO_INCREMENT = v_autoincrement;


        ELSE

            COMMIT;

        END IF;

END
|
DELIMITER ;