| 11. 11. 1. 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: |
|
|
|
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>
|
|