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; 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)