SET

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 datatype 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;
Query OK, 0 rows affected (0.066 sec)

SELECT @a;
+------+
| @a   |
+------+
|   10 |
+------+
1 row in set (0.054 sec)

Setting the value referencing another user defined variable:

SET @a = 5 + 5;
Query OK, 0 rows affected (0.066 sec)

SET @b = @a * @a;
Query OK, 0 rows affected (0.074 sec)

SELECT @b;
+------+
| @b   |
+------+
|  100 |
+------+
1 row in set (0.051 sec)

Setting the value as the result of a SQL statement:

SELECT * FROM db.t;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|    7 |    8 |
+------+------+
4 rows in set (0.001 sec)

SET @a = (SELECT COUNT(*) FROM db.t);
Query OK, 0 rows affected (0.071 sec)

SELECT @a;

+------+
| @a   |
+------+
|    4 |
+------+
1 row in set (0.000 sec)

SET @a = (SELECT b FROM db.t WHERE a = 7);
Query OK, 0 rows affected (0.095 sec)

SELECT @a;
+------+
| @a   |
+------+
|    8 |
+------+
1 row in set (0.000 sec)

SET @a = (SELECT b,a FROM db.t WHERE a = 7);
ERROR 1241 (21000): Operand should contain 1 column(s)

SET @a = (SELECT b FROM db.t);
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;
Query OK, 0 rows affected (0.00 sec)

SET @y = 2;
Query OK, 0 rows affected (0.00 sec)

SET @x = @y, @y = @x;
Query OK, 0 rows affected (0.01 sec)

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;
Query OK, 0 rows affected (0.00 sec)

SELECT @x;
+----+
| @x |
+----+
| 2  |
+----+

User defined variables and functions:

SET @a = 8;

DELIMITER //
USE db //

Database changed
CREATE OR REPLACE FUNCTION foo (a INT) RETURNS INT AS BEGIN RETURN a * a; END //
Query OK, 1 row affected (0.007 sec)

DELIMITER ;

SET @c = foo(@a);
Query OK, 0 rows affected (0.102 sec)

SELECT @a;
+------+
| @a   |
+------+
|    8 |
+------+
1 row in set (0.021 sec)

SELECT @c;
+------+
| @c   |
+------+
|   64 |
+------+
1 row in set (0.070 sec)

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;
Query OK, 0 rows affected (0.060 sec)

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);
Query OK, 0 rows affected (0.058 sec)

SELECT @str;
+-----------------------------------------------------+
| @str                                                |
+-----------------------------------------------------+
| abcANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES |
+-----------------------------------------------------+
1 row in set (0.059 sec)
In this section: