Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to set a system or user variable for global or session use. System variables can be either global variables, which makes them visible to all users, or session variables (also called local variables), which are available only to the connection thread that creates the variable. To make a system variable global, use the GLOBAL keyword or precede the variable name by @@global. System variables are limited to the current session by default, but you can document that behavior by using the SESSION keyword or preceding the variable name with @@session or just @@ (or use the synonyms LOCAL and @@local). To mark a user variable, place a single @ in front of the variable name. Here is an example of creating a user variable:

SET @current_quarter = QUARTER(CURDATE( ));

This statement uses the CURDATE() function to determine the current date. It’s wrapped in the QUARTER() function, which determines the quarter for the date given. The result is a number from one to four depending on the date. The number is stored in the user variable, @current_quarter. For examples involving system variables, see the explanation of the SET statement in Chapter 7.

Here’s a more complete example of how this statement and a user variable may be used:

SET @row = 0;

SELECT @row := @row + 1 AS Row, 
client_name AS Client
FROM clients 
ORDER BY client_id LIMIT 3;

+------+--------------------+
| Row  | Client             |
+------+--------------------+
|    1 | Geoffrey & Company | 
|    2 | Kenneth & Partners | 
|    3 | Marie & Associates | 
+------+--------------------+

In this example, the user variable @row is set to 0 and then used in a SELECT statement with the := operator to increment the value by 1 with each row retrieved. This gives us a nice row numbering in the results.