how to count values with using find_in_set?

Question

In my db, uid is the autoincrement value indicates user ids and u_follow shows the user that follows other users with their uid seperated comma. i want to count that how many followers has each user. How can i do that ?

uid        u_follow
1          2,3
2          1,3
3          1,2
4          NULL
5          2,3,4

Answer 1

Store one value per column, otherwise you just can't do relational queries. See this excellent discussion for an introduction to database normalization.

users

uid
...

followers

uid u_follow
1   2
1   3
2   1
2   3
3   1
3   2
5   2
5   3
5   4

Then:

select u_follow, count(*) as num_followers from followers group by u_follow

If you want to include users with no followers do something like:

with a as (
  select u_follow, count(*) as num_followers
  from followers group by u_follow
)
select users.uid, coalesce(a.num_followers,0)
from users outer join a on users.uid = a.u_follow