MySQL SELECT INTO Variable

In this tutorial, we will learn about the SELECT INTO Variable statement. We will start with the basic introduction to the SELECT INTO Variable statement and then proceed to check its syntax along with some examples. So, let’s get started!

Introduction to MySQL SELECT INTO Variable

The SELECT INTO variable statement is used to save the column value or the query result into another variable. Moreover, you can save the result fetched by the query into a file as well.

To keep the answer short and simple, the SELECT INTO statement will first fetch the data from the table and insert it into the variable or a file.

Let’s now see the syntax of the SELECT INTO variable statement.

MySQL SELECT INTO Variable Syntax

MySQL provides us with the different syntax forms of the SELECT INTO variable statement. We will see them one by one.

Select the data into the variable –

SELECT ... INTO var_list FROM table_name;Code language: SQL (Structured Query Language) (sql)

For example-

SELECT * INTO @myvar FROM t1;Code language: SQL (Structured Query Language) (sql)

Note that, we are inserting the value into a single variable. Therefore, the query must fetch a single value. In order to prevent the query from fetching multiple rows, you can use the LIMIT clause or the WHERE clause.

Here, the variables that are defined with the prefix @ are the session variables and they work as long as the current session is active. Once you close the terminal or end the session, those variables won’t work anymore.

Write the data into the file –

SELECT ... INTO OUTFILE 'filepath' FROM table_name;Code language: SQL (Structured Query Language) (sql)

For example-

SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/Files/emps.txt' FROM t1;Code language: SQL (Structured Query Language) (sql)

Here, the path that you are setting for the outfile must match with the secure-file path that you can find in the my.ini file in the MySQL SERVER folder.

Also, there is no need to set the limit as the file can consist of any number of records that are fetched from the query.

Enough details, let’s see some examples now.

MySQL SELECT INTO Variable Examples

To demonstrate the SELECT INTO variable examples, we will use the following table.

Selecting the Value into a Single Variable

Now, we will write a query to select the city value of employee ‘mark’ into the new variable and display that value using the new variable.

SELECT city INTO @city FROM emps WHERE name='Mark';
SELECT @city;Code language: SQL (Structured Query Language) (sql)

As you can see, we have received the expected output.

Selecting the Value into a Multiple Variable

If there are multiple values in a single row, we can assign them by creating multiple session variables. See below-

SELECT name,city INTO @name, @city FROM emps WHERE id=1;
SELECT @name, @city;Code language: SQL (Structured Query Language) (sql)

Here, we are fetching the name and the city of the employee having id 1 into the variables ‘name’ and ‘city’.

Let’s display them and see the result.

As you can see, we have received the expected output.

Writing the Data into the File

Using the SELECT INTO variable statement, we can write the data fetched from the query into the file.

SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/emps.txt' FROM emps;Code language: SQL (Structured Query Language) (sql)

Here, the export path of files in my system is given as follows. It might be different in your case. You can check it inside the my.ini file under the ‘secure-file’ option.

Let’s check if the file is created and data is written into it.

As you can see, the query has been executed successfully, which means the file consisting of the data has been created. Let’s see the file now.

Here you can see, that all the records have been pasted into the file in a good format.

Conclusion

In this tutorial, we have learned about the SELECT INTO variable statement which is used to store the query result into the variable or a file. We have seen two different syntax as well as their examples. I hope you have learned something valuable and you might try it to enhance your MySQL knowledge.

References

MySQL Official documentation on the SELECT…INTO statement.