准备语句:使用未命名和未编号的问号样式位置占位符

Question

例如，在Postgres以外的SQL系统中，例如MySQL，准备语句可以使用问号?作为预准备语句中数据的占位符。

INSERT INTO foo (id, name) VALUES (?, ?), (?, ?);

但是，在Postgres中，唯一可用的占位符似乎是编号占位符，因此上面用于Postgres的INSERT语句如下所示：

INSERT INTO foo (id, name) VALUES ($1, $2), ($3, $4);

当我试图在一条语句中插入大量行时，就会出现这种语法的问题，对于10,000行和10列，必须有从$1 to $100,000到$1 to $100,000的占位符，这导致查询大小增加了3-4倍。

Postgres是否支持准备语句的任何类型的未编号和未命名占位符？

编辑:

我使用准备好的语句，因为它们允许防御SQL注入。因此，我的目标是保持我的批量插入尽可能快，同时确保插入过程安全。

Answer 1

准备好的语句用于加速使用不同参数重复执行同一查询。如果您的目标是同时插入许多行，那么最好执行常规的insert查询，这将比准备的insert更快。但是，如果坚持使用此解决方案，则可以使用数组作为准备语句的参数。示例：

create table foo(id int, val text);

prepare insert_into_foo (int[], text[]) as 
    insert into foo 
    select unnest(a), unnest(b)
    from (values ($1, $2)) v(a, b);

execute insert_into_foo (array[1,2,3], array['a','b','c']);
execute insert_into_foo (array[4,5,6,7], array['d','e','f','g']);

deallocate insert_into_foo; 

这个技巧的主要缺点是，您需要非常小心地在数组中放置相同数量的参数。任何错误都可能是痛苦的。因此，我建议把它与保险丝一起使用：

prepare insert_into_foo (int[], text[]) as 
    insert into foo 
    select unnest(a), unnest(b)
    from (values ($1, $2)) v(a, b)
    where array_length(a, 1) = array_length(b, 1); -- fuse

execute insert_into_foo (array[1,2], array['a','b','c']); -- does nothing