MySQL: User-defined variables in SQL query

MySQL: User-defined variables in SQL query

About the variables in MySQL. There multiple types of variables in MySQL: server variable, session variable, status variable and user defined variable.

More information (official) you will find here:
http://dev.mysql.com/doc/refman/5.0/en/mysqld-server.html

This time, I will make a closer look at user variables. They can be divided into two main categories: defined variables, used in procedures and functions and undefined, also can be used in procedures and function, but also can be used in sql queries as in-line code.

Defined variables:

DECLARE vards VARCHAR(10); vai
DECLARE done INT DEFAULT 0; tālāk šādam mainīgam var piešķir vērtību
SET name := „tests”;
un izmantot SQL vaicājumos:
SELECT id FROM my_table WHERE name = vards;

Declaration can be used in user created mysql procedures and functions.

Undefined variables, or variables without defined data type:

To assign value to variable:

SET @my_variable=123;

This type of variables can be used in sql queries:

SELECT @a:=123, @b:=@a+321;

Variables can be combined as well:

mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

Variables without predefined data type, should not be used in queries where grouping is in use (GROUP BY, HAVING), because results can be unexpected (mostly you will get null value). Visibility of such variables is per-session. That means, such kind of variables are isolated from other sessions. Also if session was terminated.

More about variables:
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html


Example of usage of the variable without predefined data type:

SELECT
  @rate:=(SELECT
            c.rate
          FROM currencies c
          WHERE c.currID = itemsPrices.currID
          ORDER BY c.date
          DESC LIMIT 1) as rate,
  price1LVL,
  price1LVL / @rate as price1,
  price2LVL,
  price2LVL / @rate as price2,
  price3LVL,
  price3LVL / @rate as price3
FROM itemsPrices

Some thoughts about how variables should look like:

About this thematic thousands of books and tutorials are written, but this is a dry theory, and I doubt if many of us has read it. Of course, with the growing practical experience in database programming, early or later we start to realize, that code should look nice, readable and easy undestandable. And length and semantic meaning of variable is a case.

There is no strict limitation and recomendations on how mysql variables should look like. The only requirements are they should contain alpha numeric characters and underscore. Undefined variables can be recognized by @ sign at the beginning, but for defined variables such identification marks are up to you.

Examples:

DECLARE thisismyfirstvariable;
DECLARE this_is_my_first_variable;
DECLARE thisIsMyFirstVariable;
DECLARE _ThisIsMyFirstVariable;

 

» Rate it!

(23)

» Comments

No comments
* mandatory
* not public
Besucherzahler ukraina brides
website counter
vimeo youtube     myspace facebook twitter
where to eat in riga?