Mega Code Archive
LOCAL, SESSION, AND GLOBAL VARIABLES IN MYSQL
<![CDATA[
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>
]]>