:=

赋值运算符。使运算符左侧的用户变量获取其右侧的值。右侧的值可以是一个字面值、存储值的另一个变量，或任何产生标量值的合法表达式，包括查询结果（前提是该值为标量值）。您可以在同一个 SET 语句中执行多次赋值。您可以在同一语句中执行多次赋值。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.

与 = 不同，:= 操作符从不被解释为比较操作符。这意味着你可以在任何有效的 SQL 语句中（不仅仅是在 SET 语句中）使用 := 给变量赋值。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
mysql> SELECT @var1 := 1, @var2;
        -> 1, NULL
mysql> SELECT @var1, @var2;
        -> 1, NULL
mysql> SELECT @var1, @var2 := @var1;
        -> 1, 1
mysql> SELECT @var1, @var2;
        -> 1, 1

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

您可以在除了 SELECT 之外的其他语句中使用 := 进行值赋值，例如 UPDATE，如下所示：You can make value assignments using := in other statements besides SELECT, such as UPDATE, as shown here:

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

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

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

虽然也可以在单个 SQL 语句中使用 := 运算符同时设置和读取同一变量的值，但不推荐这样做。第 11.4 节，“用户定义变量” 解释了为什么应避免这样做。While it is also possible both to set and to read the value of the same variable in a single SQL statement using the := operator, this is not recommended. Section 11.4, “User-Defined Variables”, explains why you should avoid doing this.

=

此运算符用于在两种情况下执行值赋值，如下两段所述。This operator is used to perform value assignments in two cases, described in the next two paragraphs.

在SET语句中，= 被视为赋值运算符，它使操作符左侧的用户变量获得其右侧的值。（换句话说，当在SET语句中使用时，= 的处理方式与:=完全相同。）右侧的值可以是字面值、存储值的另一个变量，或者任何返回标量值的合法表达式，包括查询结果（前提是该值为标量值）。你可以在同一个SET语句中执行多个赋值操作。Within a SET statement, = is treated as an assignment operator that causes the user variable on the left hand side of the operator to take on the value to its right. (In other words, when used in a SET statement, = is treated identically to :=.) 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.

在 UPDATE 语句的 SET 子句中，= 也充当赋值运算符；在这种情况下，它使运算符左侧的列获得右侧给定的值，前提是满足 UPDATE 的任何 WHERE 条件。你可以在同一个 UPDATE 语句的 SET 子句中进行多重赋值。In the SET clause of an UPDATE statement, = also acts as an assignment operator; in this case, however, it causes the column named on the left hand side of the operator to assume the value given to the right, provided any WHERE conditions that are part of the UPDATE are met. You can make multiple assignments in the same SET clause of an UPDATE statement.

在任何其他上下文中，= 被视为 比较运算符。In any other context, = is treated as a comparison operator.

mysql> SELECT @var1, @var2;
        -> NULL, NULL
mysql> SELECT @var1 := 1, @var2;
        -> 1, NULL
mysql> SELECT @var1, @var2;
        -> 1, NULL
mysql> SELECT @var1, @var2 := @var1;
        -> 1, 1
mysql> SELECT @var1, @var2;
        -> 1, 1

更多信息，请参见 第 15.7.6.1 节，“SET 变量赋值语法”、第 15.2.17 节，“UPDATE 语句” 和 第 15.2.15 节，“子查询”。For more information, see Section 15.7.6.1, “SET Syntax for Variable Assignment”, Section 15.2.17, “UPDATE Statement”, and Section 15.2.15, “Subqueries”.