我想用一个INSERT查询插入多个行,例如:
INSERT
INSERT INTO tmp(col_a,col_b) VALUES('a1','b1'),('a2','b2')...
有没有一种方法可以轻松地做到这一点,最好是针对这样的对象数组:
[{col_a:'a1',col_b:'b1'},{col_a:'a2',col_b:'b2'}]
我可能最终将500条记录放在一个块中,因此运行多个查询将是不可取的。
到目前为止,我只能对单个对象执行此操作:
INSERT INTO tmp(col_a,col_b) VALUES(${col_a},${col_b})
附带的问题:使用${}符号插入是否可以防止SQL注入?
${}
我是pg-promise的作者。
在旧版本的库中,Performance Boost文章中的简化示例涵盖了这一点,在编写高性能数据库应用程序时,它仍然是一本好书。
更新的方法是依靠helpers名称空间,该名称空间最终是灵活的,并且针对性能进行了优化。
const pgp = require('pg-promise')({ /* initialization options */ capSQL: true // capitalize all generated SQL }); const db = pgp(/*connection*/); // our set of columns, to be created only once (statically), and then reused, // to let it cache up its formatting templates for high performance: const cs = new pgp.helpers.ColumnSet(['col_a', 'col_b'], {table: 'tmp'}); // data input values: const values = [{col_a: 'a1', col_b: 'b1'}, {col_a: 'a2', col_b: 'b2'}]; // generating a multi-row insert query: const query = pgp.helpers.insert(values, cs); //=> INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2') // executing the query: await db.none(query);
参见API:ColumnSet,插入。
这样的插入甚至不需要事务,因为如果一组值插入失败,则不会插入任何值。
您可以使用相同的方法来生成以下任何查询:
UPDATE
使用$ {}表示法插入是否可以防止SQL注入?
是的,但并不孤单。如果要动态插入模式/表/列名称,则使用SQL名称很重要,这将保护您的代码免于SQL注入。
问:如何同时获取id每个新记录?
id
答: 只需将其追加RETURNING id到查询中,然后使用方法many执行即可:
RETURNING id
const query = pgp.helpers.insert(values, cs) + 'RETURNING id'; const res = await db.many(query); //=> [{id: 1}, {id: 2}, ...]
甚至更好的方法是,获取id-s,然后使用map方法将结果转换为整数数组:
const res = await db.map(query, [], a => +a.id); //=> [1, 2, ...]
更新1
有关插入大量记录的信息,请参见数据导入。
更新2
使用v8.2.1和更高版本,您可以将静态查询生成包装到一个函数中,以便可以在查询方法中生成静态查询生成,以在查询生成失败时拒绝:
// generating a multi-row insert query inside a function: const query = () => pgp.helpers.insert(values, cs); //=> INSERT INTO "tmp"("col_a","col_b") VALUES('a1','b1'),('a2','b2') // executing the query as a function that generates the query: await db.none(query);