why we can use = and := both with set?

Question

Generally we know that '=' operator using for comparison and ':=' for assignment,

But while using with SET both are working as assignment operator why?

below stored procedure for example:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `substringExample`()
BEGIN 
    DECLARE x varchar(7);    
    DECLARE num int;
    DECLARE inc int;

    SET inc:= 1;

    WHILE inc<1400 DO

    SELECT SUBSTRING(USER_TEMP_NUM, 8, 13) AS ExtractString 
    INTO x FROM USER_REGISTRATION_DETAILS where sl_no=inc;

    SET num= CONVERT(x,int);

    IF (num%2=0) THEN
        SELECT num;
    END IF;
       SET inc:=inc+1;
    END WHILE;
END$$
DELIMITER ;

in the above code SET num= CONVERT(x,int); gives the correct output as well as SET num:= CONVERT(x,int);

I am beginner of stored procedure so dont know at expert level

`:=` operator is mainly used for assignment operations inside queries. Inside a query you cannot use `=` because this is reserved for comparisons. If you want to use `SET` in a separate statement, i.e. not in the context of a query, then you are free to use either of the operators. — Giorgos Betsos, Mar 21 '18 at 07:08
Read the manual https://dev.mysql.com/doc/refman/5.5/en/assignment-operators.html — P.Salmon, Mar 21 '18 at 07:17
thanks @GiorgosBetsos for your response , can you share me any document or link for this so i can get depth knowledge about it — Bikrant Jena, Mar 21 '18 at 07:24

DineshDB · Answer 1 · 2018-03-21T07:35:20.670

From John Woo's answer:

Both of them are assignment operators but one thing I can find their differences is that = can be used to perform boolean operation while := cannot.

valid: SUM(val = 0)
Invalid: SUM(val := 0)

FROM User-Defined Variables

One more thing, You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements.

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 | 
+------+------+------+--------------------+

And also check this link, hope it helps you,

What is the difference between := and = mysql assignment operator

why we can use = and := both with set?

1 Answers1