如何高效实现Sybase批量插入自增ID并关联更新表数据？

要解决6000条记录的插入更新效率问题，核心是砍掉单条循环的频繁数据库交互，直接用数据库原生的批量逻辑或者Python批量操作，以下分不同场景给出具体方案：

1. PostgreSQL 直接用SQL脚本搞定（最快） #

PostgreSQL的RETURNING子句可以直接捕获插入生成的自增ID，结合CTE一次性完成插入和更新：

-- 假设表A除了id=0外，有col1、col2等字段，且这些字段组合能唯一标识每条记录（有单独唯一主键更稳妥）
WITH inserted_records AS (
    INSERT INTO table_b (col1, col2, ...)  -- 填写表B需要的字段，对应表A的字段
    SELECT col1, col2, ... FROM table_a WHERE id = 0
    RETURNING id AS new_b_id, col1, col2  -- 返回表B生成的自增ID，以及匹配表A的字段
)
UPDATE table_a
SET id = inserted_records.new_b_id
FROM inserted_records
WHERE table_a.id = 0
AND table_a.col1 = inserted_records.col1
AND table_a.col2 = inserted_records.col2;  -- 用唯一字段匹配，确保每条记录对应正确

如果表A没有能唯一匹配的字段，可以先加个临时唯一列辅助：

-- 新增临时UUID列
ALTER TABLE table_a ADD COLUMN temp_unique_uuid UUID DEFAULT gen_random_uuid();
-- 执行上面的CTE插入更新，把temp_unique_uuid加入SELECT和匹配条件
ALTER TABLE table_a DROP COLUMN temp_unique_uuid;  -- 完成后删掉临时列

2. SQL Server 用OUTPUT子句捕获自增ID #

SQL Server可以通过临时表存储插入后的自增ID，再关联更新表A：

-- 定义临时表存储插入的新ID和匹配字段
DECLARE @InsertedIds TABLE (new_b_id INT, col1 VARCHAR(100), col2 INT);

INSERT INTO table_b (col1, col2, ...)
OUTPUT inserted.id, inserted.col1, inserted.col2 INTO @InsertedIds
SELECT col1, col2, ... FROM table_a WHERE id = 0;

-- 关联更新表A
UPDATE a
SET a.id = i.new_b_id
FROM table_a a
JOIN @InsertedIds i ON a.col1 = i.col1 AND a.col2 = i.col2 AND a.id = 0;

3. MySQL 8.0+ 用临时表关联更新 #

MySQL 8.0支持CTE，或者用临时表配合自增特性完成：

-- 创建临时表存储表A中id=0的记录，加个自增temp_id用于后续计算新ID
CREATE TEMPORARY TABLE temp_a_records (
    temp_id INT AUTO_INCREMENT PRIMARY KEY,
    col1 VARCHAR(100),
    col2 INT,
    ...  -- 表A的其他字段
);

-- 批量插入表A的目标记录到临时表
INSERT INTO temp_a_records (col1, col2, ...)
SELECT col1, col2, ... FROM table_a WHERE id = 0;

-- 批量插入到表B
INSERT INTO table_b (col1, col2, ...)
SELECT col1, col2, ... FROM temp_a_records ORDER BY temp_id;

-- 利用1484406和temp_id计算每条记录对应的新ID，关联更新表A
UPDATE table_a a
JOIN temp_a_records t ON a.col1 = t.col1 AND a.col2 = t.col2 AND a.id = 0
SET a.id = (SELECT 1484406) + t.temp_id - 1;

-- 清理临时表
DROP TEMPORARY TABLE temp_a_records;

4. Python 层面优化（如果必须用Python） #

如果不想直接写SQL脚本，Python里要避免单条循环，改用批量查询、批量插入、批量更新：

# 以PostgreSQL为例，其他数据库替换对应的驱动和语法
import psycopg2
from psycopg2.extras import execute_values

# 建立连接
conn = psycopg2.connect("dbname=你的库名 user=用户名 password=密码")
cur = conn.cursor()

# 1. 批量查询表A中id=0的所有记录
cur.execute("SELECT col1, col2, ... FROM table_a WHERE id = 0")
source_records = cur.fetchall()

# 2. 批量插入到表B，同时获取生成的自增ID
cur.executemany(
    "INSERT INTO table_b (col1, col2) VALUES (%s, %s) RETURNING id",
    [(r[0], r[1]) for r in source_records]
)
new_ids = [row[0] for row in cur.fetchall()]

# 3. 批量更新表A，把新ID对应回去
update_sql = """
UPDATE table_a
SET id = new_data.new_id
FROM (VALUES %s) AS new_data (col1, col2, new_id)
WHERE table_a.id = 0 AND table_a.col1 = new_data.col1 AND table_a.col2 = new_data.col2
"""
# 构造参数：把原记录字段和新ID一一对应
update_params = [(source_records[i][0], source_records[i][1], new_ids[i]) for i in range(len(source_records))]
execute_values(cur, update_sql, update_params)

# 提交并关闭连接
conn.commit()
cur.close()
conn.close()

核心是把6000次数据库交互压缩成3次，效率会提升几十倍甚至上百倍。

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