mysql 变量

1. 使用用户变量

 

 

可以清空MySQL 用户变量以记录结果，不必将它们保存到客户端的临时变量中。

例如，要找出价格最高或最低的物品的，其方法是：

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;



mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;



+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

 

2.用户变量

 

可以先在用户变量中保存值然后在以后引用它；这样可以将值从一个语句传递到另一个语句。用户变量与连接有关 。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

用户变量的形式为@var_name ，其中变量名var_name 可以由当前字符集的文字数字字符、‘. ’、‘_ ’和‘$ ’组成。 默认字符集是cp1252 (Latin1) 。可以用mysqld 的--default-character-set 选项更改字符集。用户变量名对大小写不敏感。

设置用户变量的一个途径是执行SET 语句：

 

SET @var_name = expr [, @var_name = expr] ...

 

对于SET ，可以使用= 或:= 作为分配符。分配给每个变量的expr 可以为整数、实数、字符串或者NULL 值。

也可以用语句代替SET 来为用户变量分配一个值。在这种情况下，分配符必须为:= 而不能用= ，因为在非SET 语句中= 被视为一个比较 操作符：

 

 

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

 

用户变量可以用于表达式中。目前不包括明显需要文字值的上下文中，例如SELECT 语句的LIMIT 子句，或者LOAD DATA 语句的IGNORE number LINES 子句。

如果使用没有初始化的变量，其值是NULL 。

