MySQL/MariaDB中通过用户名角色名维护用户角色关联的SQL实现

数据库表结构 #

CREATE TABLE IF NOT EXISTS `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name` (`name` ASC) VISIBLE
);

CREATE TABLE IF NOT EXISTS `role` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name` (`name` ASC) VISIBLE
);

CREATE TABLE IF NOT EXISTS `userRoleMap` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  UNIQUE INDEX `user_role_map` (`user_id` ASC, `role_id` ASC) VISIBLE,
  CONSTRAINT `userRoleMap_userfk_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `user` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `userRoleMap_rolefk_1`
    FOREIGN KEY (`role_id`)
    REFERENCES `role` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);

1. 为用户添加角色（用户/角色不存在则触发错误） #

可以通过单条SQL实现，利用数据库约束自动触发错误：

INSERT INTO userRoleMap (user_id, role_id)
VALUES (
    (SELECT id FROM user WHERE name = '目标用户名'),
    (SELECT id FROM role WHERE name = '目标角色名')
);

逻辑说明： #

• 若用户或角色不存在，子查询返回NULL，而user_id/role_id是NOT NULL字段，会触发1048 - Column 'xxx' cannot be null错误；
• 若用户与角色的关联已存在，user_role_map唯一索引会触发1062 - Duplicate entry错误，避免重复添加；
• 所有校验由数据库原生约束完成，无需额外逻辑，执行效率高。

2. 为用户移除角色（仅用户不存在时触发错误） #

方案1：单条SQL（用户不存在时无报错，仅执行无操作） #

如果可以接受“用户不存在时不报错、仅无操作”，单条SQL即可完成需求：

DELETE urm
FROM userRoleMap urm
JOIN user u ON urm.user_id = u.id
LEFT JOIN role r ON urm.role_id = r.id
WHERE u.name = '目标用户名'
AND r.name = '目标角色名';

方案2：强制用户不存在时报错（两条SQL/存储过程） #

若必须在用户不存在时触发明确错误，单条纯SQL无法直接实现（删除操作本身不会主动检查用户存在性），可通过以下方式实现：

方式一：事务内执行两条SQL

-- 检查用户是否存在，不存在则返回空结果（可结合应用层判断抛出错误）
SELECT id FROM user WHERE name = '目标用户名' FOR UPDATE;
-- 删除角色关联，角色不存在则无操作
DELETE urm
FROM userRoleMap urm
WHERE urm.user_id = (SELECT id FROM user WHERE name = '目标用户名')
AND urm.role_id = (SELECT id FROM role WHERE name = '目标角色名');

方式二：存储过程封装（推荐，逻辑更清晰）

DELIMITER //
CREATE PROCEDURE remove_user_role(IN username VARCHAR(40), IN rolename VARCHAR(40))
BEGIN
    DECLARE target_user_id INT;
    -- 查询目标用户ID，不存在则返回NULL
    SELECT id INTO target_user_id FROM user WHERE name = username;
    -- 用户不存在时主动抛出错误
    IF target_user_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户不存在';
    END IF;
    -- 删除角色关联，角色不存在则无操作
    DELETE FROM userRoleMap
    WHERE user_id = target_user_id
    AND role_id = (SELECT id FROM role WHERE name = rolename);
END //
DELIMITER ;

逻辑说明： #

• 方案1中，LEFT JOIN role确保角色不存在时不会过滤用户记录，但由于r.name不匹配，最终删除0行，符合需求；
• 方案2通过主动查询用户ID并判断，确保用户不存在时触发明确错误，角色不存在时仅无操作，完全符合需求。

内容的提问来源于stack exchange，提问作者vivi