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
    -- 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

Use Session Variable in Query - Assign Row Number

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:

row_number name
1 London
2 Brussels
3 Vienna

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;

MySQL Resources