Utilizando Variáveis definidas pelo usuário no MySQL

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

6 comentários sobre “Utilizando Variáveis definidas pelo usuário no MySQL

  1. Boa solução.
    to chegando agora no seu blog através do ajaxonline (tb to de colunista lá).

    Tava precisando ler uns conteúdos destes hehe 🙂

  2. Olá Amigo.

    Beleza. Porém, como fazer pra usar esta variável dentro do WHERE ?

    Estou querendo usar isso pq já nao aguento mais ficar repetindo as subqueries (faço na declaração e depois na crítica).

    Por exemplo:

    Atualmente ta assim:
    select (select max(pg.pagamento) from pagam as pg where ven.codigo = pg.codigo) as PAGAMENTO, ven.codigo as CODIGO where (select max(pg.pagamento) from pagam as pg where ven.codigo = pg.codigo) is not null

    O que eu gostaria:

    select (select max(pg.pagamento) from pagam as pg where ven.codigo = pg.codigo) as PAGAMENTO, ven.codigo as CODIGO where PAGAMENTO is not null

    Entendeu ?

    Gostaria de encher o campo PAGAMENTO como se fosse uma variável. Não posso usar a cláusula HAVING pois ela interfere nas funções SUM, MAX, etc… Este foi apenas um exemplo do que preciso. Porem este campo/variável que for preenchido tem que poder ser comparada no where.

    Se puder me dar uma força, ficarei grato

    Obrigado

  3. Amigo, estou precisando de uma ajuda, preciso utilizar variáveis no mysql, sendo um dos motivos este auto incremento, só que tem um porém, até onde li, o mysql não aceita essas variáveis dentro de uma view por exemplo, aí pensei em fazer uma função e chamá-la de dentro da view, como faria isso?!

    Agradeço desde já a ajuda e abraços!

  4. Tenho um problema mais simples ,queria evitar que fossem inseridos dados no meu cadastro de fornecedores em duplicidade, então dois campos um cod auto increment primari key e outro cnpj tambem primary key atraves do campo cnpj queria evitar o cadastro de empresas em duplicidade como faço isso?

  5. Rodrigo, põe o campo cnpj como unique, assim so pode conter um unico cnpj em sua tabela fornecedor.

  6. Ola Rafael

    Essa SQL funciona ?
    UPDATE table1 SET table1field = (SELECT MAX(table2.table2field) FROM table2 WHERE table1.table1field = table2.table2field)

    obrigado

Os comentários estão desativados.