Ao restaurar ou recriar uma tabela, não basta recuperar as informações que aparecem na tela. Outras tabelas apontam para cada linha pelo identificador dela, e um identificador trocado faz um item de venda apontar para a venda errada. A chave primária é o que dá a cada linha essa identidade, e preservá-la é uma condição de qualquer manutenção.
A chave primária
A chave primária é a coluna, ou o conjunto de colunas, que identifica cada linha de forma única. Uma tabela tem uma única chave primária, e a coluna da chave não aceita valores repetidos nem a ausência de valor. Na tabela vendas, a chave primária é a coluna id. O comando SHOW CREATE TABLE vendas; mostra essa definição na linha PRIMARY KEY.
O identificador é um valor guardado na própria linha. Ele não indica a posição da linha no disco, e uma reorganização da tabela pode mudar a posição de armazenamento sem mudar o identificador.
Uma informação do negócio nem sempre serve como identidade. O número do documento, por exemplo, pode se repetir entre caixas diferentes, e o documento 4521 pode existir no caixa 01 e no caixa 02. Por isso, muitas tabelas usam um identificador sem significado de negócio, criado apenas para distinguir as linhas.
Uma gravação com um identificador que já existe é recusada com o erro 1062, de entrada duplicada.
A numeração automática
A opção AUTO_INCREMENT faz o MySQL gerar o próximo número quando uma linha é incluída sem um identificador informado. Dois comandos acompanham essa numeração:
SHOW CREATE TABLE vendas;: mostra, no fim da definição, depois da palavra AUTO_INCREMENT, o próximo número que será gerado;SELECT LAST_INSERT_ID();: mostra o último número gerado pela própria conexão.
O campo Auto_increment do comando SHOW TABLE STATUS mostra o mesmo contador. A partir do MySQL 8, porém, esse campo pode trazer um valor guardado pelo servidor há até 24 horas, e o comando SET SESSION information_schema_stats_expiry = 0; faz a conexão ler o valor atual.
A numeração não é contínua. Em geral, os números de linhas apagadas não voltam a ser usados. No InnoDB, uma gravação desfeita ou uma tentativa que falhou também consome números. No MyISAM, que não desfaz gravações, uma tentativa recusada não consome número, mas uma inclusão com um identificador maior que o próximo número ou um ajuste no contador também abre lacunas. Por isso, um salto na sequência dos identificadores não prova, sozinho, que alguma linha foi apagada.
O comportamento do contador depende da versão. Até o MySQL 5.7, o InnoDB guarda o contador apenas na memória e, depois de um reinício, recalcula o próximo número a partir do maior identificador existente. Se as últimas linhas tinham sido apagadas, os números delas podem voltar a ser gerados. A partir do MySQL 8, o contador é preservado entre reinícios. No engine MyISAM, o contador fica no cabeçalho do arquivo de índices e pode se perder em alguns reparos.
Como o InnoDB usa a chave primária
O InnoDB organiza as linhas da tabela pela chave primária. Quando a tabela não tem chave primária declarada, o InnoDB usa o primeiro índice de valores únicos cujas colunas não aceitem a ausência de valor. Quando nem esse índice existe, o InnoDB cria um identificador interno, invisível nas consultas.
Preservar a identidade
A tabela itens_venda guarda, na coluna venda_id, o identificador da venda de cada item. Se as vendas forem inseridas de novo sem a coluna id, o MySQL gera identificadores novos, e os itens passam a apontar para vendas erradas ou inexistentes. Por isso, a coluna da chave primária precisa constar da lista de colunas em uma restauração ou cópia:
INSERT INTO vendas (id, numero_documento, data_venda, caixa, total) SELECT id, numero_documento, data_venda, caixa, total FROM vendas_copia;: copia as vendas mantendo os identificadores originais.
Antes de uma manutenção, convém anotar o maior identificador e o próximo valor do contador:
SELECT COUNT(*), MAX(id) FROM vendas;: mostra a quantidade de linhas e o maior identificador presente;SHOW CREATE TABLE vendas;: mostra o próximo valor do contador.
O próximo valor pode ser bem maior que o maior identificador presente, porque as linhas com os números mais altos podem ter sido apagadas. O contador não deve ser reduzido para o maior identificador mais um, porque os números intermediários podem continuar citados em outras tabelas, em registros do sistema ou nos dados já enviados à matriz. O comando ALTER TABLE vendas AUTO_INCREMENT = 2001; restabelece um valor conhecido, e o número 2001 representa o valor anotado antes da manutenção.
No suporte
Antes de recriar ou restaurar uma tabela, convém anotar a definição da chave primária, o maior identificador e o próximo valor do contador. Uma contagem igual de linhas não comprova que as identidades foram preservadas. Renumerar identificadores exige conhecer todos os lugares que os utilizam.
Para guardar
- A chave primária identifica cada linha de forma única e não é a posição da linha no disco.
- O AUTO_INCREMENT gera os identificadores, e a sequência tem lacunas normais.
- Até o MySQL 5.7, o contador do InnoDB pode voltar a gerar números apagados depois de um reinício.
- Uma restauração precisa manter a coluna da chave primária para preservar as referências.
- O contador não deve ser reduzido para o maior identificador presente mais um.
Uma tabela foi recriada, e as linhas foram inseridas de novo sem a coluna id. Qual é o risco?
O MySQL gera identificadores novos para as linhas. As tabelas que guardam os identificadores antigos, como itens_venda, passam a apontar para vendas erradas ou inexistentes. Recuperar as colunas visíveis não corrige essas referências.
Os identificadores das vendas pulam do 1050 para o 1053. Isso prova que duas vendas foram apagadas?
Não. No InnoDB, o AUTO_INCREMENT também consome números em gravações desfeitas e em tentativas que falharam, e as lacunas são normais. No MyISAM, as lacunas são menos comuns, mas também surgem de inclusões com o identificador informado e de ajustes no contador. Uma exclusão só se confirma com outras evidências, como os registros do sistema ou uma cópia anterior da tabela.