使用MySQL的时候经常会遇到给变量赋值和比较的情况，所以:=和=两种赋值符号就经常出现在如下的复杂语句中：

select distinct ConsecutiveNums from(select if(num=@pre,@i:=@i+1,@i:=1),if(@i=3,num,null) ConsecutiveNums,@pre:=numfrom logs,(select @i:=1) t1,(select @pre:=(select min(num) from logs)-1) t2) numswhere ConsecutiveNums is not null;

那么二者到底有什么区别呢？

官方文档中 := 的描述如下：

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 statement. You can perform multiple assignments in the same statement.

Unlike= , the := operator is never interpreted as a comparison operator. This means you can use in any valid SQL statement (not just in statements) to assign a value to a variable.

这段话的意思是说:=永远是赋值的意思，也就是把右面的标量值赋值给左面的表达式或者变量（注意加粗部分），就连select表达式中都是赋值的意思。

官方文档中 = 的描述如下：

This operator is used to perform value assignments in two cases, described in the next two paragraphs.

Within a 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 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 statement.

In the SET clause of an 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 are met. You can make multiple assignments in the sameSET clause of an statement.

In any other context, = is treated as a comparison operator.

这段话意思是 = 只有在set 语句或update 的set子句中才是是赋值的意思（注意加粗部分），其他情况下都是比较操作符，也就是判等的意思，看下面的例子。

set @i :=1;select @i:=@i+1,@i=1,@i;

运行结果是什么呢？如下图

1）set @i:=@i+1 是定义用户变量@i，并用set子句赋值为1；

2）@i:=@i+1让@i加上1再赋值给@i，此时@i的值为2；

3）@i=1，最让人误解的一句就是这，这个是比较@i和1是否相等的意思，相等返回1，不等返回0；

4）@i，为了证明上述事实，再次显示@i的值为之前修改过的2。

综上就是MySQL中:=和=的区别，在编写复杂的SQL时注意区分，不要弄混了！最佳实践是赋值的时候所有的地方都使用:=，包括update语句中。

用行动记录数据库的点点滴滴！