MySQL 在带有逗号分隔列表的列上搜索多个值

Question

我有一个 mysql 表，它驱动我网站上的菜单。我为每个菜单项有一列，定义哪些用户角色可以查看该菜单项。

我将“授权”角色存储为逗号分隔列表

我正在尝试构建一个查询，该查询允许我提供用户所处的角色列表并查找允许他们查看的菜单项。我尝试使用 LIKE %ROLE1% %ROLE2% 等...但这仅返回所有这些角色中的菜单项。

如何找到用户的角色和相应的菜单项？

Answer 1

我同意每个人都会告诉你的，你的数据库需要 normalization更具体地说，考虑 First Normal Form 。您应该创建一个 MENU_ITEM_ROLE 表，用作 ROLE 和 MENU_ITEM 之间的桥接表。它将保存多个记录，其中包含 MENU_ITEM 实体的主键以及访问它所需的相应角色。

但是，鉴于您所处的情况，您说您尝试过:LIKE %ROLE1% %ROLE2% 等它只返回所有角色都可用的菜单项。我认为这可能是因为您在不同的 LIKE 表达式之间使用了 AND 运算符。在这种情况下，我认为合适的运算符是“OR”，并且只会检索至少具有一个“OR”角色的菜单项。

您应该考虑到，如果您的角色名称具有相似的值，则此解决方案以及尝试通过字符串操作解决此问题会导致更多麻烦。例如。角色 'ROLE_ADMIN' 上有一个菜单项 A，角色 'ROLE_ADMIN_USERS' 上有另一个菜单项 B，那么用户具有角色 'ROLE_ADMIN' 将能够看到菜单项 A 和 B。

考虑到这种情况，最可靠的解决方案是使用对 MENU_ITEM 表的角色字段进行标记的函数，然后搜索匹配项。在这个方向上我会使用类似 FIND_IN_SET 的东西.

例如

select * from menu_item where find_in_set('ROLE1', roles)>0

也引用here .