存储用户ID列表的最佳方法 [英] Best method for storing a list of user IDs

问题描述

我现在正在使用PHP/MySQL评分系统.为了使用户能够对用户进行评分，必须登录.每个用户都有一个唯一的"UID".网站上将有多个评级实例(就我而言，每个游戏一个)，我需要一种有效的方法来将UID列表存储在MySQL行中(评级系统的每个实例的评级表中一个MySQL行) )保持对谁投票的统计.

I'm working on a PHP/MySQL rating system right now. For the user to be able to rate the user has to log in. Each user has a unique "UID". There will be multiple rating instances on the website (one for every game, in my case) and I need an efficient way of storing a list of UIDs in a MySQL row (one MySQL row in the ratings table for each instance of the rating system) to keep a tally of who has voted.

在其他系统中，我看到该列表存储在序列化的PHP数组中.每次用户投票时，都必须提取，反序列化序列化的数组，插入新的UID，重新序列化数组，并更新MySQL行.每次加载页面时，都必须再次对该序列表进行反序列化，并检查查看该页面的用户是否已投票，以确保用户没有两次投票.

I've seen in other systems that the list is stored in a serialized PHP array. Every time a user votes, then serialized array has to extracted, unserialized, the new UID is inserted, the array is re-serialized, and the MySQL row is UPDATEd. Every time the page is loaded, that list has to once again be unserialized and checked to see if the user viewing the page has voted yet to make sure the user doesn't vote twice.

这似乎效率低下且麻烦. MySQL是否具有内置列表功能来帮助提高此过程的效率?还有其他更聪明的方法可以解决此问题吗?

This seems kind of inefficient and cumbersome. Does MySQL have a built in list function to help this process be more efficient? Are there any more clever ways I could fix this problem?

我考虑了一种可能的选择，那就是忘记序列化并将UID存储在MySQL数据库的TEXT类型字段中.我只在每个UID后面附加一些非数字字符(例如，句点[.]).要添加用户条目，我只需将UID连接到TEXT字段的末尾，然后连接一个句点.当检查用户是否已经投票时，我可以只是"SELECT * FROM table WHERE投票='％$ UID.％';".这项工作会更有效吗?还是有一种更优雅的方式来完成工作?

I've considered one possible alternative which is forget the serializing and store the UIDs in a TEXT type field in the MySQL database. I would just append some non-numeric character after each UID (let's say a period [.]). To add a user entry I would just concatenate the UID onto the end of the TEXT field and then a period. When checking if the user has already voted I could just "SELECT * FROM table WHERE votes = '%$UID.%';". Would this work more efficiently or is there a more elegant way of getting the job done?

有关表结构的后续帖子... 高效评分系统的MySQL表结构

Follow-up post about the table structure... Efficient MySQL table structure for rating system

推荐答案

效率低下.在关系方面，您所拥有的是用户与游戏之间的多对多关系.用户可以在许多游戏上投票.一个游戏可以被许多用户投票.解决方案是拥有一个联接表:

It is inefficient. What you have here in relational terms is a many-to-many relationship between users and games. A user can vote on many games. A game can be voted on by many users. The solution for this is to have a join table:

USERS (uid, name, ...)
GAMES (gid, name, ...)
VOTES (id, uid, gid, ...)

其中uid和gid是返回其各自表的外键.

Where uid and gid are foreign keys back to their respective tables.

如果有人投票，请在VOTES中插入一条记录.

If someone votes, insert a record in VOTES.

要获取游戏的投票列表:

To get a list of votes for a game:

$get = mysql_query("SELECT * FROM votes WHERE gid = $game_id");
...

要获取用户投票的列表，请执行以下操作:

To get a list of the user's votes:

$get = mysql_query("SELECT * FROM votes WHERE uid = $user_id");
...

以此类推.

不要加入数组并将其存储在单列中.您避免这种情况是正确的.

Don't join an array and store it in a single column. You're right to avoid that.

这篇关于存储用户ID列表的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！