使用准备好的语句进行PostgreSQL内部查询

Question

使用准备好的语句进行PostgreSQL内部查询

phpsqlpostgresqldatabase-designauto-increment

3

我有一张表用来存储联系人信息。

我想要获取一列中添加用户ID为{某个数字}的最大值，并将其设置为当前插入记录的相同列值。

我正在使用预处理语句：

pg_prepare($db, "add", 'INSERT INTO '.CONTACTS.' (c_user_serial,c_name,c_company,c_email)  VALUES ($1, $2, $3, $4)');

$insert_co = pg_execute($db, "add", array({(MAX OF c_user_serial where c_user_id = 1234) + 1 increment },$name,$company,$email));

c_user_id 是添加此联系人的用户ID，还有另一列作为索引（id），它是一个常见的serial列，每一行都会递增，c_user_serial 是一个递增的序列号，每个用户都有一个。假设一个用户添加了一个联系人，那么它就是1。在其他用户添加了许多联系人之后，当这个用户添加第二个联系人时，我希望这一列存储2，因此这是一种自动递增的列，但应该按用户递增。

不确定如何在此处使用内部查询来获取该列的最大值，并使用递增的值进行当前插入。

- CodeRows

1

我试图写一个答案，但是我注意到这个：(MAX OF c_user_id where c_user_id = 1234)没有意义 - 如果你知道c_user_id的值是1234，那么最大值肯定是1234。所以使用MAX函数没有意义。问题中是否混淆了什么？ - Turophile

我试图修复你的问题，但是不清楚你想检索什么类型的数字，就像Turophile评论的那样。你还需要定义如果子查询找不到NULL或没有行时该怎么做。 - Erwin Brandstetter

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

这个查询会做你所要求的事情：

INSERT INTO contacts (c_user_serial, c_user_id, c_name, c_company, c_email)
SELECT max(c_user_serial) + 1, $1, $2, $3, $4
FROM   tbl
WHERE  c_user_id = $1;

您最初忘记插入所需的user_id本身。我已经添加了它。

然而，这会带来几个主要问题：

1. 当并发事务之间存在竞争条件时，当前的“最大值”可能是不可靠的，而且容易出现问题。（而使用serial可以安全地进行并发访问。）

2. 如果找不到c_user = $1的行，则什么也不会插入。这可能是您想要的，也可能不是。

3. 如果max(c_user_serial)返回NULL，则会为c_user_id插入另一个NULL值。如果c_user_id被定义为NOT NULL，则不能发生这种情况。

为避免问题2和3，并在每个新用户开始时从1开始，请执行以下操作：

INSERT INTO contacts (c_user_serial, c_user_id, c_name, c_company, c_email)
VALUES (COALESCE((SELECT max(c_user_serial) + 1 FROM tbl WHERE c_user_id = $1), 1)
       , $1, $2, $3, $4)

"

在这里，“no row”被转换为NULL，COALESCE在这种情况下默认为1。

简单解决方案

综合考虑，简单的解决方案是：

"

pg_prepare($db, "add"
     , 'INSERT INTO ' . CONTACTS . ' (c_user_serial, c_user_id, c_name, c_company, c_email)
        VALUES (COALESCE((SELECT max(c_user_serial) + 1 FROM tbl WHERE c_user_id = $1), 1)
               , $1, $2, $3, $4)');    
$insert_co = pg_execute($db, "add", array($user_id,$name,$company,$email));

请确保CONTACTS保存的是已经转义过的表名，否则你会面临SQL注入的风险！

如果你想要没有间隔的序列，你必须以某种方式处理UPDATE和DELETE，这将不可避免地使你的序列变得混乱，这也是为什么整个想法并不好的原因之一。

另一个更重要的原因是我提到的竞争条件。对于它，没有便宜而优雅的解决方案。（有解决方案，但是或多或少都很昂贵...）

如果可能的话，请坚持使用一个普通的serial列来存储所有行。在检索数据时，您可以始终从1开始为每个用户附加编号：

复合键的每组行的序列号