(show you how to use the MySQL SELECT INTO variable to store query results in one or more variables.)

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;

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 returned

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;

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

SELECT @city;

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;

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

SELECT
    @city,
    @country;

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;

Error:

Error Code: 1172. Result consisted of more than one row

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

SELECT
    creditLimit
INTO
    @creditLimit
FROM
    customers
WHERE
    customerNumber > 103
LIMIT 1;

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;

Output:

+----------------+
| @full_name     |
+----------------+
| Carine Schmitt |
+----------------+
1 row in set (0.00 sec)

Summary

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