:=

MySQL 8.4

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.

Syntax

MYSQL

SELECT @var1, @var2;
NULL, NULL
SELECT @var1 := 1, @var2;
1, NULL
SELECT @var1, @var2;
1, NULL
SELECT @var1, @var2 := @var1;
1, 1
SELECT @var1, @var2;
1, 1

SELECT @var1:=COUNT(*) FROM t1;
4
SELECT @var1;
4

Examples

Example

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
SELECT @var1 := 1, @var2;
1, NULL
SELECT @var1, @var2;
1, NULL
SELECT @var1, @var2 := @var1;
1, 1
SELECT @var1, @var2;
1, 1

SELECT @var1:=COUNT(*) FROM t1;
4
SELECT @var1;
4

Example 2

You can make value assignments using := in other statements besides SELECT, such as UPDATE, as shown here:

MYSQL

SELECT @var1;
4
SELECT * FROM t1;
1, 3, 5, 7

UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT @var1;
1
SELECT * FROM t1;
2, 3, 5, 7