SELECT ... INTO User Defined Variable

Syntax

SQL

SELECT ... INTO @user_defined_variable [,...]

Remarks

Refer to user-defined variables as @user_defined_variable.
User-defined variables are case-insensitive, which means @rollno is the same as @RollNo.
These variables are session-specific; they expire once the session ends. Also, a session variable defined by a user cannot be viewed or referenced by another session.
The data type of a user-defined variable is inferred automatically.
The INFORMATION_SCHEMA.USER_VARIABLES table contains a list of all the user-defined variables.

Warning

Do not use the SET command to define or assign values to user-defined variables. The feature SET @myvar = <value> is not supported.

SQL

SET @pi = 3.14;

SHOW WARNINGS;

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                         |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1706 | Feature 'Updating User Defined Variables using SET query' is not supported by MemSQL. Execution will continue, but the feature will be ignored. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+

Limitations of User-Defined Variables

A user-defined variable must be assigned a value before it can be referenced in a query.

Note

Currently, SingleStore does not support initializing (or updating) user-defined variables using a SET query. Although the SET @var = value query does not throw an error, use the SELECT ... INTO statement instead to initialize a user-defined variable.
The SELECT ... INTO statement must return only a single row.
These variables cannot be used directly as an identifier or part of an identifier in a query, for example SELECT `@col_name` FROM table_name is not allowed.

Important

An exception to this rule is a column defined as CREATE TABLE t (`@col_name` INT). In this case SELECT `@col_name` FROM t returns the value from the column.
These variables cannot be used in the GROUP BY or ORDER BY clause.
User-defined variables cannot be referenced in stored procedures, computed columns, user-defined functions, table-valued functions, or views.
If a query uses @ in a LOAD DATA statement, SingleStore interprets it as a reference to a LOAD DATA assignment instead of a user-defined variable.

Examples

Example 1

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

SQL

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

SQL

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.

SQL

SELECT * FROM INFORMATION_SCHEMA.USER_VARIABLES;

+---------------+----------------+-----------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE         |
+---------------+----------------+-----------------------+
| pi            | 3.14           | decimal(3,2) NOT NULL |
+---------------+----------------+-----------------------+

Example 2

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

SQL

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 |
+-------------+----------------+-----------------+

SQL

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.

Example 3

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.

SQL

SELECT 100 INTO @Views;

SELECT @Views;

+--------+
| @Views |
+--------+
|    100 |
+--------+

SQL

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

SQL

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 |
+-------+---------+-------+

Caution

Implicit Collation

When character_set_server is set to utf8, string literals with characters using 4-byte encoding are implicitly assigned binary collation and processed as a sequence of bytes rather than characters. This implicit conversion to binary collation causes string functions to return unexpected results. To avoid using implicit binary collation, either use explicit type casting or use database columns defined with the utf8mb4 character set.

For more information, refer to Implicit Collation in Special Cases.