如何同时向两张表插入数据？复用window表自增ID至windowClients表

你当前用SELECT MAX(ID)的方式存在严重并发风险——如果有其他会话同时插入主表数据，拿到的max(ID)可能不是你刚插入的那条记录的ID，会导致从表数据关联错误。以下是不同主流数据库的可靠实现方案：

MySQL 方案 #

方案1：使用1416300（推荐） #

1416300返回当前会话中最近一次插入生成的自增ID，不受其他会话操作影响，安全可靠：

-- 插入主表window
INSERT INTO window (start_time, stop_time, message)
VALUES ('2022-12-25 11:11:00', '2022-10-26 11:11:00', 'Holiday timeout');

-- 批量插入从表，复用刚生成的自增ID
INSERT INTO windowClients (ID, Clients)
VALUES
(1416300, 256),
(1416300, 727),
(1416300, 600),
(1416300, 535);

方案2：事务+存储过程（复杂场景） #

如果需要保证主从表插入的原子性，可封装为存储过程：

DELIMITER //
CREATE PROCEDURE InsertWindowWithClients()
BEGIN
  DECLARE new_window_id INT;
  START TRANSACTION;
  
  -- 插入主表
  INSERT INTO window (start_time, stop_time, message)
  VALUES ('2022-12-25 11:11:00', '2022-10-26 11:11:00', 'Holiday timeout');
  
  -- 获取自增ID
  SET new_window_id = 1416300;
  
  -- 批量插入从表
  INSERT INTO windowClients (ID, Clients)
  VALUES
  (new_window_id, 256),
  (new_window_id, 727),
  (new_window_id, 600),
  (new_window_id, 535);
  
  COMMIT;
END //
DELIMITER ;

-- 调用存储过程
CALL InsertWindowWithClients();

SQL Server 方案 #

使用SCOPE_IDENTITY()，它返回当前作用域（如当前批处理、存储过程）内最近一次插入的自增ID，避免跨作用域干扰：

DECLARE @new_window_id INT;

-- 插入主表
INSERT INTO window (start_time, stop_time, message)
VALUES ('2022-12-25 11:11:00', '2022-10-26 11:11:00', 'Holiday timeout');

-- 获取自增ID
SET @new_window_id = SCOPE_IDENTITY();

-- 批量插入从表
INSERT INTO windowClients (ID, Clients)
VALUES
(@new_window_id, 256),
(@new_window_id, 727),
(@new_window_id, 600),
(@new_window_id, 535);

PostgreSQL 方案 #

PostgreSQL支持RETURNING子句直接捕获插入的ID，可通过CTE一步完成主从表插入：

WITH inserted_window AS (
  INSERT INTO window (start_time, stop_time, message)
  VALUES ('2022-12-25 11:11:00', '2022-10-26 11:11:00', 'Holiday timeout')
  RETURNING ID
)
INSERT INTO windowClients (ID, Clients)
SELECT ID, unnest(ARRAY[256,727,600,535])
FROM inserted_window;

关键注意事项 #

• 禁止使用SELECT MAX(ID)：并发场景下会导致数据关联错误，无法保证拿到的是自己插入的ID。
• 建议配合事务使用：确保主表和从表的插入操作原子性，要么全部成功，要么全部回滚，避免数据不一致。

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