从逗号分隔的字段中选择 [英] Selecting from a comma separated field

问题描述

说我有一个subscribers表，对于每个这样的用户都有一行..

Say I have a subscribers table, that has a row for each user like this..

id name    subscribers
1  user1   user2,user3,user4
2  user2   user4,user5,user3
3  user3   user1,user6,user2    etc...

我想做的是，运行这样的select语句.

What I want to do is, run a select statement like this..

 SELECT subscribers from table where id = '1'

..然后，限制向我显示的订户数量 也就是说，如果我将其限制为2，则只能从table.subscribers WHERE id=1

.. And then, limit how many subscribers to show me i.e. If I limited it to 2, it would only SELECT "user2,user3" from table.subscribers WHERE id=1

我知道在用PHP选择全部内容后可以限制它，但是我不想遇到性能问题，如果每列中都有数百万个用户名...

I know I can limit it after selecting all with PHP but I don't want to run into performance problems, if there were millions of usernames in each column...

此外，这是设置subscibe/follow系统的最佳结构.还是有更好的方法?

Also, is this the best structure to set up a subscibe/follow system.. Or is there a better way?

推荐答案

您正在同一字段中存储多个值.这太糟糕了！

You are storing multiple values in the same field. This is bad!

您需要第二张表来表示订阅-它会有一个列userid和subscriberuserid(或类似名称).

You need a second table, to represent subscriptions - it would have a column userid and subscriberuserid (or something similar).

对于该用户拥有的每个订户，此表中都会有一条记录，其中包含该用户的userid(及其订户的userid).

For every subscriber that a user has, there would be a record in this table with that user's userid (and the userid of their subscriber).

然后，您可以限制自己的内心内容:

Then, you can limit to your hearts content:

SELECT subscribers.subscriberuserid
FROM subscribers
WHERE userid = 1
LIMIT 2

这篇关于从逗号分隔的字段中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！