Mysql：获取计数逗号分隔值与Like [英] Mysql: Getting Count of Comma Separated Values With Like

问题描述

我决定使用favs（将用户的ID标记为收藏夹）作为favs列中的逗号分隔列表，该列也在包含发件人，网址，内容等的邮件表中。

I decided to use favs (id's of users which marked that post as a favorite) as a comma separated list in a favs column which is also in messages table with sender,url,content etc..

但是当我尝试用这样的查询计数这些行：

But when I try to count those rows with a query like:

select count(id) 
from messages 
where favs like '%userid%' 

它返回一个错误的结果，因为所有的id可能是另一个的

of course it returns a wrong result because all id's may be a part of another's

例如，当查询id = 1时，它也增加了任何其他内容的计数器被收藏用户ID 11 ...

For example while querying for id=1 it also increase the counter for any other content that is favorited by user id 11...

你能告诉我你的想法或任何解决方案，使这个系统工作吗？

Can you please tell me your idea or any solution to make this system work?

推荐答案

有几个或的，你可以有一个丑陋的解决方案：

With a few or's, you can have an ugly solution:

select count(id) from messages where favs like 'userid,%' or favs like '%,userid,%' or favs like '%,userid'

这可能是一个更优雅的解决方案，但是至少会返回你想要的结果。我相信。

There's likely a more elegant solution, but that one will at least return the result you're looking for, I believe.

这篇关于Mysql：获取计数逗号分隔值与Like的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！