MySQL:@variable与变量.有什么不同? (第2部分) [英] MySQL: @variable vs. variable. Whats the difference? (Part2)

问题描述

好吧，以我问的最后一个问题为基础， Mysql如何处理以下代码中的where语句:

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;//

在这种情况下，MySQL会做什么?它会将where子句视为

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

some_table.id = id

还是像对待它一样

some_table.id = some_table.id 

现在我正在做类似的事情

Right now I am doing something like

WHERE id = @id

因为我不知道MySQL中有会话变量，也没有抱怨，所以我认为这是一种明确的方式来表达此列等于该变量的位置".

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的变量命名架构时，它有点怪异.通常，MySQL区分三种类型的变量:

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

• 系统变量(全局或会话范围内):@@varname
• 用户定义的变量(它们在会话范围内):@varname
已存储的
• local 变量程序:varname

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

因此，诸如您上面提到的那些命名冲突仅在存储的程序中发生.因此，您首先应该尝试通过分配明确的参数名称来避免这些命名冲突，例如通过使用p(例如pId)预修饰参数.如果MySQL遇到歧义，它将把引用解释为变量的名称(

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):

[...]局部变量名称不应 与列名相同.如果是SQL 语句，例如SELECT ... INTO 语句，包含对 列和声明的局部变量 相同名称的 MySQL当前 将引用解释为名称 变量. [...]

[...] 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.

这篇关于MySQL:@variable与变量.有什么不同? (第2部分)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！