Tenho uma tabela com uma coluna de auto-incremento para a chave primária. Ao adicionar e/ou excluir registros o valor de auto incremento irá continuar a aumentar.
problema 1:
Se eu tiver 10 registros inseridos e depois deletar 9 dos 10 registros, o próximo valor a ser inserido será 11, não 9.
Solução:
Pode-se executar a seguinte query:
[sql]ALTER TABLE tablename AUTO_INCREMENT = 1[/sql]
Isso irá redefinir o valor do auto-incremento para o maior valor definido +1, assim, o valor de auto-incremento do próximo registro a ser inserido terá início a partir 9, no exemplo dado.
Pode-se utilizar também essa técnica, quando se deseja de fato limpar todos os registros da tabela e recomeçar o auto-incremento.
[sql]
DELETE FROM tablename
ALTER TABLE tablename AUTO_INCREMENT = 1
[/sql]
Problema 2:
Se eu tivesse entrado com 10 registros, e excluido alguns como o 4 e 5 e quero inserir próximo registro como 4 não 11.
Solução:
Execute a seguinte query:
[sql]SET insert_id = 4;
INSERT INTO tablename VALUES (‘blah’, ‘…’);[/sql]
Isto irá adicionar o próximo registro na posição 4.
SET insert_id = # (onde # é o valor do auto-incremento que você quer usar).
PS.: Só é eficaz para a inserções imediatas imediatas, uma por vez.
Muito bom, vai ajudar bastante a corrigir varios DB. Valeu Jonas, sempre trazendo coisa boa pra nós.
Caro Jonas, ótimo post. Porém vejo que você não mostrou como realmente resetar o AUTO_INCREMENT. Da forma que o fez, teria que excluir a tabela, e as vezes não é isso que as pessoas querem ou podem fazer. Muitos devem vir parar no seu post esperando ler como se faz para zerar o AI, ou seja, quando for inserir um novo registro na tabela, ele comece o ID do 1.
Acho que a forma mais eficaz seria a seguinte:
ALTER TABLE `tabela` DROP `id`;
ALTER TABLE `tabela` AUTO_INCREMENT = 1;
ALTER TABLE `tabela` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Dessa forma ele zera o campo ID, excluindo-o. É claro que se tiver registros, os IDs já inseridos não serão sobrescritos, então dependendo da situação vale a pena zerar os registros tbm.
Blz Rômulo, obrigado pelo comentário.
Agradeço a ajuda, mas, não consegui resolver o meu problema. Tenho uma tabela com 90 registros apenas. Por um erro, inseriram o id 17001, etc. Apaguei estes registros (>= 17000) e gostaria de resetar o autoincrement para o número 90.
Em SQL Server é fácil: DBCC CHECKIDENT(NOMEDATABELA, RESEED, 90), mas em MySQL não sei como.