MySQL: @variable vs. variable. Whats the difference? (Part2)

Question

Ok, building off of the last question I asked, How does Mysql handle the where statment in the following code:

DELIMITER ;//
DROP PROCEDURE IF EXISTS `test`;//
CREATE PROCEDURE `test`
(
  id INT
)
BEGIN
  SELECT *
    FROM some_table
   WHERE id = id;
END;//

What does MySQL do in this case? Does it treat the where clause as

some_table.id = id

or does it treat it like

some_table.id = some_table.id

Right now I am doing something like

WHERE id = @id

because I didn't know that there were session variables in MySQL and it didn't complain and I thought that it was an explicit way of saying "where this column equals this variable".

Some might say "duh.. of course it treats it as column = variable" but I could easily have said where "variable = column." So how does it handle this?

Answer 1

I'm pretty sure that the @ signifies that it's a variable in the procedure, rather than referencing a table column. There is only one table column named id, so in this case it is unambiguous. If you were doing a join, then you would need to prefix it:

select *
from table1, table2
where table1.id = @id
and table2.some_field = table1.id

It doesn't matter where it is, the variable will always require the @ prefix, as in this query:

select id, @id
from table1

the first id is the unambiguous table column table1.id, and the @id references the stored procedure variable.

Answer 2

MySQL's variable naming schema is a bit weird, when having the first look into it. Generally MySQL differentiates between three types of variables:

• system variables (global or session scoped): @@varname
• user defined variables (they are session scoped): @varname
• local variables in stored programs: varname

So naming conflicts, such as those you mentioned above, only arise within stored programs. Therefore you first should try to avoid these naming conflicts by assigning unambiguous parameter names, e.g. by prefxing the parameters with p such as pId. If MySQL encounters an ambiguity it will interpret the reference as the name of a variable (see here):

[...] Local variable names should not be the same as column names. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable. [...]

The wording currently somehow gives the impression that this behaviour could change in future versions.