Using the Handler function is skipping the ids. How do I make it not happen?

Question

Take 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 hangs correctly, until then everything ok. But when I insert a team that does not exist yet it inserts correctly, but skips the ids on the respective times I tried to insert brazil again and 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 insert "Mexico" it inserts correctly but in id = 6.

Answer 1

When an error occurs when trying to insert Mysql burns the id and arrow the autoincrement +1 you can check if you have an error in the execution and set the autoincrement by changing the table, but I do not think it is feasible, I believe that verifying what the next value to be inserted before performing the Insert is more viavél:

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 ;