LOCAL, SESSION, AND GLOBAL VARIABLES IN MYSQL

LOCAL, SESSION, AND GLOBAL VARIABLES IN MYSQL : Variable Scope : Procedure Function MySQL TUTORIALS

MySQL TUTORIALS » Procedure Function » Variable Scope »

LOCAL, SESSION, AND GLOBAL VARIABLES IN MYSQL

MySQL has three different kinds of variables:

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.

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

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


SET @cost = @cost + 5.00;

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


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>