如何向Postgres关联表插入可变行数？哪种方案更优？

场景说明 #

前端请求体为如下JSON，其中tags是可变长度的数组：

// req.body 的 JSON 结构
{
    "title": "title",
    "reference": "1213",
    "noteType": "type_1",
    "tags": [3, 4], // 长度不固定
    "text": "Lorem ipsum dolor sit amet."
}

现有代码逻辑 #

当前代码先向notecards表插入数据并返回notecard_id，再用该ID向关联表tags_notecard插入对应行：

// 创建数据行并获取ID
const response = await db.query(`insert into notecards( 
        title, 
        reference, 
        note_type, 
        main_text
    ) values ( 
        $1, 
        $2, 
        $3, 
        $4 
    ) returning notecard_id;`, [
        title,
        reference,
        noteType,
        text
])
const notecard_id = Number(response.rows[0].notecard_id);

// 使用ID向关联表插入多行数据
await db.query(`insert into tags_notecard( 
        tag_id, 
        notecard_id
    ) values 
        ( $1, $2 )
        ( $3, $4 );`, 
    [
        tags[0], notecard_id,
        tags[1], notecard_id 
])  

由于tags长度可变，当前实现无法适配，因此提出两种方案：

方案一：循环执行单条插入语句 #

for (let i = 0; i < tags.length; i++) {
    await db.query(`insert into tags_notecard(tag_id, notecard_id) 
        values ( $1, $2 );`, [tags[i], notecard_id])
}

缺点：会发起多次数据库请求，IO开销大。

方案二：拼接批量插入的SQL语句与参数列表 #

let queryString = "insert into tags_notecard(tag_id, notecard_id) values"
let paramsList = []
for (let i = 0, j =1 ; i < tags.length; i++, j+=2) {
    if (i !== tags.length - 1) {
        queryString = queryString + "($" + (j) + ", $" + (j+1)+ "),";
    } else {
        queryString = queryString + "($" + (j) + ", $" + (j+1)+ ");";   
    }
    paramsList.push(tags[i]);
    paramsList.push(notecard_id);
}
await db.query(queryString, paramsList);

缺点：需要手动构建SQL字符串，容易出错，维护性差。

可行性分析与更优方案 #

两种方案的可行性 #

1. 方案一：在IO限制场景下可行但性能差。如果tags数量少（比如个位数），影响不大；但如果tags数量多（几十上百），多次IO请求会显著增加延迟，同时占用更多数据库连接资源，可能导致连接池耗尽。
2. 方案二：在IO限制场景下可行且性能优于方案一，但手动拼接SQL存在风险：比如参数索引计算错误、末尾逗号处理不当，而且如果后续表结构变更，需要同步修改拼接逻辑，容易引入bug。

更优替代方案 #

利用PostgreSQL的unnest函数结合批量插入，无需手动拼接SQL，同时只发起一次数据库请求：

// 使用unnest批量插入标签关联数据
await db.query(`
    insert into tags_notecard(tag_id, notecard_id)
    select unnest($1::int[]), $2
`, [tags, notecard_id]);

或者用VALUES结合数组展开的方式，同样简洁：

await db.query(`
    insert into tags_notecard(tag_id, notecard_id)
    values ${tags.map((_, idx) => `($${idx+1}, $${tags.length +1})`).join(',')}
`, [...tags, notecard_id]);

这种方案的优势：

• 只发起一次数据库请求，IO开销最小，适配IO限制场景
• 无需手动拼接复杂的参数索引，代码更简洁易维护
• 利用数据库原生语法，避免SQL拼接错误

另外，还可以将整个操作包裹在事务中，确保notecards插入和tags_notecard插入要么同时成功，要么同时失败，保证数据一致性：

const client = await db.connect();
try {
    await client.query('BEGIN');
    // 插入notecard并获取ID
    const response = await client.query(`insert into notecards( 
            title, 
            reference, 
            note_type, 
            main_text
        ) values ( 
            $1, $2, $3, $4 
        ) returning notecard_id;`, [title, reference, noteType, text]);
    const notecard_id = Number(response.rows[0].notecard_id);
    // 批量插入标签关联
    await client.query(`
        insert into tags_notecard(tag_id, notecard_id)
        select unnest($1::int[]), $2
    `, [tags, notecard_id]);
    await client.query('COMMIT');
} catch (err) {
    await client.query('ROLLBACK');
    throw err;
} finally {
    client.release();
}

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