mysql syntax

Question

In response to another question here on stackoverflow (How do you select every n-th row from mysql), someone supplied this answer:

SELECT * FROM ( SELECT @row := @row +1 AS rownum, [column name]
FROM ( SELECT @row :=0) r, [table name] ) ranked WHERE rownum % [n] = 1

Could someone provide or point me to more information about the syntax usage here. I'm not familiar with the use of := ?

(I'm not able to ask for a further explanation in the comments due to points.)

Thanks.

Answer 1

To me it looks like it's exactly the same as an =.

Answer 2

a = b in MySQL compares a to b and returns true if they're equal, or false otherwise. @a := b, on the other hand, sets the value of @a to b.

Basically, = is the comparison operator ("is equal to"), and := is the assignment operator ("set equal to").

EDIT: I just found out that you can use = as the assignment operator in SET statements, as there's no such thing as comparisons in those. In SELECT statements, you have to use := for assignment.

Answer 3

From http://dev.mysql.com/doc/refman/5.0/en/user-variables.html:

"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=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

"