PostgreSQL批量插入location_access表如何循环遍历员工ID避免硬编码

方案1：笛卡尔积关联插入 （优先推荐，无需循环，执行效率远高于循环方案） #

这种方案直接通过SQL的集合运算生成所有需要插入的数据，代码更简洁，维护成本更低。

适用场景1：员工ID已存储在员工表（如employees）中 #

INSERT INTO location_access (employee, location, permissionid)
SELECT 
  e.employee_id,
  l.location_id,
  4
FROM employees e
CROSS JOIN (
  -- 所有需要分配的地点ID列表
  SELECT 8241 AS location_id UNION ALL
  SELECT 8260 UNION ALL
  SELECT 8269 UNION ALL
  SELECT 8261 UNION ALL
  SELECT 8170 UNION ALL
  SELECT 7908 UNION ALL
  SELECT 8008 UNION ALL
  SELECT 8026 UNION ALL
  SELECT 8029 UNION ALL
  SELECT 8242 UNION ALL
  SELECT 8270 UNION ALL
  SELECT 8278 UNION ALL
  SELECT 8279 UNION ALL
  SELECT 8281 UNION ALL
  SELECT 8284 UNION ALL
  SELECT 8288 UNION ALL
  SELECT 8171 UNION ALL
  SELECT 7909 UNION ALL
  SELECT 8156 UNION ALL
  SELECT 7258 UNION ALL
  SELECT 8027
) l
-- 过滤需要分配权限的员工ID
WHERE e.employee_id IN (41743,41746,41753,43554,43557,43561,43563,43568,43569,43570,43572,43573,43574,43576,43577,43579,43582,43584)

适用场景2：只有员工ID列表，没有存储在表中 #

把员工ID也写到关联查询里即可：

INSERT INTO location_access (employee, location, permissionid)
SELECT 
  e.emp_id,
  l.location_id,
  4
FROM (
  -- 所有需要分配权限的员工ID列表
  SELECT 41743 AS emp_id UNION ALL
  SELECT 41746 UNION ALL
  SELECT 41753 UNION ALL
  SELECT 43554 UNION ALL
  SELECT 43557 UNION ALL
  SELECT 43561 UNION ALL
  SELECT 43563 UNION ALL
  SELECT 43568 UNION ALL
  SELECT 43569 UNION ALL
  SELECT 43570 UNION ALL
  SELECT 43572 UNION ALL
  SELECT 43573 UNION ALL
  SELECT 43574 UNION ALL
  SELECT 43576 UNION ALL
  SELECT 43577 UNION ALL
  SELECT 43579 UNION ALL
  SELECT 43582 UNION ALL
  SELECT 43584
  -- 剩余190+员工ID依次按上述格式补充即可
) e
CROSS JOIN (
  -- 所有需要分配的地点ID列表
  SELECT 8241 AS location_id UNION ALL
  SELECT 8260 UNION ALL
  SELECT 8269 UNION ALL
  SELECT 8261 UNION ALL
  SELECT 8170 UNION ALL
  SELECT 7908 UNION ALL
  SELECT 8008 UNION ALL
  SELECT 8026 UNION ALL
  SELECT 8029 UNION ALL
  SELECT 8242 UNION ALL
  SELECT 8270 UNION ALL
  SELECT 8278 UNION ALL
  SELECT 8279 UNION ALL
  SELECT 8281 UNION ALL
  SELECT 8284 UNION ALL
  SELECT 8288 UNION ALL
  SELECT 8171 UNION ALL
  SELECT 7909 UNION ALL
  SELECT 8156 UNION ALL
  SELECT 7258 UNION ALL
  SELECT 8027
) l

方案2：循环实现（MySQL示例） #

如果确实需要用循环遍历实现，可以参考以下存储过程写法：

-- 1. 创建临时表存储所有待处理的员工ID
CREATE TEMPORARY TABLE temp_emp_ids (emp_id INT);
-- 插入所有员工ID，批量插入语法更简洁
INSERT INTO temp_emp_ids VALUES 
(41743),(41746),(41753),(43554),(43557),(43561),(43563),
(43568),(43569),(43570),(43572),(43573),(43574),(43576),
(43577),(43579),(43582),(43584);
-- 剩余员工ID按上述格式补充即可

-- 2. 创建批量插入的存储过程
DELIMITER //
CREATE PROCEDURE batch_insert_location_permission()
BEGIN
  DECLARE is_done INT DEFAULT FALSE;
  DECLARE current_emp_id INT;
  -- 定义游标遍历员工ID
  DECLARE emp_cursor CURSOR FOR SELECT emp_id FROM temp_emp_ids;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_done = TRUE;

  OPEN emp_cursor;
  emp_loop: LOOP
    FETCH emp_cursor INTO current_emp_id;
    IF is_done THEN
      LEAVE emp_loop;
    END IF;
    -- 插入当前员工对应的所有地点权限
    INSERT INTO location_access (employee,location,permissionid)
    VALUES
    (current_emp_id,8241,4),
    (current_emp_id,8260,4),
    (current_emp_id,8269,4),
    (current_emp_id,8261,4),
    (current_emp_id,8170,4),
    (current_emp_id,7908,4),
    (current_emp_id,8008,4),
    (current_emp_id,8026,4),
    (current_emp_id,8029,4),
    (current_emp_id,8242,4),
    (current_emp_id,8270,4),
    (current_emp_id,8278,4),
    (current_emp_id,8279,4),
    (current_emp_id,8281,4),
    (current_emp_id,8284,4),
    (current_emp_id,8288,4),
    (current_emp_id,8171,4),
    (current_emp_id,7909,4),
    (current_emp_id,8156,4),
    (current_emp_id,7258,4),
    (current_emp_id,8027,4);
  END LOOP;
  CLOSE emp_cursor;
END //
DELIMITER ;

-- 3. 调用存储过程执行批量插入
CALL batch_insert_location_permission();

-- 4. 清理临时资源（可选）
DROP PROCEDURE IF EXISTS batch_insert_location_permission;
DROP TEMPORARY TABLE IF EXISTS temp_emp_ids;

注意事项 #

• 执行插入前建议开启事务，验证数据正确后再提交，避免误操作：

BEGIN TRANSACTION;
-- 执行插入语句后先查询校验结果
SELECT COUNT(*) FROM location_access WHERE employee IN (你的员工ID列表);
-- 确认数据无误再执行提交，否则回滚
COMMIT;
-- 数据错误则执行：ROLLBACK;

• 数据量较大时建议分批插入，避免长时间锁表影响业务。

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