MySQL: User-Defined Variables in SQL Queries

About variables in MySQL. There are several types of variables that can be used and sometimes have their values changed: server variables, session variables, status variables, user-defined variables.

MySQL variables can be explored here:
http://dev.mysql.com/doc/refman/5.0/en/mysqld-server.html

This time my interest fell specifically on user-defined variables. They can be broadly divided into two categories: declared variables, used in procedures and functions, and undeclared variables, also used in procedures and functions, but which can be used in an SQL query "in-line".

Declared variables:
DECLARE name VARCHAR(10); or
DECLARE done INT DEFAULT 0; such a variable can then be assigned a value
SET name := "tests";
and used in SQL queries:
SELECT id FROM my_table WHERE name = name;

True, all of this is within the scope of a function/procedure.

Undeclared variables, or variables with an undeclared data type:

For assignment, use as follows:
SET @my_variable=123;

this type of variable can also be used in SQL queries, for example:
SELECT @a:=123, @b:=@a+321;

or combining:

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

These variables should not be used in queries that use grouping (GROUP BY, HAVING), as the result may be far from what was expected.
Variable "visibility" is within the scope of a session. This means they are isolated from other sessions - including from subsequent sessions if those have been interrupted.

More can be read here:
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Example of using an undeclared-type variable:

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 on variable naming:

Thick books and tutorials have been written on this topic. But it would be naive to hope that anyone has read them eagerly. True, at a certain stage of development in programming, the desire arises to make code somewhat readable and easy to follow, and the "look" of a variable (its length and semantic meaning) is one of the ways to do this.

MySQL tutorials show that a variable is formed "nothing special" - it is a string of lowercase Latin characters. Undeclared variables can be distinguished by the @ sign before them, while for declared variables the distinguishing marker must be devised by the developer.

Example:
DECLARE thisismyfirstvariable;
DECLARE this_is_my_first_variable;
DECLARE thisIsMyFirstVariable;
DECLARE _ThisIsMyFirstVariable;