Using User-Defined Varibles in MySQL

  • MySQL
  • September 28, 2006

Cast the first stone he who never made a mistake modeling a database! Every now and then in your career you will be face to face with a problem like this: due to the nature of a table’s data you created a table without a primary key, or using a composed key. So far so good, but due to an upgrade you see the need to have a unique key identifying all the registers in your table, in my case it was due to a AJAX interface.

So what now? You have a table full of data, and of course, as Murphy’s law will tell you, that data cannot be erased. MySQL will prevent you from turning a filled to a primary key if it finds duplicated values in the table. Quite a brain twister, but I did a little research and found a rather simple solution to the matter.

UPDATE: So it actually came to my atention that a query I had already tried does the job in an even simpler form, but my modelling tool executed the commands out of sync and that why i had problems. So this article stays on as a good example of how to use mysql variables.

First up I created an “id” filed in my table to be my future primary key, initially it was configured to be only an INT field with no auto-increment attribute, or primary key qualification. The default value for the field became 0 (zero), and now all I needed to do was populate that field with a sequence from 1 to XX (10.300 in my case). Doing this manually would be rather troublesome so I needed to find an automatic solution.

My first idea was to use sub-queries, where the update for each register would seek the highest value for ID and increment it by one. But soon I found a flaw to my idea, as sub-queries do not accept the update and select targets as the same table. So I went back to my drawing boars to find a new solution, and soon I found a possible solution, MySQL variables.

User defined MySQL variables (MySQL Reference) have been around for quite a while and allow you to transfer values form query to query (of the same connection) with out the use of temporary tables. So I figured out I could use a variable to store the value of the highest ID and keep incrementing it in each update query.

@var\_name := value

The recommended syntax shown above uses := to set the value of variables because this syntax can be used inside ou outside of a SET command. So now I needed to define a query to use this feature and complete my count, so I built this query:

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

When the query begins it sets maxId to zero and with every execution it increments that variable coming to a full count at the end of its execution

Now all I have to do is set the auto-increment attribute and define the column as my primary key.

User defined variables have innumerous other possibilities and uses, and should be considered a useful tool for every programmer. So I hope I showed you a bit of their capacity and how they can solve a simple problem we will all face someday.

To add auto-increment primary key values used this code below: (contribution by balluche)

alter table mytable drop column id; alter table mytable add id INT NOT NULL PRIMARY KEY auto\_increment;
Tags:
comments powered by Disqus

Related Posts

Installing Composer Packages

Installing Composer Packages

  • October 13, 2014

I have been putting together a new talk about Composer, and that means looking around the community, doing loads of research and trying to identify the items that need to be covered in a talk. Mostly I have been trying to identify things that people do on a regular basis that according to composer internals is either wrong or not ideal.

Read More
Being an Enabler

Being an Enabler

  • December 19, 2012

When you look around at user groups you will notice that many groups have “leaders” or “managers”, this is usually the person who started the group and is not really calling the shots, but usually is giving direction to the group. This is an usual model and it works pretty well in most cases, however I have ran into situations where this causes some misconceptions.

Read More
Automação Residencial usando PHP

Automação Residencial usando PHP

  • January 18, 2007

Ao concluir meu curso de graduação, Engenharia da Computação, desenvolvi este projeto final. Desde que iniciei o blog me prometi postar um artigo sobre o mesmo e disponibilizar o projeto na sua integridade, então agora sim cumpro minha promessa.

Read More