CrystalRays的技术博客

咱暂且先用LOFTER,以后有时间了再自己建站吧

MySQL学习小记(二)——变量【转】

原文地址:https://www.cnblogs.com/gavin110-lgy/p/5772577.html

根据mysql手册,mysql的变量分为两种:系统变量和用户变量。但是在实际使用中,还会遇到诸如局部变量、会话变量等概念。根据个人感觉,mysql变量大体可以分为四种类型:

一、局部变量。

局部变量一般用在sql语句块中,比如存储过程的begin/end。其作用域仅限于该语句块,在该语句块执行完毕后,局部变量就消失了。

局部变量一般用declare来声明,可以使用default来说明默认值。

例如在存储过程中定义局部变量:

drop procedure if exists add;

create procedure add

(

    in a int,

    in b int

)

begin

    declare c int default 0;

    set c = a + b;

    select c as c;

end;

 

在上述存储过程中定义的变量c就是局部变量

 

二、用户变量。

用户变量的作用域要比局部变量要广。用户变量可以作用于当前整个连接,但是当当前连接断开后,其所定义的用户变量都会消失。

用户变量使用如下(这里我们无须使用declare关键字进行定义,可以直接这样使用):

select @变量名

对用户变量赋值有两种方式,一种是直接用"="号,另一种是用":="号。其区别在于使用set命令对用户变量进行赋值时,两种方式都可以使用;当使用select语句对用户变量进行赋值时,只能使用":="方式,因为在select语句中,"="号被看作是比较操作符。

示例程序如下:

drop procedure if exists math;

create procedure math

(

    in a int,

    in b int

)

begin

    set @var1 = 1;

    set @var2 = 2;

    select @sum:=(a + b) as sum, @dif:=(a - b) as dif;

end;

 

mysql> call math(3, 4);
+------+------+
| sum  | dif  |
+------+------+
|    7 |   -1 | 
+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @var1; //var1为用户变量
+-------+
| @var1 |
+-------+
| 1     | 
+-------+
1 row in set (0.00 sec)

mysql> select @var2; //var2为用户变量
+-------+
| @var2 |
+-------+
| 2     | 
+-------+
1 row in set (0.00 sec)

 

3、会话变量

服务器为每个连接的客户端维护一系列会话变量。在客户端连接时,使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量,而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样,仅限于当前连接。当当前连接断开后,其设置的所有会话变量均失效。

设置会话变量有如下三种方式:

set session var_name = value;

set @@session.var_name = value;

set var_name = value;

查看一个会话变量也有如下三种方式:

select @@var_name;

select @@session.var_name;

show session variables like "%var%";

 

mysql> show session variables;
+---------------------------------+------------------------------------------------------------+
| Variable_name                   | Value                                                      |
+---------------------------------+------------------------------------------------------------+
| auto_increment_increment        | 1                                                          |
| auto_increment_offset           | 1                                                          |
| automatic_sp_privileges         | ON                                                         |
| back_log                        | 50                                                         |
| basedir                         | /usr/                                                      |
| bdb_cache_size                  | 8388600                                                    |
| bdb_home                        | /var/lib/mysql/                                            |
| bdb_log_buffer_size             | 32768                                                      |
| bdb_logdir                      |                                                            |
| bdb_max_lock                    | 10000                                                      |
| bdb_shared_data                 | OFF                                                        |
| version                         | 5.0.51a                                                    |
| version_bdb                     | Sleepycat Software: Berkeley DB 4.1.24: (January 11, 2008) |
| version_comment                 | Source distribution                                        |
| version_compile_machine         | i386                                                       |
| version_compile_os              | redhat-linux-gnu                                           |
| wait_timeout                    | 28800                                                      |
+---------------------------------+------------------------------------------------------------+
234 rows in set (0.00 sec)

 

 

4、全局变量

全局变量影响服务器整体操作。当服务器启动时,它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。要想更改全局变量,必须具有SUPER权限。全局变量作用于server的整个生命周期,但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效,需要更改相应的配置文件。

 

要设置一个全局变量,有如下两种方式:

set global var_name = value; //注意:此处的global不能省略。根据手册,set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL,默认使用SESSION

set @@global.var_name = value; //同上

 

要想查看一个全局变量,有如下两种方式:

select @@global.var_name;

show global variables like "%var%";

 

mysql> show global variables;
+---------------------------------+------------------------------------------------------------+
| Variable_name                   | Value                                                      |
+---------------------------------+------------------------------------------------------------+
| auto_increment_increment        | 1                                                          |
| auto_increment_offset           | 1                                                          |
| transaction_alloc_block_size    | 8192                                                       |
| transaction_prealloc_size       | 4096                                                       |
| tx_isolation                    | REPEATABLE-READ                                            |
| updatable_views_with_limit      | YES                                                        |
| version                         | 5.0.51a                                                    |
| version_bdb                     | Sleepycat Software: Berkeley DB 4.1.24: (January 11, 2008) |
| version_comment                 | Source distribution                                        |
| version_compile_machine         | i386                                                       |
| version_compile_os              | redhat-linux-gnu                                           |
| wait_timeout                    | 28800                                                      |
+---------------------------------+------------------------------------------------------------+
234 rows in set (0.00 sec)


评论
热度(1)

© CrystalRays的技术博客 | Powered by LOFTER