MySQueaL Resources

SET Statement

This MySQL statement sets a system or user variable for global or session use.

Use this MySQL 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 MySQL 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 with this MySQL statement:

SET @current_quarter = QUARTER(CURDATE( ));

This MySQL 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 other explanation of the MySQL statement, SET in our MySQL documentation.

Here's a more complete example of how this MySQL 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.