What's the difference between := and = in MySQL?

mysql> set @num := 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @num = 0;
Query OK, 0 ro开发者_StackOverflow中文版ws affected (0.00 sec)

mysql> select @num;
+------+
| @num |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

Seems both works.

In short, when using SET they both act as assignment operators, but in any non-set statements, := is for assingment and = checks for equality.

For SET, either = or := can be used 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 must be := and not = because = is treated as a 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 | 
+------+------+------+--------------------+

Taken from MySQL 8.4 User Defined Variables

Both are assignment operator.

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