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)