What is the difference between := and = operators?
Answer
In a SET
statement, both :=
and =
are assignment operators.
In a SELECT
statement, :=
is an assignment operator and =
is an equality operator.
Example
SET @a = 1, @b := 2;
SELECT @a, @b; -- 1, 2
SELECT @a = @b; -- 0 (false)
SELECT @a := @b; -- 2
SELECT @a, @b; -- 2, 2
SELECT @a = @b; -- 1 (true)
From your other question I know that you mean in the use case of
SELECT variable = column FROM table;
Go ahead and see for yourself...
CREATE TABLE foo (id int);
INSERT INTO foo VALUES (1), (2), (3);
SET @asdf = 2;
SET @asdf := 2; /*those are the same*/
/*As SET is always an assignment operation, it doesn't matter here if you write it with := or with =*/
SELECT id, @asdf, @asdf = id FROM foo;
returns
+------+-------+------------+
| id | @asdf | @asdf = id |
+------+-------+------------+
| 1 | 2 | 0 |
| 2 | 2 | 1 |
| 3 | 2 | 0 |
+------+-------+------------+
In the result a 0
in the last column equals false
, a 1
equals true
.
SELECT @asdf := id FROM foo;
returns
+-------------+
| @asdf := id |
+-------------+
| 1 |
| 2 |
| 3 |
+-------------+
because the value of id
gets assigned to the variable @asdf
If you now issue a
SELECT @asdf;
it returns
+-------+
| @asdf |
+-------+
| 3 |
+-------+
because the row containing 3
was last selected.
SELECT @asdf := id FROM foo ORDER BY id DESC;
returns
+-------------+
| @asdf := id |
+-------------+
| 3 |
| 2 |
| 1 |
+-------------+
Now
SELECT @asdf;
returns
+-------+
| @asdf |
+-------+
| 1 |
+-------+
Difference is clear now?