我们有以下 account 表(MySQL RDBMS):
We have the following account table (MySQL RDBMS):
+----+-------------------+----------------+-----------------+-----------------+ | id | username | admin_for_blog | editor_for_blog | author_for_blog | +----+-------------------+----------------+-----------------+-----------------+ | 1 | author_only | NULL | NULL | 1 | | 2 | editor_and_author | NULL | 2 | 2 | | 3 | admin_only | 3 | NULL | NULL | +----+-------------------+----------------+-----------------+-----------------+我们正在迁移到基于角色的授权,因此我们需要为上述现有数据找到新的角色.角色是:AUTHOR、EDITOR 和 ADMIN.一个用户可以链接到多个角色.角色将存储为逗号分隔值(顺序无关紧要).在上面的数据中,输出应该是:
We are migrating to a role-based authorization, so we need to find the new roles for the above existing data. Roles are: AUTHOR, EDITOR and ADMIN. A user can be linked to multiple roles. Roles will be stored as comma-separated values (order doesn't matter). In the above data, the output should then be:
+----+-------------------+---------------+----------------+-----------------+-----------------+ | id | username | roles | admin_for_blog | editor_for_blog | author_for_blog | +----+-------------------+---------------+----------------+-----------------+-----------------+ | 1 | author_only | AUTHOR | NULL | NULL | 1 | | 2 | editor_and_author | AUTHOR,EDITOR | NULL | 2 | 2 | | 3 | admin_only | ADMIN | 3 | NULL | NULL | +----+-------------------+---------------+----------------+-----------------+-----------------+所以基本上:
- 如果author_for_blog 不为空,则添加角色AUTHOR
- 如果editor_for_blog不为空,则添加角色EDITOR
- 如果admin_for_blog 不为空,则添加角色ADMIN
- if author_for_blog is not null, then add role AUTHOR
- if editor_for_blog is not null, then add role EDITOR
- if admin_for_blog is not null, then add role ADMIN
关于如何做到这一点的任何提示?我已经能够为每个用户附加一个角色,但我找不到一种方法来管理以逗号分隔的多个角色:
Any hint on how to to this? I've been able to attach one role per user, but I can't find a way to manage multiple roles separated by commas:
select username, case admin_for_blog is not null when true then 'ADMIN' else case editor_for_blog is not null when true then 'EDITOR' else case author_for_blog is not null when true then 'AUTHOR' else 'error!' end end end from account 推荐答案您可以使用函数 concat_ws 跳过 null 值.
You can use the function concat_ws which skips the null values.
select username, concat_ws(',' ,case when admin_for_blog is not null then 'ADMIN' end ,case when editor_for_blog is not null then 'EDITOR' end ,case when author_for_blog is not null then 'AUTHOR' end ) from account更多推荐
如何从现有数据创建新的逗号分隔列?
发布评论