SELECT ... INTO User Defined Variable

The following example assigns a value to a user-defined variable, and then uses it in a SELECT statement.

CREATE TABLE circle (Radius int);
INSERT INTO circle VALUES(2),(3);

SELECT 3.14 INTO @pi;

SELECT Radius, Radius*POW(@pi,2) AS "Area" FROM circle;
****
+--------+---------+
| Radius | Area    |
+--------+---------+
|      2 | 19.7192 |
|      3 | 29.5788 |
+--------+---------+

The data type of the user-defined variable was inferred automatically, based on its value.

SELECT * FROM INFORMATION_SCHEMA.USER_VARIABLES;
****
+---------------+----------------+-----------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE         |
+---------------+----------------+-----------------------+
| pi            | 3.14           | decimal(3,2) NOT NULL |
+---------------+----------------+-----------------------+

The following example assigns a value to a user-defined variable using a SELECT ... INTO statement.

CREATE TABLE udv_courses (course_code varchar(20), section_number int, number_students int);

INSERT INTO udv_courses VALUES
("CS-101",1,20),
("CS-101",2,16),
("CS-101",3,22),
("CS-101",4,25),
("CS-101",5,22),
("CS-150",1,10),
("CS-150",2,16),
("CS-150",3,11),
("CS-150",4,17),
("CS-150",5,9),
("CS-201",1,14),
("CS-201",2,17),
("CS-301",1,7),
("CS-301",2,10);

SELECT * FROM udv_courses ORDER BY course_code, section_number;
****
+-------------+----------------+-----------------+
| course_code | section_number | number_students |
+-------------+----------------+-----------------+
| CS-101      |              1 |              20 |
| CS-101      |              2 |              16 |
| CS-101      |              3 |              22 |
| CS-101      |              4 |              25 |
| CS-101      |              5 |              22 |
| CS-150      |              1 |              10 |
| CS-150      |              2 |              16 |
| CS-150      |              3 |              11 |
| CS-150      |              4 |              17 |
| CS-150      |              5 |               9 |
| CS-201      |              1 |              14 |
| CS-201      |              2 |              17 |
| CS-301      |              1 |               7 |
| CS-301      |              2 |              10 |
+-------------+----------------+-----------------+

SELECT number_students FROM udv_courses WHERE course_code = 'CS-301' and section_number = 1 INTO @ns;

SELECT @ns;
****
+------+
| @ns  |
+------+
|    7 |
+------+

Note: The SELECT ... INTO statement must return only a single value.

The following example shows how using @ in a LOAD DATA query is interpreted as a reference to LOAD DATA assignment, instead of a user-defined variable.

SELECT 100 INTO @Views;

SELECT @Views;
****
+--------+
| @Views |
+--------+
|    100 |
+--------+

SELECT * FROM allviews;
****
+-------+---------+-------+
| State | Product | Views |
+-------+---------+-------+
| NY    | C       |    15 |
| CA    | D       |    20 |
| CA    | A       |    60 |
| CA    | E       |    50 |
| CA    | G       |    10 |
| NY    | B       |    20 |
| NY    | F       |    40 |
+-------+---------+-------+

cat /tmp/data.csv
****
TX,H,25
TX,I,10

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE allviews
FIELDS TERMINATED BY ','
(State,Product,@Views)
SET Views = @Views + 10;

SELECT * FROM allviews;
****
+-------+---------+-------+
| State | Product | Views |
+-------+---------+-------+
| NY    | C       |    15 |
| CA    | D       |    20 |
| TX    | H       |    35 |
| TX    | I       |    20 |
| CA    | A       |    60 |
| CA    | E       |    50 |
| CA    | G       |    10 |
| NY    | B       |    20 |
| NY    | F       |    40 |
+-------+---------+-------+