What does the := operator mean in mysql?

I have a mysql table (scho_id,school_name,school_views).

I was looking for a mysql query to get rank of schools on the basis of school_views.

I found this solution on stackoverflow.

SET @points := -1, @num := 0;
SELECT scho_id
, school_views
, @num := if(@points = school_views, @num, @num + 1) as school_rank
, @points := school_info.school_views as dummy
FROM school_info
ORDER BY school_views desc, scho_id asc;

This solved my problem but I notice a new operator := in this query. I am curious to know the meaning and uses of this operator.


Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

In MySQL, := is an assignment operator:

SELECT @foo := 'bar';    // variable 'foo' now has value 'bar'
return value: 'bar'

while = is an equality test:

SELECT @foo = 'hi mom'; // does variable 'foo' have the value 'hi mom';
return value: false   ('bar' == 'hi mom' -> false)

Note that you CAN do both equality testing AND assignment with set queries:

SET @foo = 'bar' = 'baz';

which will cause @foo to be assigned false, the boolean result of 'bar' = 'baz'. It executes as the following:

SET @foo = ('bar' = 'baz');
SET @foo = false;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Notify of

Inline Feedbacks
View all comments
Would love your thoughts, please comment.x