SET
On this page
Sets a value for a user-defined variable for use in SQL statements.SELECT . user defined variables discussed here: SELECT .
Syntax
SET @my_var = <expr> [, @my_var_b = <expr_b>, @my_var_c = ...]Remarks
-
The expression can be as simple as a single digit or string, or it can be the result of a SQL statement.
Basically, anything that is valid in the form: SELECT <expr> INTO @my_var; -
The value assigned to the variable must be a single value (cannot assign multiple rows or columns).
-
User-defined variables are case-insensitive, which means
@rollnois 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
Examples
Setting the variable's value via a basic expression:
SET @a = 5 + 5;SELECT @a;
+------+
| @a |
+------+
| 10 |
+------+
1 row in set (0.054 sec)Setting the value referencing another user defined variable:
SET @a = 5 + 5;SET @b = @a * @a;SELECT @b;
+------+
| @b |
+------+
| 100 |
+------+
1 row in set (0.051 sec)Setting the value as the result of a SQL statement:
CREATE TABLE set_example (a int, b int);INSERT INTO set_example VALUES(1,2),(3,4),(5,6),(7,8);SELECT * FROM set_example;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 7 | 8 |
+------+------+
4 rows in set (0.001 sec)SET @a = (SELECT COUNT(*) FROM set_example);SELECT @a;
+------+
| @a |
+------+
| 4 |
+------+
1 row in set (0.000 sec)SET @a = (SELECT b FROM set_example WHERE a = 7);SELECT @a;
+------+
| @a |
+------+
| 8 |
+------+
1 row in set (0.000 sec)SET @a = (SELECT b,a FROM set_example WHERE a = 7);ERROR 1241 (21000): Operand should contain 1 column(s)SET @a = (SELECT b FROM set_example);ERROR 1242 (21000): Subquery returns more than 1 row
User-defined variables on the right-hand side of assignments are bound before the SET statement is run.
SET @x = 1;SET @y = 2;SET @x = @y, @y = @x;SELECT @x, @y;
+----+----+
| @x | @y |
+----+----+
| 2 | 1 |
+----+----+Other than that, the assignments in the list are run left-to-right.
SET @x = 1, @x = 2;SELECT @x;
+----+
| @x |
+----+
| 2 |
+----+User defined variables and functions:
SET @a = 8;DELIMITER //USE db //CREATE OR REPLACE FUNCTION foo (a INT) RETURNS INT AS BEGIN RETURN a * a; END //DELIMITER ;SET @c = foo(@a);SELECT @a, @c;
+------+------+
| @a | @c |
+------+------+
| 8 | 64|
+------+------+SET @d = (SELECT @a) * 2;Query OK, 0 rows affected (0.098 sec)SELECT @d;
+------+
| @d |
+------+
| 16 |
+------+
1 row in set (0.055 sec)SET @d = (SELECT @a) * 2, @e = foo(10), @f = @a + @b + @@SESSION.net_read_timeout;Query OK, 0 rows affected (0.062 sec)SELECT @d, @e, @f;
+------+------+------+
| @d | @e | @f |
+------+------+------+
| 16 | 100 | 3708 |
+------+------+------+
1 row in set (0.055 sec)SELECT @@SESSION.net_read_timeout;
+----------------------------+
| @@SESSION.net_read_timeout |
+----------------------------+
| 3600 |
+----------------------------+
1 row in set (0.075 sec)SET @g := @a + @b + @@SESSION.net_read_timeout;Query OK, 0 rows affected (0.081 sec)SELECT @g;
+------+
| @g |
+------+
| 3708 |
+------+
1 row in set (0.069 sec)Using strings and objects:
SET @t = FROM_UNIXTIME(1234567890), @s = 'abc', @fp = 3.14;SELECT @t, @s, @fp;
+---------------------+-----+------+
| @t | @s | @fp |
+---------------------+-----+------+
| 2009-02-14 01:31:30 | abc | 3.14 |
+---------------------+-----+------+
1 row in set (0.053 sec)SET @str = CONCAT(@s, @@GLOBAL.sql_mode);SELECT @str;
+-----------------------------------------------------+
| @str |
+-----------------------------------------------------+
| abcANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES |
+-----------------------------------------------------+
1 row in set (0.059 sec)Last modified: March 8, 2024