What is the difference between: = and = in MySQL?

Question

mysql> set @num := 1; Query OK, 0 rows affected (0.00 sec) mysql> set @num = 0; Query OK, 0 rows affected (0.00 sec) mysql> select @num; +------+ | @num | +------+ | 0 | +------+ 1 row in set (0.00 sec) 

Both seem to work.

Answer 1

In short, when using SET both of them act as assignment operators, but in any non-stationary operators := used for evaluation, and = checks equality.

For SET, you can use either = or: = as the assignment operator.

You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator should be: = and not = because = is considered as a comparison operator in operators without SET

 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 | +------+------+------+--------------------+ 

Adapted from MySQL 8.4 User Variables

Answer 2

Both assignment operators.

: = was created to be clearer, since = is also an equivalence operator in SQL. WHERE x = 1; (most programming languages == )