mysql 变量 区别,MySQL：@variable与变量。有什么不同？ （第2部分）

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.