mysql - 如何插入或更新 FIND_IN_SET 中的所有值以及更新 NOT FIND_IN_SET On Duplicate Key 更新

Question

这是我正在尝试做的事情:

名为“部门”的表与名为“员工”的表具有多对多关系。因此，表department_employees 包含列:department_id、employee_id、is_active。

将department_id、employee_id编入索引作为pk。

我得到一串员工 ID，如“12,15,18,19”。我得到了部门 ID。

我调用一个 Mysql 存储过程(例程)，它应该:

• 将所有员工插入该部门
• 如果它们存在且未激活，则激活
• 所有其他员工都应该被停用

创建过程(@dep_id INT，@emp_ids TEXT)

插入 TBL_DEPARTMENT_EMPLOYEES(DEPARTMENT_ID、EMPLOYEE_ID、IS_ACTIVE)选择@dep_id，EMPLOYEE_ID，1来自 TBL_EMPLOYEESWHERE FIND_IN_SET(EMPLOYEE_ID, @emp_ids)重复 key 更新 IS_ACTIVE=1；

更新TBL_DEPARTMENT_EMPLOYEES设置 IS_ACTIVE=0WHERE DEPARTMENT_ID=@dep_id AND NOT FIND_IN_SET(EMPLOYEE_ID, @emp_ids);

只有第一个查询运行，我猜第二个查询由于锁定或其他原因而无法运行，我尝试了事务-提交技巧，但没有成功。我想也许这里的一位很棒的人可以帮助或让其在单个查询中运行。谢谢!

Answer 1

这个过程并不漂亮，但它是我能想到的最好的连接 ID。它还应该比 FIND_IN_SET 解决方案运行得更快，因为它可以使用 EMPLOYEE_ID 上的索引。

CREATE PROCEDURE `test`(IN `dep_id` INT, IN `emp_ids` TEXT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
START TRANSACTION;
IF(emp_ids REGEXP '^[0-9,]+$') THEN #this will NOT guarantee a valid query but will prevent injections
    UPDATE TBL_DEPARTMENT_EMPLOYEES SET IS_ACTIVE=0 WHERE DEPARTMENT_ID=dep_id;
    SET @q = CONCAT('
        INSERT INTO TBL_DEPARTMENT_EMPLOYEES (DEPARTMENT_ID, EMPLOYEE_ID, IS_ACTIVE) 
        SELECT ?, EMPLOYEE_ID, 1 
        FROM TBL_EMPLOYEES 
        WHERE EMPLOYEE_ID IN (',emp_ids,')
        ON DUPLICATE KEY UPDATE IS_ACTIVE=1;
    '); 
    PREPARE stmt1 FROM @q;
    SET @dep_id = dep_id;
    EXECUTE stmt1 USING @dep_id;
    DEALLOCATE PREPARE stmt1;
END IF;
COMMIT;
END ;