MySQL SELECT INTO Variable

Summary: in this tutorial, you will learn how to use the MySQL SELECT INTO variable to store query results in variables.

Introduction to MySQL SELECT INTO variable statement

To store the result set of a query in one or more variables, you use the SELECT INTO variable statement.

Here’s the syntax of the SELECT INTO variable statement:

SELECT 
    c1, c2, c3, ...
INTO 
    @v1, @v2, @v3,...
FROM 
    table_name
WHERE 
    condition;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• c1, c2, and c3 are columns or expressions that you want to store in variables.
• @v1, @v2, and @v3 are the variables that store the values from c1, c2, and c3.

The number of variables must be the same as the number of columns or expressions in the select list. In addition, the query must return zero or one row.

If the query returns no rows, MySQL issues a “no data” warning, and the values of the variables remain unchanged.

If the query returns multiple rows, MySQL issues an error. To ensure that the query always returns a maximum of one row, you use the LIMIT clause to restrict the result set to a single row:

SELECT 
    c1, c2, c3, ...
INTO 
    @v1, @v2, @v3,...
FROM 
    table_name
WHERE 
    condition
LIMIT 1; -- ensure maximum one row returnedCode language: SQL (Structured Query Language) (sql)

MySQL SELECT INTO variable examples

We will use the customers table in the sample database for the demonstration:

1) MySQL SELECT INTO single variable example

The following statement retrieves the city of the customer with the number 103 and stores it in the @city variable:

SELECT 
    city
INTO
    @city
FROM 
    customers
WHERE 
    customerNumber = 103;Code language: SQL (Structured Query Language) (sql)

The following statement displays the content of the @city variable:

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

Output:

+--------+
| @city  |
+--------+
| Nantes |
+--------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

2) MySQL SELECT INTO multiple variables example

To store values from the select list into multiple variables, you separate variables by commas.

For example, the following statement retrieves the city and country of customer number 103 and stores the data in variables @city and @country:

SELECT 
    city,
    country 
INTO
    @city,
    @country
FROM 
    customers
WHERE 
    customerNumber = 103;Code language: SQL (Structured Query Language) (sql)

The following statement shows the contents of the @city and @country variables:

SELECT 
    @city, 
    @country;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+----------+
| @city  | @country |
+--------+----------+
| Nantes | France   |
+--------+----------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

3) MySQL SELECT INTO variable – fixing multiple rows error

The following statement causes an error because the query returns multiple rows:

SELECT 
    creditLimit
INTO
    @creditLimit
FROM 
    customers
WHERE 
    customerNumber > 103;Code language: SQL (Structured Query Language) (sql)

Error:

Error Code: 1172. Result consisted of more than one rowCode language: SQL (Structured Query Language) (sql)

To fix this error, you use the LIMIT 1 clause as follows:

SELECT 
    creditLimit
INTO
    @creditLimit
FROM 
    customers
WHERE 
    customerNumber > 103
LIMIT 1;Code language: SQL (Structured Query Language) (sql)

4) Assigning an expression to a variable

The following example retrieves the full name of the contact of customer id 103 by concatenating the contact’s first name and last name. Then, it assigns the full name to the @full_name variable:

SELECT 
    CONCAT_WS('', contactFirstName, contactLastName)
INTO @full_name FROM
    customers
WHERE
    customerNumber = 103;

SELECT @full_name;Code language: JavaScript (javascript)

Output:

+----------------+
| @full_name     |
+----------------+
| Carine Schmitt |
+----------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)

Summary

• Use the SELECT INTO variable statement to store the query’s results into one or more variables.