Set the result of a query to a variable in MySQL?

You can set the result of a query using select into command. The syntax is as follows.

select yourColumnName1 into @anyVariableName from yourTableName where yourColumnName2='anyValue';

Check the result is present in the variable or not using the select command. The syntax is as follows -

select @anyVariableName;

To understand the above syntax, let us first create a table. The query to create a table is as follows.

mysql> create table StudentInformation
-> (
-> StudentId int,
-> StudentName varchar(100),
-> StudentAge int
-> );
Query OK, 0 rows affected (0.62 sec)

Insert some records in the table using insert command. The query is as follows.

mysql> insert into StudentInformation values(1,'John',23);
Query OK, 1 row affected (0.21 sec)

mysql> insert into StudentInformation values(2,'Adam',24);
Query OK, 1 row affected (0.17 sec)

mysql> insert into StudentInformation values(3,'Bob',21);
Query OK, 1 row affected (0.20 sec)

mysql> insert into StudentInformation values(4,'Carol',20);
Query OK, 1 row affected (0.17 sec)

mysql> insert into StudentInformation values(5,'Mike',25);
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement. The query is as follows.

mysql> select *from StudentInformation;

The following is the output.

+-----------+-------------+------------+
| StudentId | StudentName | StudentAge |
+-----------+-------------+------------+
| 1         | John        | 23         |
| 2         | Adam        | 24         |
| 3         | Bob         | 21         |
| 4         | Carol       | 20         |
| 5         | Mike        | 25         |
+-----------+-------------+------------+
5 rows in set (0.00 sec)

Here is the query to set the result of query into a variable.

mysql> select StudentAge into @yourAge from StudentInformation where StudentName='Adam';
Query OK, 1 row affected (0.03 sec)

Check what is stored in the variable @yourAge. The query is as follows.

mysql> select @yourAge;

The following is the output displaying age of Student Adam.

+----------+
| @yourAge |
+----------+
| 24       |
+----------+
1 row in set (0.00 sec)