Instead of trying to parse a string column, use the database's built-in relational capabilities as they were intended to be used.
First, we need three tables, Users, Roles, and UserRoles. UserRoles is a cross reference table that joins Users to their Roles.
CREATE TABLE Users
(
UserID int NOT NULL primary key
, UserName varchar(100) NOT NULL
);
CREATE TABLE Roles
(
RoleID int NOT NULL primary key
, RoleName varchar(50) NOT NULL
);
CREATE TABLE UserRoles
(
UserID int NOT NULL
, RoleID int NOT NULL
, FOREIGN KEY UserRolesUserIDFK (UserID) REFERENCES Users (UserID)
, FOREIGN KEY UserRolesRoleIDFK (RoleID) REFERENCES Roles (RoleID)
, PRIMARY KEY (UserID, RoleID)
);
In the UserRoles
table above, you'll note we have two foreign keys, one each for the User entry and the associated Role entry. This provides referential integrity that prevents deletion of a role that is in use by a user, and adding a user to a non-existent role. I've also added a compound primary key to the table, on (UserID, RoleID)
, which means you cannot add a user to a single role more than once. This eliminates the need to DISTINCT
the output queries below, which for a system with a large number of Users and Roles would make a significant positive difference to performance.
Next, we'll insert some sample data:
INSERT INTO Users (UserID, UserName)
VALUES (1, 'Sarah')
, (2, 'Hannah');
INSERT INTO Roles (RoleID, RoleName)
VALUES (1, 'Administrator')
, (2, 'User');
INSERT INTO UserRoles (UserID, RoleID)
VALUES (1, 2)
, (2, 1)
, (2, 2);
Now, when we want to get the results, we do this simple query:
SELECT u.UserName
, r.RoleName
FROM Users u
INNER JOIN UserRoles ur ON u.UserID = ur.UserID
INNER JOIN Roles r ON ur.RoleID = r.RoleID
ORDER BY u.UserName;
Results look like:
╔══════════╦═══════════════╗
║ UserName ║ RoleName ║
╠══════════╬═══════════════╣
║ Hannah ║ Administrator ║
║ Hannah ║ User ║
║ Sarah ║ User ║
╚══════════╩═══════════════╝
If you need the role membership on a single line, along with the count of the number of roles each user is a member of, then the following should work:
SELECT u.UserName
, (
SELECT GROUP_CONCAT(r.RoleName SEPARATOR ', ')
FROM Roles r
INNER JOIN UserRoles ur ON r.RoleID = ur.RoleID
WHERE ur.UserID = u.UserID) AS Roles
, (
SELECT COUNT(1)
FROM UserRoles ur
WHERE ur.UserID = u.UserID) AS CountOfRoles
FROM Users u
ORDER BY u.UserName;
Output looks like:
╔═══════════╦══════════════════════╦══════════════╗
║ UserName ║ Roles ║ CountOfRoles ║
╠═══════════╬══════════════════════╬══════════════╣
║ Hannah ║ Administrator, User ║ 2 ║
║ Sarah ║ User ║ 1 ║
╚═══════════╩══════════════════════╩══════════════╝
The queries above can efficiently use indexes without any headaches, and they easier to comprehend than a a large number of REPLACE
or CASE
statements.
Multiple REPLACE
or CASE
statements, where not necessary, only introduce complexity that may mask the real intention, and confuse the query optimizer, producing poor plans and possibly slow results.
A significant benefit of using discrete rows for each user/role relationship is you can have an unlimited number of roles per user, without needing an unlimited size string column to hold the membership details. This also makes it super easy to determine if someone is a member of a specific role, or to see all the members of specific roles.
For example, to see who's a member of the User
role, you could do this:
SELECT u.UserName
FROM Users u
INNER JOIN UserRoles ur ON u.UserID = ur.UserID
INNER JOIN Roles r ON ur.RoleID = r.RoleID
WHERE r.RoleName = 'User'
ORDER BY u.UserName;
Results:
╔══════════╗
║ UserName ║
╠══════════╣
║ Hannah ║
║ Sarah ║
╚══════════╝
This design also makes it easy to see who is not a member of a specific role:
SELECT u.UserName
FROM Users u
WHERE NOT EXISTS (
SELECT 1
FROM UserRoles ur
INNER JOIN Roles r ON ur.RoleID = r.RoleID
WHERE r.RoleName = 'Administrator'
AND ur.UserID = u.UserID
);
Results:
╔══════════╗
║ UserName ║
╠══════════╣
║ Sarah ║
╚══════════╝
I've created a fiddle for the above code here.