Que jogue a primeira pedra quem nunca errou na projeção de um banco! Uma vez ou outra em sua carreira você vai se ver frente a frente com um problema como este: uma tabela foi criada, devido ao seu formato e os dados não é necessária a presença de uma chave primária, ou é utilizada uma chave primária composta, abrangendo mais de um campo. Até então tudo ok, mas com novidades e um upgrade do sistema, você percebe a necessidade de criar um campo de auto-increment para ser sua chave primária, no meu caso para facilitar uma interface em AJAX.

E agora? Você tem uma tabela cheia de dados, que devido a Lei de Murphy você não pode apagar. Com isso o MySQL não deixa o campo ser adicionado/alterado como auto-increment e chave primária. Um labirinto e tanto, mas com algumas pesquisas e alguns neurônios fritos achei uma solução para rodear este problema.

ATUALIZAÇÃO: Fui informado de que havia uma falha na minha lógica e usando uma simples query o mesmo resultado pode ser obtido (eu havia tentado porém minha ferramenta de modelagem executava os comandos em ordem diferente por isso tive problemas), portanto vou coloca-la no final do artigo, mas materei este artigo como um bom exemplo de como se pode usar variaveis definidas pelo usuario no MySQL.

Primeiro criei um campo “id” na tabela que iria ser a minha futura chave primária, inicialmente configurei ele apenas como INT sem adicionar o atributo auto-increment ou registra-lo como chave primária. O valor padrão para o campo se tornou 0 (zero), e agora eu precisava transformar isso em uma seqüência de 1 à XXX (neste caso 10.300). Na mão seria impossível fazer esta tarefa então eu precisava encontrar uma solução que fizesse isso de forma automática.

Minha primeira idéia era de usar uma sub-query, onde para cada linha eu faria uma busca pelo valor mais alto e adicionaria 1 a este valor. Mas logo dei de cara com uma restrição no uso de sub-queries: o update e o select não podem usar a mesma tabela. De volta a mesa de rascunhos fui pesquisar mais a fundo as funções do MySQL e esbarrei com uma possibilidade, as variáveis.

Variáveis definidas pelo usuário (Manual MySQL) já estão disponíveis no MySQL a algum tempo, e permitem que valores sejam passados entre queries da mesma conexão, sem a necessidade de tabelas temporárias. Desta forma eu verifiquei que poderia utilizar uma variável para carregar o valor do maior ID de query em query.

@nome_variavel := valor

A sintaxe acima é a recomendada, o uso de := permite atribuir valores à variáveis fora de um comando SET. Agora só precisava definir uma query de forma a utilizar esta sintaxe e gerar minha coluna com uma contagem completa. Montei então a query abaixo:

SET @maxId= 0;
UPDATE minhatabela SET id = ( SELECT @maxId := @maxId+1 ) WHERE 1

Assim ao iniciar a execução a variável maxId recebe o valor 0 (zero) e a cada linha atualizada na tabela este valor sofre um incremento de um, e assim chego ao final com uma contagem completa de 1 até o numero de linhas da tabela. Agora sim posso converter o campo para auto-increment e defini-lo como chave primária.

As variáveis definidas pelo usuário possuem uma vasta utilidade que não se restringe a este exemplo que dei e devem ser tratadas como uma grande ferramenta na mão de um programador experiente. Espero ter mostrado um pouco da capacidade delas e ajudar com um problema que todos nós já devemos ter enfrentado uma vez na vida.

Se você deseja apenas criar a chave e não utilizar variavéis veja este código: (contribuição: balluche)

alter table mytable drop column id;
alter table mytable add id INT NOT NULL PRIMARY KEY auto_increment;

This post is also available in: Inglês