MySQL SELECT INTO Variable

Introduction to MySQL SELECT INTO variable statement

In MySQL, the SELECT INTO variable statement allows you to store query results into one or more variables. This is often useful when you need to reuse values later in a session or procedure.

SELECT
    column1, column2, column3...
INTO
    @var1, @var2, @var3...
FROM
    table_name
WHERE
    condition
LIMIT 1;  -- ensures only one row is returned

In this syntax:

• colum1, column2, and column3 are columns or expressions that you want to store in variables.
• @v1, @v2, and @v3 are the variables that store the values from column1, column2, and column3.
• 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.

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:

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)

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)

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;

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)

Example 2 – Multiple Variables

Store multiple columns into variables.

SELECT employeeNumber, lastName, firstName
INTO @empNo, @lname, @fname
FROM employees
WHERE jobTitle = 'Sales Rep'
LIMIT 1;

SELECT @empNo, @lname, @fname;

Output:

+-------+----------+---------+
| @empNo| @lname   | @fname  |
+-------+----------+---------+
| 1166  | Patterson| Steve   |
+-------+----------+---------+

Common Pitfall

If you omit LIMIT 1 and the query matches multiple rows:

SELECT lastName
INTO @lname
FROM employees
WHERE jobTitle = 'Sales Rep';

MySQL will throw:

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

Summary

• Use SELECT INTO variable to store query results into session variables.
• The query must return a single row; otherwise, errors occur.
• Add LIMIT 1 to make sure only one row is assigned.
• Works with single variables, multiple variables, and expressions.