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?
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.
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.
