MySQL SELECT INTO Variable

Created with Sketch.

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.

MySQL SELECT INTO Variable syntax

To store query result in one or more variables, you use the SELECT INTO variable syntax:

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 select and store into the variables.
  • @v1, @v2, and @v3 are the variables which 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 returns zero or one row.

If the query return no rows, MySQL issues a warning of no data and the value of the variables remain unchanged.

In case the query returns multiple rows, MySQL issues an error. To ensure that the query always returns maximum one row, you use the LIMIT 1 clause to limit the result set to a single row.

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

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 finds the city and country of the customer number 103 and stores the data in two corresponding 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)

MySQL SELECT INTO variable – multiple rows example

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)

Here is the output:

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

Code language: SQL (Structured Query Language) (sql)

To fix it, 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)

In this tutorial, you have learned how to use the MySQL SELECT INTO variable syntax to store result query result in one or more variables.

Leave a Reply

Your email address will not be published. Required fields are marked *