更新数据库中值为拼接字符串的字段
我们开发系统涉及权限的时候,会处理到用户和角色的关系
通常情况下,我们会建一个用户角色关系映射表:user_role_mapping
字段有id,user_id,role_id
如果某个用户有多个角色,那么在user_role_mapping表中是有多条记录的
也有特殊的处理方式
在用户表user中新建一个字段role_ids记录
如果某个用户有多个角色,那么在role_ids记录的值是 1,2,多个角色用逗号分隔
添加新的角色
那我们批量给用户(某个组织下的用户)添加新的角色的时候该如何处理呢?
简单来说分为三种情况:
1.如果用户已经存在新增的角色,这个时候是不需要修改的,我们保持这一批用户权限不变
2.如果用户从未设置过角色(null),或者角色字段为空,我们直接给这一批用户添加权限:set role_ids = 新role_id
3.如果用户设置过多种角色(注意要排除包含新角色的情况),比如上图用户666666的角色有9和10,我们在给他添加角色11,最新的role_ids的值是9,10,11,我们需要 set role_ids = concat(role_id,'',' 新role_id')即可
综上所诉语句如下
UPDATE user set role_ids = 新role_id WHERE dept_id IN ('deptid1','deptid2') AND (role_ids = '' OR role_ids is null);
UPDATE user set role_ids = 新role_id WHERE dept_id IN ('deptid1','deptid2') AND LENGTH(role_ids ) > 0 AND LOCATE(新role_id,role_ids ) = 0;
解释 LOCATE(新role_id,role_ids ) = 0
返回字段 role_ids 中字符串 新role_id 第一次出现的位置,此处值为0,找不到,表示用户的多个角色并不包含新的角色
批量修改的时候需要执行以上两条语句。
删除角色
我们处理了添加新角色操作,接下来处理删除角色。看明白的同学,应该知道,我们操作的条件核心是定位字符串位置,操作字符串。
删除的情况分析如下:
1.如果用户包含一个角色,直接置空即可。
2.如果用户包含多个角色,且删除的角色出现在最后的位置,需要替换 ',role_id' 为空
3.如果用户包含多个角色,且删除的角色出现在中间的位置的位置,需要替换 ',role_id' 为空
4.如果用户包含多个角色,且删除的角色出现在第一位,需要替换 'role_id,' 为空
综上所诉:情况2和情况3,操作一致,我们可以当做一种情况处理
整理语句如下:
UPDATE user set role_ids = REPLACE(role_ids,'删除的role_id','') WHERE role_ids = '删除的role_id';
UPDATE ser set role_ids = REPLACE(role_ids,CONCAT(',','删除的role_id'),'') WHERE LOCATE(CONCAT(',','删除的role_id',','),role_ids) > 1
OR (LOCATE(CONCAT(',','删除的role_id),role_ids) = LENGTH(role_ids) - LENGTH('删除的role_id') AND LENGTH(role_ids) > LENGTH(CONCAT(',','删除的role_id)))
UPDATE user set role_ids = REPLACE(role_ids,CONCAT('删除的role_id',','),'') WHERE LOCATE(CONCAT('删除的role_id',','),role_ids) = 1
解释 LOCATE(CONCAT('删除的role_id',','),role_ids) = 1
CONCAT('删除的role_id',',') 字符串拼接 比如 CONCAT('9',',') 结果是字符串:9,
LOCATE('9,',role_ids) = 1 确保要删除角色是在角色字符串第一位
LOCATE(',9,',role_ids) > 1 确保要删除角色是在角色字符串中间的位置
LOCATE(',9',role_ids) = LENGTH(role_ids) - LENGTH('9') AND LENGTH(role_ids) > LENGTH(',9') 确保要删除角色是在角色字符串最后的位置