Sign inTry Free

SET

On this page

Sets a value for a user-defined variable for use in SQL statements. This is analogous to the SELECT ... INTO user defined variables discussed here: SELECT ... INTO User Defined Variable.

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 @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.

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

Was this article helpful?

On this page

Was this article helpful?