MySQL User Defined Variable

This is a brief introduction to user defined variables in MySQL as many times I have gotten queries regarding how to use them in dynamic queries.

In MySQL you can easily create user defined variable and use it through out that particular session or client connection. A variable is a alphanumeric characters following a "@". In the versions 5 and above the name is case-insensitive and hence @VAR, @vaR all mean the same. For example:

set @var = 1;

The above statement creates a variable called "var" and sets it to 1. Also note you don't need to declare it before using it.

The statement,

set @var1 = 1, @var2 = 2, @var3 = 3;

sets all three variables in single statement. You can then select the variables from

select @var1, @var2, @var3;

Now, let us say that you would like to select particular row(s) from a given table. To do so you need to first build the sql string, prepare it and then execute it. This allows you to pass the variables one or more times to the same statement.

For this let us assume we have a table (table_t) that has two columns - id and name and has 3 rows (1, 'one'), (2, 'two') and (3, 'three'). To select row with id = 1

set @var = 1;

set @st = concat('select id, name from table_t where id = ', @var);

prepare stmt from @st;

execute stmt ;

And to select with string variable like names you need to escape the single quote as below.

set @var = 'one'

set @st = concat('select id, name from table_t where name = ', '''', @var, '''');

prepare stmt from @st;

execute stmt ;

This is a trivial example but you get the idea of how to use user defined variables. The same technique can be used to build more complex stored procedures and statements that are executed often with different variables.

Cheers,

Shiva

---

---