SELECT . . . INTO User Defined Variable
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
Syntax
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_
table contains a list of all the user-defined variables.SCHEMA. USER_ VARIABLES
Limitations of User-Defined Variables
-
A user-defined variable must be assigned a value before it can be referenced in a query.
-
The
SELECT .
statement must return only a single row.. . INTO -
These variables cannot be used directly as an identifier or part of an identifier in a query, for example
SELECT `@col_
is not allowed.name` FROM table_ name Important
An exception to this rule is a column defined as
CREATE TABLE t (`@col_
.name` INT) In this case SELECT `@col_
returns the value from the column.name` FROM t -
These variables cannot be used in the
GROUP BY
orORDER 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 aLOAD DATA
statement, SingleStore interprets it as a reference to aLOAD 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.
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 |
+---------------+----------------+-----------------------+
Example 2
The following example assigns a value to a user-defined variable using a SELECT .
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 .
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.
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 allviewsFIELDS 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_
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.utf8mb4
character set.
For more information, refer to Implicit Collation in Special Cases.
Last modified: March 8, 2024