mysql - 将字符串映射到关系

Question

我正在创建一个可以将任务分配给不同用户的系统。问题是任务是通过一个名为 recipient 的字符串列映射的，该列最终映射到一组用户。此专栏的内容可能如下所示:

• has:tasks-update,tasks-access - 拥有 tasks-update 和 tasks-access 权限的用户。
• role:administrator - 具有管理员角色的用户。

现在我正在有问题地解决它。当我必须弄清楚谁有权访问特定任务时，这有点容易，但当用户需要知道“分配”给他们的任务时，这就很麻烦了。现在我正在解析每个收件人列以查看是否包含用户，不幸的是，这不太可行，因为它会带来巨大的性能成本。

我已经在适当的列上有了索引来加快查找速度。

对此的解决方案是，我会在收件人更改时解析收件人，然后将用户和任务之间的关系放在一个中间表中。虽然这让我可以快速查找分配给用户的任务，但它也变得有问题，因为现在我需要跟踪(例如)每次用户被授予管理员角色并将其同步到中间表。

我希望我能像现在这样在不牺牲性能的情况下深入了解解决这个问题，但也不必一直同步。

Answer 1

将任何内容的列表作为字符串存储在单个列中可能会导致各种问题

正如您已经遇到的那样.. 列表上的任何关系查找、插入、更新或删除操作首先需要对现有列表进行某种形式的解析

值得注意的是，引擎可能无法将此列上的任何索引用于这些任务，因为基于字符串的列(除了 FULL TEXT)上的索引仅在搜索字符串开头时真正有用

例如，

SELECT * 
  FROM site_user 
 WHERE recipients LIKE '%tasks-update%'

将无法在收件人列上使用索引

---

一个建议

我会将您当前的列表拆分成新的表格，例如

1. 角色 - id, name, …
   • 例如{3, '管理员',... }
2. permission - id, name, …
   • 例如{5, 'tasks-access',... }
   • 例如{9, '任务更新',... }
3. site_user - id, name, role_id, …
   • 例如{7, 'Jeff', 3,... }
4. site_user_permission - id, site_user_id, permission_id, …
   • 例如{1, 7, 5,…}
   • 例如{2, 7, 9,…}

在示例记录中，“Jeff”是“管理员”，并被分配了“任务更新”和“任务访问”权限

查找应该可以使用 JOIN 轻松实现，并且在添加或删除数据时保持一致。可以通过添加适当的外键和唯一索引来维护数据完整性

---

注意如果没有导致您出现问题的操作的具体示例，或者没有关于您打算如何使用用户角色和权限的更多详细信息，除了提出一般性建议外，很难做更多的事情