如何解决这个简单的问题 - 对用户隐藏产品组
所以我有一个具有如下结构的数据库：
[user_table] user_id, name, active
"john", "John S. Smith", true
[group_table] group_id, description, active
"abc", "test group", "true"
"cba", "second group", "true"
"bca", "third group", "true"
所以现在我想从“john”中隐藏“abc”组。 我创建一个名为“hide_group”的新表，其中包含字段 user_id 和 group_id。我插入适当的值，一切都很好。但它并没有得到很好的优化。如果我有 20 个用户和数百个组怎么办？这将意味着数百个新记录。 有没有更优化的方法来处理关系？我正在使用 MyISAM 引擎，如果我记得的话，我不能使用外键。 我正在阅读 mysql 文档，但它对我来说仍然很模糊。也许有人可以指出我正确的方向？
编辑，我的表结构是这样的：
[group]
group_id, group_description
[group_hide]
group_id, user_id, hide
我想选择“组”表下的所有组。除了那些隐藏在 group_id 表中的。我该怎么做？现在，我可以使其仅选择 group_hide 表内的所有记录（这不是我需要的），或者仅选择 group_hide 表内的所有记录，忽略 group_hide 中的所有记录。我希望它不要选择应该隐藏的组，但其他所有内容都应该可见。
So I have a database that has a structure something like this:
[user_table] user_id, name, active
"john", "John S. Smith", true
[group_table] group_id, description, active
"abc", "test group", "true"
"cba", "second group", "true"
"bca", "third group", "true"
So now I would like to hide "abc" group from "john".
I create a new table called "hide_group" with field user_id and group_id. I insert appropriate values and everything is nice. But it is not very well optimized. What if I have 20 users and hundreds of groups? That would mean hundreds of new records.
Is there a more optimized way to do this with relationships? I am using MyISAM engine, and if I recall, I can't use foreign key with it.
I am reading mysql documentation but it is still a blur to me. Maybe someone could point me at the right direction?
edit, my table structure is something like this:
[group]
group_id, group_description
[group_hide]
group_id, user_id, hide
I want to select all groups that are under "group" table. Except the ones that are hidden inside the group_id table. How can I do this? Right now I can make it either only select all records inside the group_hide table (which is not what I need), or just all records inside group, ignoring all that is in group_hide. I would like it not to select groups that are supposed to be hidden, but everything else should be visible.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您可以以某种方式对用户及其组可见性进行分类，您可以考虑定义某些“角色”。因此，关联将在角色（例如“管理员”或“默认”或“主持人”）和组之间以及角色和用户之间进行。
例如，您将创建一个包含 ID 和
name的表roles、一个包含 2 列role_id的表roles_users和user_id和另一个表hidden_categories_roles，其中包含 2 列hidden_category_id和role_id。不要忘记在两个表的两列上都放置索引以加快查询速度。如果用户差异很大以至于无法进行分类，那么您的方法就是正确的，您应该简单地在新表的两列上定义索引以加快查询速度。
If you can categorize the users and their group visibility somehow, you might consider defining certain "roles". So the associations would be between a role (say "Admin" or "Default" or "Moderator") and the groups, and between roles and users.
E.g. you would create a table
roleswith an ID and aname, a tableroles_userswith 2 columnsrole_idanduser_idand another tablehidden_categories_roleswith 2 columnshidden_category_idandrole_id. Don't forget to put indexes on both columns in both tables to speed up queries.If the users are so different that they can't be categorized, your approach is the correct one, and you should simply define indexes on both columns of your new table to speed up queries.