MySQL - Session Variables - How to Define and Use User-Defined Variables

A session variable is a user-defined variable (not a server option) that starts with @, does not require declaration, can be used in any SQL query or statement, not visible to other sessions, and exists until the end of the current session.

Quick Example:

    -- Initialize the session variable (declaration statement is not required)
    SET @message = 'Current date and time is ';
 
   -- Use the session variable in the query
    SELECT CONCAT(@message, CURRENT_TIMESTAMP);
    -- Result: Current date and time is 2012-04-03 15:20:20

Session variables:

Session Variable Name	@var_name
Declaration	Not required
Data Type	Defined in the assignment
Assignment	SET @var_name = | := value	@var_name := value in a SQL statement
Scope	Until the end of the current session
Visibility	Current session only

Last Update: MySQL 5.6

You can start using a session variable without declaration by just assigning a value.

You can assign a string or numeric value to the same session variable. Then when the variable is used in expressions, MySQL will perform required data type casting implicitly:

  -- Initialize to string
  SET @id = 'A';
 
  SELECT CONCAT(@id, 'B');
  -- Result: AB
 
  -- Assign a number to the same session variable
  SET @id = 13;
 
  SELECT @id * 3;
  -- Result: 39
 
  SELECT CONCAT(@id, 'B');
  -- Result: 13B

Assume that there is the following table definition and data:

  CREATE TABLE cities (name VARCHAR(90));
 
  INSERT INTO cities VALUES ('London');
  INSERT INTO cities VALUES ('Brussels');
  INSERT INTO cities VALUES ('Vienna');

You can use a session variable in a query to assign the row number:

  SET @row_number = 0;
 
  -- Increment the counter in the query
  SELECT @row_number := @row_number + 1, name FROM cities;

The query returns:

You can get the same result in a single query without SET statement initializing the row number to 0 in a subquery:

 SELECT @row_number := @row_number + 1, name 
 FROM cities, (SELECT @row_number := 0) r;

• Reference and Migration
• How To Get Top N Rows per Each Group

MySQL 5.6 Documentation

• User-Defined Variables