MySQL Variables

Summary: in this tutorial, you will learn how to use MySQL variables in SQL statements.

Introduction to MySQL user-defined variables

Sometimes, you want to pass a value from an SQL statement to other SQL statements within the same session.

To do this, you store the value in a user-defined variable in the first statement and use it in the subsequent statements.

To create a user-defined variable, you use the following syntax:

@variable_nameCode language: SQL (Structured Query Language) (sql)

In this syntax, @variable_name is a user-defined variable. It is preceded by the @ symbol. In MySQL, user-defined variables are case-insensitive, meaning that @id and @ID are the same variables.

Note that user-defined variables are the MySQL-specific extension to SQL standard. They may not be available in other database systems.

MySQL variable assignment

MySQL offers two ways to assign a value to a user-defined variable.

1) Using the SET statement

To assign a value to a variable, you can use the SET statement as follows:

SET @variable_name = value;Code language: SQL (Structured Query Language) (sql)

This statement assigns the value to the @variable_name.

Besides using the assign operator =, you can use the := operator:

SET @variable_name := value;Code language: SQL (Structured Query Language) (sql)

2) Using the SELECT statement

The following SELECT statement assigns a value to the user-defined variable @variable_name:

SELECT value INTO @variable_name;Code language: SQL (Structured Query Language) (sql)

MySQL variable examples

We’ll use the products table from the sample database for the demonstration.

1) Basic user-defined variable example

The following statement retrieves the most expensive product in the products table and assigns the price to the user-defined variable @msrp:

SELECT 
  MAX(msrp) INTO @msrp
FROM 
  products;Code language: SQL (Structured Query Language) (sql)

To select the value of the @msrp, you use the following statement:

SELECT @msrp;Code language: CSS (css)

Output:

+------------------+
| @msrp:=MAX(msrp) |
+------------------+
|           214.30 |
+------------------+
1 row in set, 1 warning (0.03 sec)Code language: JavaScript (javascript)

The following statement uses the @msrp variable to query the information of the most expensive product.

SELECT 
  productCode, 
  productName, 
  productLine, 
  msrp 
FROM 
  products 
WHERE 
  msrp = @msrp;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------+--------------------------+--------------+--------+
| productCode | productName              | productLine  | msrp   |
+-------------+--------------------------+--------------+--------+
| S10_1949    | 1952 Alpine Renault 1300 | Classic Cars | 214.30 |
+-------------+--------------------------+--------------+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

2) Using a user-defined variable in a query that returns multiple values

A user-defined variable can hold a single value. If the SELECT statement returns multiple values, MySQL will issue an error and the variable will take the value of the first row in the result set:

SELECT 
  buyPrice INTO @buy_price
FROM 
  products 
WHERE 
  buyPrice > 95 
ORDER BY 
  buyPrice;Code language: SQL (Structured Query Language) (sql)

Output:

ERROR 1172 (42000): Result consisted of more than one row

In this example, the @buy_price will store the first value in the result set:

SELECT @buy_price;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+
| @buy_price |
+------------+
|      95.34 |
+------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Summary

• Use the MySQL user-defined variables in the SQL statements to pass data between statements within a session.