count syntax

[BubikolRamios]

select id_galery,count(id)=2 from galery_optional_inf
where id in (13)

This is legal, as it executes. Any explanation what "=2" does ?

[Old Pedant]

It compares the value of COUNT(id) with 2 and produces either TRUE or FALSE.

Because MySQL doesn't support a *true* boolean type, TRUE is converted to the integer (or bit) value 1. FALSE becomes 0.

mysql> select * from zap;
+------+-------+
| id   | flags |
+------+-------+
|    1 |    10 |
|    2 |     6 |
|    3 |     9 |
+------+-------+
3 rows in set (0.25 sec)

mysql> select count(id) = 2 from zap;
+---------------+
| count(id) = 2 |
+---------------+
|             0 |   [in other words, "FALSE"]
+---------------+
1 row in set (0.05 sec)

mysql> select count(id) = 3 from zap;
+---------------+
| count(id) = 3 |   [in other words, "TRUE"]
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

[BubikolRamios]

Is there any practical/performance use of it ?
Besides that it works I cant see any.

[Keleth]

Well, lets say you want to make a forum with a VIP club... eg, everyone over a certain number of posts. You can do COUNT(*) = 1000 or COUNT(*) > 1000, etc, and it'll show if someone is or isn't a VIP.

[Old Pedant]

Good example, Keleth. It's nothing that the host language (e.g., PHP/ASP/JSP) couldn't do, of course, but sometimes it's nice to do it in the query, instead.

[BubikolRamios]

Thanks to both.