Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.0Mb
PDF (A4) - 35.1Mb
PDF (RPM) - 34.1Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 200.1Kb
Man Pages (Zip) - 305.3Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Assignment Operators

13.3.4 Assignment Operators

Table 13.5 Assignment Operators

NameDescription
= Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
:= Assign a value

  • :=

    Assignment operator. Causes the user variable on the left hand side of the operator to take on the value to its right. The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same SET statement. You can perform multiple assignments in the same statement-

    Unlike =, the := operator is never interpreted as a comparison operator. This means you can use := in any valid SQL statement (not just in SET statements) to assign a value to a variable.

    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 besides SELECT, such as UPDATE, 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 10.4, “User-Defined Variables”, explains why you should avoid doing this.

  • =

    This operator is used to perform value assignments in two cases, described in the next two paragraphs.

    Within a SET statement, = is treated as an assignment operator that causes the user variable on the left hand side of the operator to take on the value to its right. (In other words, when used in a SET statement, = is treated identically to :=.) The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same SET statement.

    In the SET clause of an UPDATE statement, = also acts as an assignment operator; in this case, however, it causes the column named on the left hand side of the operator to assume the value given to the right, provided any WHERE conditions that are part of the UPDATE are met. You can make multiple assignments in the same SET clause of an UPDATE statement.

    In any other context, = is treated as a comparison operator.

    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 14.7.4, “SET Syntax”, Section 14.2.11, “UPDATE Syntax”, and Section 14.2.10, “Subquery Syntax”.


User Comments
Sign Up Login You must be logged in to post a comment.