在MySQL中使用Spaces搜索 [英] Search in MySQL with Spaces

问题描述

我正在创建类似于 Instagram 的应用。我有一个照片表和 likers 列，显示 user_id 已喜欢照片。例如，如果 user_id 1和5像 photo_id 51，则 photo_id 51的 likers 栏会显示 1 5 。如图所示， user_id s用空格分隔。现在，像Instagram，我想要能够显示哪些用户名喜欢特定的照片。为此，我需要能够分别从 likers 列中获取所有 user_id 。任何人都可以帮助我如何做到这一点，或者建议一种替代方法？

I am creating an app similar to Instagram. I have a photos table with a likers columns, showing which user_ids have liked a photo. For example, if user_id 1 and 5 like photo_id 51, then photo_id 51's likers column will show 1 5. As seen, the user_ids are separated by spaces. Now, like Instagram, I want to be able to show which username's are liking a specific photo. For this, I need to be able to get all the user_ids from the likers column separately. Can anyone help me with how to do this, or suggest an alternative way?

推荐答案

你几乎肯定不想在一个字段中存储多个数据。你想要有这样的：

You're approaching this the wrong way. You almost certainly don't want to store multiple pieces of data in one field. You want to have something like this:

Table: User
- id
- blah blah

Table: Photo
- id
- blah blah

Table: Like
- user_id
- photo_id

这将允许您编写非常灵活的查询，包括您所描述的：

This will allow you write very flexible queries, including what you described:

SELECT u.id
FROM User AS u
JOIN Like AS li ON li.user_id = u.id
WHERE li.photo_id = $current_photo_id

这是一个名为数据库规范化。如果您希望您的申请成功，请阅读更多信息。

This is a topic known as database normalization. Please read more about it if you want your application to succeed.

这篇关于在MySQL中使用Spaces搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！