mysql中三种变量类型的区别

MySQL has three different kinds of variables:

MySQL 有三种不同变量类型：

Local variables

本地变量

Local variables are set in the scope of a statement or block of statements.

本地变量的工作范围是在代码或者一段代码的范围内。

Once that statement or block of statements has completed, the variable goes out of scope.

一旦代码或者一段代码执行完成，变量就失效了。

Session variables

会话变量

Session variables are set in the scope of your session with the MySQL server.

会话变量的工作范围是当前连接到MySQL服务器的会话的范围。

A session starts with a connection to the server and ends when the connection is closed.

一个会话从连接到服务器时开始，当连接关闭时结束。

Variables go out of scope once the connection is terminated.

会话变量在连接终止后便失效了。

Variables created during your connection cannot be referenced from other sessions.

会话变量只在当前连接中有效，无法被其他会话进行使用或参考。

To declare or reference a session variable, prefix the variable name with an @ symbol:

要声明或使用一个会话变量，需要在变量名称前加上@符号：

SET @count = 100;.

Global variables

全局变量

Global variables exist across connections.

全局变量可以跨会话使用。

They are set using the GLOBAL keyword: SET GLOBAL max_connections = 300;.

全局变量使用“GLOBAL”关键字：SET GLOBAL max_connections = 300;

Global variables are not self-defined, but are tied to the configuration of the running server.

全局变量不是自我定义的，但是是绑定到正在运行的服务器的配置中的。

Using the DECLARE statement with a DEFAULT will set the value of a local variable.

使用DECLARE语句来声明本地变量，并且可以用DEFAULT来设置默认值。

Values can be assigned to local, session, and global variables using the SET statement:

可以用SET语句来为本地变量，会话变量和全局变量进行赋值：

SET @cost = @cost + 5.00;

MySQL's SET statement includes an extension that permits setting multiple variables in one statement:

MySQL的SET语句允许在一句语句中为多个变量进行赋值：

mysql>
mysql> SET @cost = 5, @cost1 = 8.00;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select @cost;
+-------+
| @cost |
+-------+
| 5     |
+-------+
1 row in set (0.01 sec)

mysql>
mysql> select @cost1;
+--------+
| @cost1 |
+--------+
| 8.00   |
+--------+
1 row in set (0.00 sec)

mysql>

原文地址：http://www.java2s.com/Tutorial/MySQL/0201__Procedure-Function/LOCALSESSIONANDGLOBALVARIABLESINMYSQL.htm