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. WHERE ur.UserID = u.UserID) AS CountOfRoles INNER JOIN UserRoles ur ON r.RoleID = ur.RoleID SELECT GROUP_CONCAT(r.RoleName SEPARATOR ', ') 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 INNER JOIN Roles r ON ur.RoleID = r.RoleID INNER JOIN UserRoles ur ON u.UserID = ur.UserID
Now, when we want to get the results, we do this simple query: SELECT u.UserName
Next, we'll insert some sample data: INSERT INTO Users (UserID, UserName) 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. 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 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. In the UserRoles table above, you'll note we have two foreign keys, one each for the User entry and the associated Role entry. , FOREIGN KEY UserRolesRoleIDFK (RoleID) REFERENCES Roles (RoleID) , FOREIGN KEY UserRolesUserIDFK (UserID) REFERENCES Users (UserID) UserRoles is a cross reference table that joins Users to their Roles. Instead of trying to parse a string column, use the database's built-in relational capabilities as they were intended to be used.įirst, we need three tables, Users, Roles, and UserRoles. I can't understand why one query runs quickly and the other very slowly (seconds versus minutes, after a few mins I killed the slow query).Įxplain statement output for both is the same:
#REPLACE COMMAND IN MYSQL CODE#
Ideally I would use the REPLACE function instead of a CASE statement, as it seems easier to scale out in terms of expanding the code and less risk to manage. When string_of_user_role_ids= "6,7,8" then 'Farmer, Journalist, Teacher' When string_of_user_role_ids= "7,8" then 'Journalist, Teacher' When string_of_user_role_ids= "6,8" then 'Farmer, Teacher' When string_of_user_role_ids= "6,7" then 'Farmer, Journalist' When string_of_user_role_ids= "8" then 'Teacher' When string_of_user_role_ids= "7" then 'Journalist' , case when string_of_user_role_ids= "6" then 'Farmer' this runs quickly, but is more difficult to keep adding in multiple new when clauses whenever a new user role is added , replace(replace(replace(replace(replace(string_of_user_role_ids, Note, the underlying user_roles table is of format - this runs slowly The below query runs extremely slow using replace. Thus, we can say that the REPLACE statement performs two standard functions, DELETE and INSERT.Is it better to use CASE statements instead of REPLACE functions in MySQL when mapping a comma-separated-string field? First, it will delete the existing record, and then the newly updated record is added, similar to a standard INSERT command.
In the REPLACE statement, the updation performed in two steps. If the duplicate record found, the replace command will delete the existing row and then adds the new record in the table.If no matching value is found with the existing data row, then a standard INSERT statement is performed.The REPLACE command requires one of the two possible actions take place: In this case, we will use the REPLACE statement to perform our task. If we use the standard insert query for this purpose, it will give a Duplicate entry for PRIMARY KEY or a UNIQUE key error.
#REPLACE COMMAND IN MYSQL UPDATE#
This statement is required when we want to update the existing records into the table to keep them updated. This statement works the same as the INSERT statement, except that if an old row matches the new record in the table for a PRIMARY KEY or a UNIQUE index, this command deleted the old row before the new row is added. The REPLACE statement in MySQL is an extension of the SQL Standard.
0 kommentar(er)
