将MySQL转换为新系统？

Question

I have an old MySQL table structure that I want to upgrade to a new one, I'll explain the structures below.

Its for a permission system, and the table represents what rank id's get what permission id's

Table [Old]:
permission_id the id of the permission given
rank the rank id that gets the permission

Now, for the old table system, it uses an extra call to permissions table to find the name of the permission linked with permission_id, in my new system I just store permission rights by permission's name not the permissions id.

Table [New]:
permission_name the name of the permission given
allowed_ranks a string of rank ids, seperated by ,

How would I convert all the records to 1 simple record for all permissions, but convert the rank column to the new allowed_ranks with a seperator of ,?

Answer 1

Do not convert to the new system if you have any choice. You can avoid "an extra call to permissions" by just joining the tables.

SELECT * 
FROM old_table AS t 
INNER JOIN permissions AS p ON t.permission_id = p.permission_id 
WHERE t.rank = ?
;

If you need to display the information in the new table format, just use this query:

SELECT p.permission_name, GROUP_CONCAT(t.rank) AS allowed_ranks
FROM old_table AS t 
INNER JOIN permissions AS p ON t.permission_id = p.permission_id 
GROUP BY p.permission_name
;

Note: you could use the second query to populate the new table, but it is pretty much guaranteed you will quickly regret moving to it; at least, once you have to find all permissions associated with a specific rank.