我想用一个INSERT查询插入多行,例如:
I would like to insert multiple rows with a single INSERT query, for example:
INSERT INTO tmp(col_a,col_b) VALUES('a1','b1'),('a2','b2')...有没有一种方法可以轻松地做到这一点,最好是针对这样的对象数组:
Is there a way to do this easily, preferably for an array of objects like these:
[{col_a:'a1',col_b:'b1'},{col_a:'a2',col_b:'b2'}]我最终可能在一个块中包含500条记录,因此不希望运行多个查询.
I might end up with 500 records in one chunk, so running multiple queries would be undesirable.
到目前为止,我只能对单个对象执行此操作:
So far I have been able to do it for a single object only:
INSERT INTO tmp(col_a,col_b) VALUES(${col_a},${col_b})作为附带的问题:使用${}表示法的插入是否可以防止SQL注入?
As a side question: Are insertions using ${} notation protected against SQL injections?
推荐答案我是 pg-promise .
在旧版本的库中, Performance Boost 文章,在编写高性能数据库应用程序时仍然是一本好书.
In older versions of the library this was covered by simplified examples within the Performance Boost article, which is still a good read when writing high-performance database applications.
更新的方法是依靠 helpers名称空间,最终具有灵活性,并针对性能进行了优化.
The newer approach is to rely on the helpers namespace, which is ultimately flexible, and optimised for performance.
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 ,插入.
这种插入甚至不需要事务,因为如果一组值插入失败,则不会插入任何内容.
Such an insert doesn't even require a transaction, because if one set of values fails to insert, none will insert.
您可以使用相同的方法来生成以下任何查询:
And you can use the same approach to generate any of the following queries:
- 单行INSERT
- 多行INSERT
- 单行UPDATE
- 多行UPDATE
- single-row INSERT
- multi-row INSERT
- single-row UPDATE
- multi-row UPDATE
使用$ {}表示法插入是否可以防止SQL注入?
Are insertions using ${} notation protected against sql injection?
是的,但不是一个人.如果要动态插入模式/表/列名称,则使用 SQL很重要名称,它们结合起来可以保护您的代码免遭SQL注入.
Yes, but not alone. If you are inserting schema/table/column names dynamically, it is important to use SQL Names, which in combination will protect your code from SQL injection.
相关问题: Node中的PostgreSQL多行更新. js
问:如何同时获取每个新记录的id?
Q: How to get id of each new record at the same time?
A:只需将RETURNING id附加到查询中,然后使用方法很多:
A: Simply by appending RETURNING id to your query, and executing it with method many:
const query = pgp.helpers.insert(values, cs) + 'RETURNING id'; const res = await db.many(query); //=> [{id: 1}, {id: 2}, ...]甚至更好,使用方法地图:
const res = await db.map(query, [], a => +a.id); //=> [1, 2, ...]要了解为什么在此使用+的原因,请参见: pg-promise返回整数作为字符串.
To understand why we used + there, see: pg-promise returns integers as strings.
UPDATE-1
有关插入大量记录的信息,请参见数据导入.
For inserting huge number of records, see Data Imports.
UPDATE-2
使用v8.2.1和更高版本,您可以将静态查询生成包装到一个函数中,以便可以在查询方法中生成该静态查询生成,以便在查询生成失败时拒绝:
Using v8.2.1 and later, you can wrap the static query-generation into a function, so it can be generated within the query method, to reject when the query generation fails:
// 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);更多推荐
具有pg
发布评论