用户系统-MySQL数据库中的多个角色 [英] User System - Multiple Roles in MySQL Database

问题描述

因此，我正在尝试创建一个基本的用户系统，并且在该系统内，我希望用户能够具有多个角色.

So I am in the process of attempting to create a basic user system and within this system I want users to be able to have multiple roles.

例如，我的角色如下:管理员，活动组织者，捐赠者

Say for example I have the roles as follows: Administrator, Events Organiser, Donator

将这些多个角色分配给用户，然后检查它们是否具有显示特定权限的这些角色中的最佳方法是什么.

What is the best way to assign these multiple roles to a user and then check if they have any of these roles for showing certain permissions.

如果每个人只有一个角色，那将不是问题，因为我只分配说Administrator = 10，Organizer = 5和Donator = 1，然后执行if语句检查MySQL数据是否为等于这三个数字中的任何一个.

If it was only one role per person then it wouldn't be a problem as I'd just assign say Administrator = 10, Organiser = 5 and Donator = 1 and then do an if statement to check if the MySQL data is equal to any of those three numbers.

我无法想象有一种方法可以添加一个MySQL字段并用"Administrator，Donator"填充它，因此用户将同时拥有这两个角色?

I can't imagine there is a way to add a MySQL Field and fill it with say "Administrator,Donator" and therefore that user would have both of those roles?

是否只是一种情况，我需要创建3个单独的字段并在这些字段中放入0或1，然后分别检查每个字段?

Is it just a case of I would need to create 3 separate fields and put a 0 or a 1 in those fields and check each one separately?

推荐答案

使用多个表并将它们联接:

Use multiple tables and join them:

User
--------------
id    name
 1    test

Role
--------------
id    name
 1    Donator
 2    Organizer
 3    Administrator

User_Role
--------------
id    user_id    role_id
 1    1          1
 2    1          3


SELECT * FROM User u 
    LEFT JOIN User_Role ur ON u.id = ur.user_id
    LEFT JOIN Role r ON ur.role_id = r.id
WHERE r.name = "Administrator";

如果您知道只有3个角色并且很容易记住，则查询会更容易.

The query is easier if you know you only have 3 roles and they are easy to remember.

SELECT * FROM User u LEFT JOIN User_Role ur ON u.id = ur.user_id WHERE ur.role_id = 3;

这篇关于用户系统-MySQL数据库中的多个角色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！