34

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.

moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
prograshid
  • 876
  • 3
  • 12
  • 32
  • If you simply check the mysql docs (https://dev.mysql.com/doc/refman/8.0/en/assignment-operators.html#operator_assign-value) you will see that..... they're wrong. Nice. – Joe Phillips Apr 07 '22 at 18:18

1 Answers1

52

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;
Marc B
  • 356,200
  • 43
  • 426
  • 500
  • So the `=` (equality test operator ) work as assignment operator if it is used with `SET` ? E.g - `SET @param='some value'` and with `UPDATE` statement e.g. `UPDATE table_name SET column_name='value'` – prograshid Sep 07 '16 at 22:10
  • 2
    yes. the first `=` in a `set` does assignment. a set query can't return anything, so it doesn't make much sense to have `=` do an equality test. the result of that test couldn't be returned. however, now that this will do testing AND assignment: `set @foo = 'bar' = 'baz';`. `@foo` will be assignment the boolean result of the `'bar' = 'baz'` equality test, e.g. false. – Marc B Sep 08 '16 at 14:07
  • It seem though, you cannot use := with declared variables in SELECT statements. – Timofeus Sep 23 '21 at 09:10
  • explained very well. thanks – Daniel_Ranjbar Feb 03 '23 at 14:10