SET

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

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SET [GLOBAL|@@global.|SESSION|@@session.] variable = expression

Explanation

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.

Examples

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.