如果用户变量分配了一个字符串值，其字符集和校对规则与该字符串的相同。用户变量的可压缩性（coercibility ）是隐含的。( 即为表列值的相同的可压缩性（coercibility ）。

注释： 在SELECT 语句中，表达式发送到客户端后才进行计算。这说明在HAVING 、GROUP BY 或者ORDER BY 子句中，不能使用包含SELECT 列表中所设的变量的表达式。例如，下面的语句不能按期望工作：

 

mysql> SELECT (@aa:=id) AS a，(@aa+3) AS b 从tbl_name HAVING b=5；

 

HAVING 子句中引用了SELECT 列表中的表达式的别名，使用@aa 。不能按期望工作：@aa 不包含当前行的值，而是前面所选的行的id 值。

一般原则是不要在语句的一个部分为用户变量分配一个值而在同一语句的其它部分使用该变量。可能会得到期望的结果，但不能保证。

设置变量并在同一语句中使用它的另一个问题是变量的默认结果的类型取决于语句前面的变量类型。下面的例子说明了该点：

 

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

 

对于该 SELECT 语句，MySQL 向客户端报告第1 列是一个字符串，并且将@a 的所有访问转换为字符串，即使@a 在第2 行中设置为一个数字。执行完SELECT 语句后，@a 被视为下一语句的一个数字。

要想避免这种问题，要么不在同一个语句中设置并使用相同的变量，要么在使用前将变量设置为0 、0.0 或者'' 以定义其类型。

未分配的变量有一个值NULL ，类型为字符串。

 

 

3. 变量赋值

 

DECLARE语句

DECLARE语句被用来把不同项目局域到一个子程序：局部变量，条件和处理程序及光标。SIGNAL和RESIGNAL语句当前还不被支持。

DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。

光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。


你可以在子程序中声明并使用变量。

  DECLARE局部变量
DECLARE var_name[,...] type [DEFAULT value]
这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。

局部变量的作用范围在它被声明的BEGIN ... END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。

变量SET语句
SET var_name = expr [, var_name = expr] ...
在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量。

在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。其中不同的变量类型（局域声明变量及全局和集体变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。在那种情况下，此选项被识别，但是被忽略了。

SELECT ... INTO语句
SELECT col_name[,...] INTO var_name[,...] table_expr
这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
注意，用户变量名在MySQL 5.1中是对大小写不敏感的。请参阅9.3节，“用户变量”。

重要: SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的参考，并包含一个与列相同名字的局部变量，MySQL当前把参考解释为一个变量的名字。例如，在下面的语句中，xname 被解释为到xname variable 的参考而不是到xname column的：

CREATE PROCEDURE sp1 (x VARCHAR(5))
  BEGIN
    DECLARE xname VARCHAR(5) DEFAULT 'bob';
    DECLARE newname VARCHAR(5);
    DECLARE xid INT;
   
    SELECT xname,id INTO newname,xid
      FROM table1 WHERE xname = xname;
    SELECT newname;
  END;
当这个程序被调用的时候，无论table.xname列的值是什么，变量newname将返回值‘bob’。

 

 

 

*************

 

一下内容来源互联网

 

mysql变量的术语分类：
1.用户变量：以"@"开始，形式为"@变量名"
用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名  或者  set @@global.变量名
对所有客户端生效。只有具有super权限才可以设置全局变量

3.会话变量：只对连接的客户端有效。

4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量
declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量

通俗理解术语之间的区别：

用户定义的变量就叫用户变量。这样理解的话，会话变量和全局变量都可以是用户定义的变量。只是他们是对当前客户端生效还是对所有客户端生效的区别了。所以，用户变量包括了会话变量和全局变量

局部变量与用户变量的区分在于两点:1.用户变量是以"@"开头的。局部变量没有这个符号。2.定义变量不同。用户变量使用set语句，局部变量使用declare语句定义 3.作用范围。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后，局部变量就消失了。

所以，最后它们之间的层次关系是：变量包括局部变量和用户变量。用户变量包括会话变量和全局变量。


使用备忘，set @var 若没有指定GLOBAL 或SESSION ，那么默认将会定义用户变量
两种方式定义用户变量：
1."=",如 set @a =3,@a:=5
2.":="。select常常这样使用
总结：使用select 和set设置变量的区别，set可以使用以上两种形式设置变量。而select只能使用":="的形式设置变量
实践积累：用户变量在mysql客户端退出后，会自动消失。之后我打开客户端，使用"select @a;" 显示变了的值为null。说明，未定义的变量初始化是null

实际中的问题

设置常量对group_concat()的配置影响：
SET @@GROUP_CONCAT_MAX_LEN=4
手册中提到设置的语法是这样的：
SET [SESSION | GLOBAL] group_concat_max_len = val;

以下两种形式都能达到达到同样的效果，但是有什么区别？

SET @@global.GROUP_CONCAT_MAX_LEN=4;
global可以省略，那么就变成了：SET @@GROUP_CONCAT_MAX_LEN=4;

2011.2.25

之前的理解不怎么准确。现在对加深理解后的地方进行总结。

mysql中变量的层次关系是:大体包括用户变量和系统变量。系统变量包括系统会话变量和系统全局变量。

我是这样理解相互之间的区别：

因为用户变量就是用户定义的变量，系统变量就是mysql定义和维护的变量。所以，用户变量与系统变量的区别在于，是谁在管理这些变量。mysql一启动的时候就会读取系统变量(这样做目的是可以确定mysql的以何种机制或模式运行)。 系统会话变量与用户变量都是在当前客户端退出后消失。他们之间的区别可以这样理解，虽然常常看到"set @@varible"的形式去改变系统变量的值，但是并不涉及到定义系统变量。用户变量是可以自己定义(初始化)。系统变量按照只是在改变值。

局部变量只在begin-end语句块中定义并有效。执行到该语句块之后就消失了。定义的方式有明显的特点，使用declare语句。

为什么看到使用系统变量以"@@变量名"和"变量名"的形式出现，怎么理解两者形式的区别？

使用系统变量理论上是可以使用两种形式：1.前面带有符号"@@" 2.符号省略。比如我会看的如下形式：CURRENT_USER。但是，约定系统变量要使用"@@变量名"的形式，就是在前面加上符号"@@"。

为什么会出现CURRENT_USER这样没有符号的情况？看书籍《SQL For MySQL Developers A Comprehensive Tutorial and Reference》大致说明的原因，这样做是为了与其他的SQL产品保持一致。