MySQL :: MySQL 5.7 Reference Manual :: 12.4.4 Assignment Operators (original) (raw)

12.4.4 Assignment Operators

Table 12.6 Assignment Operators

Name Description
:= Assign a value
= Assign a value (as part of aSET statement, or as part of the SET clause in anUPDATE statement)
mysql> SELECT @var1, @var2;  
        -> NULL, NULL  
mysql> SELECT @var1 := 1, @var2;  
        -> 1, NULL  
mysql> SELECT @var1, @var2;  
        -> 1, NULL  
mysql> SELECT @var1, @var2 := @var1;  
        -> 1, 1  
mysql> SELECT @var1, @var2;  
        -> 1, 1  
mysql> SELECT @var1:=COUNT(*) FROM t1;  
        -> 4  
mysql> SELECT @var1;  
        -> 4  

You can make value assignments using:= in other statements besidesSELECT, such asUPDATE, as shown here:

mysql> SELECT @var1;  
        -> 4  
mysql> SELECT * FROM t1;  
        -> 1, 3, 5, 7  
mysql> UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1;  
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
mysql> SELECT @var1;  
        -> 1  
mysql> SELECT * FROM t1;  
        -> 2, 3, 5, 7  

While it is also possible both to set and to read the value of the same variable in a single SQL statement using the:= operator, this is not recommended.Section 9.4, “User-Defined Variables”, explains why you should avoid doing this.

mysql> SELECT @var1, @var2;  
        -> NULL, NULL  
mysql> SELECT @var1 := 1, @var2;  
        -> 1, NULL  
mysql> SELECT @var1, @var2;  
        -> 1, NULL  
mysql> SELECT @var1, @var2 := @var1;  
        -> 1, 1  
mysql> SELECT @var1, @var2;  
        -> 1, 1  

For more information, see Section 13.7.4.1, “SET Syntax for Variable Assignment”,Section 13.2.11, “UPDATE Statement”, and Section 13.2.10, “Subqueries”.