MySQL AUTO_INCREMENT com exemplos

O que é incremento automático?

O incremento automático é uma função que opera em tipos de dados numéricos. Ele gera automaticamente valores numéricos sequenciais toda vez que um registro é inserido em uma tabela para um campo definido como incremento automático.

O atributo funciona com qualquer tipo inteiro, de TINYINT a BIGINT. A coluna também deve ser indexada, o que acontece automaticamente quando ela é declarada como chave primária.

Quando usar incremento automático?

Na lição sobre normalização de banco de dadosAnalisamos como os dados podem ser armazenados com redundância mínima, armazenando-os em muitas tabelas pequenas, relacionadas entre si por meio de chaves primárias e estrangeiras.

Uma chave primária deve ser única, pois identifica exclusivamente uma linha em um banco de dados. Mas como podemos garantir que a chave primária seja sempre única?

Uma das possíveis soluções seria usar uma fórmula para gerar a chave primária, que verificaria a existência da chave na tabela antes de adicionar dados. Isso pode funcionar, mas a abordagem é complexa e não infalível. Duas sessões inserindo dados ao mesmo tempo ainda podem ler o mesmo valor máximo e entrar em conflito.

Para evitar tal complexidade e garantir que a chave primária seja sempre única, podemos usar o MySQL O recurso de incremento automático é usado para gerar chaves primárias. O incremento automático é utilizado com o tipo de dados INT. O tipo de dados INT suporta valores com e sem sinal. Os tipos de dados sem sinal só podem conter números positivos. Como boa prática, recomenda-se definir a restrição de não sinal na chave primária de incremento automático.

Sintaxe de incremento automático

Com o raciocínio esclarecido, observe o script usado para criar a tabela de categorias de filmes.

CREATE TABLE `categories` (
  `category_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_name` varchar(150) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
);

Observe o “AUTO_INCREMENT” no campo category_id. Isso faz com que o ID da categoria seja gerado automaticamente sempre que uma nova linha for inserida na tabela. Ele não é fornecido ao inserir dados na tabela. MySQL gera isso.

Observação: A palavra-chave UNSIGNED duplica o intervalo positivo da coluna, e a largura de exibição, uma vez escrita como int(11), está obsoleta. MySQL 8.0.17 em diante. Simples int é a forma atual.

Por padrão, o valor inicial de AUTO_INCREMENT é 1 e será incrementado em 1 para cada novo registro.

Vamos examinar o conteúdo atual da tabela de categorias.

SELECT * FROM `categories`;

Executando o script acima em MySQL A análise do Workbench com o banco de dados myflixdb nos fornece os seguintes resultados.

category_id
category_name
remarks




1
Comedy
Movies with humour


2
Romantic
Love stories


3
Epic
Story acient movies


4
Horror
NULL


5
Science Fiction
NULL


6
Thriller
NULL


7
Action
NULL


8
Romantic Comedy
NULL

Existem oito linhas, portanto o próximo ID gerado deve ser 9. Agora, vamos inserir uma nova categoria na tabela de categorias, fornecendo apenas o nome.

INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');

Executando o script acima no myflixdb em MySQL bancada nos dá os seguintes resultados mostrados abaixo.

category_id
category_name
remarks




1
Comedy
Movies with humour


2
Romantic
Love stories


3
Epic
Story acient movies


4
Horror
NULL


5
Science Fiction
NULL


6
Thriller
NULL


7
Action
NULL


8
Romantic Comedy
NULL


9
Cartoons
NULL

Note que não fornecemos o ID da categoria. MySQL Foi gerado automaticamente, porque o ID da categoria está definido como de incremento automático.

Se você deseja obter o último ID de inserção gerado por MySQL, você pode usar a função LAST_INSERT_ID para fazer isso. O script mostrado abaixo obtém o último id gerado.

SELECT LAST_INSERT_ID();

A execução do script acima retorna o último número de incremento automático gerado pela consulta INSERT. Os resultados são mostrados abaixo.

Dica: A função LAST_INSERT_ID() tem escopo restrito à sua própria conexão, portanto, um valor gerado pela inserção de outro usuário nunca poderá ser retornado a você por engano.

Como definir ou redefinir o valor inicial do AUTO_INCREMENT

A sequência padrão começa em 1, mas nem sempre isso é o que um projeto precisa. Os números de fatura podem ter que ser mantidos de um sistema antigo, e uma tabela de teste geralmente precisa ser reinicializada. MySQL expõe o contador diretamente, portanto, ambos os casos são tratados com uma única cláusula. Siga estas etapas para controlar o número inicial.

1. Defina o valor no momento da criação. Adicione a cláusula AUTO_INCREMENT à instrução CREATE TABLE. A primeira linha inserida receberá esse número em vez de 1.
2. Alterar o valor em uma tabela existente. Uso ALTERAR A TABELA com a mesma cláusula. MySQL Aceita o novo número somente se ele for maior que o maior identificador atualmente armazenado.
3. Reinicie uma tabela que você tenha esvaziado. O comando TRUNCATE TABLE remove todas as linhas e retorna o contador para 1 em uma única operação, algo que o comando DELETE sozinho não faz.
4. Confirme a alteração. Insira uma linha e leia o identificador de volta com LAST_INSERT_ID() antes de confiar na nova sequência.

-- Start a brand-new table at 1000
CREATE TABLE `invoices` (
  `invoice_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `amount` decimal(10,2),
  PRIMARY KEY (`invoice_id`)
) AUTO_INCREMENT = 1000;

-- Move the counter on an existing table
ALTER TABLE `categories` AUTO_INCREMENT = 100;

-- Empty the table and reset the counter to 1
TRUNCATE TABLE `categories`;

O tamanho do passo também pode ser alterado com a variável de sistema `auto_increment_increment`, mas ela se aplica a todo o servidor, e não a uma tabela específica. É usada principalmente em replicação, onde dois servidores não devem gerar o mesmo identificador.

Por que aparecem lacunas em uma sequência AUTO_INCREMENT?

Cedo ou tarde, uma tabela exibirá identificadores como 1, 2, 5, 6. Nada está quebrado. O contador foi projetado para garantir a unicidade, não para garantir uma sequência ininterrupta de números, e nunca emite o mesmo valor duas vezes.

As lacunas surgem pelos seguintes motivos.

• Linhas excluídas: Quando uma linha é excluída de uma tabela, seu ID autoincrementado não é reutilizado. MySQL continua gerando novos números sequencialmente.
• Transações revertidas: O número é utilizado no momento da inserção. Se a transação for revertida, a linha desaparece, mas o número já terá sido gasto.
• Inserções com falha: Uma declaração rejeitada por uma restrição UNIQUE ainda pode consumir um identificador antes de falhar.
• Inserções a granel: O InnoDB pode reservar um bloco de números para uma inserção de várias linhas e descartar aqueles que não utiliza.

Tentar preencher essas lacunas é um erro. Renumerar as linhas quebra todas as chaves estrangeiras que apontam para elas, e o valor em si não tem significado para o negócio. Se um relatório precisa de uma lista contínua, gere o número da linha na consulta em vez de sobrescrever os dados armazenados.

Perguntas Frequentes