postgresql 的 mysql_insert_id 替代方案
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/55956/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql_insert_id alternative for postgresql
提问by Jeffrey04
is there an alternative for mysql_insert_id()php function for PostgreSQL? Most of the frameworks are solving the problem partially by finding the current value of the sequence used in the ID. However, there are times that the primary key is not a serial column....
mysql_insert_id()PostgreSQL 的 php 函数有替代方案吗？大多数框架通过查找 ID 中使用的序列的当前值来部分解决问题。但是，有时主键不是串行列....
回答by angch
From the PostgreSQL point of view, in pseudo-code:
从 PostgreSQL 的角度来看，在伪代码中：
* $insert_id = INSERT...RETURNING foo_id;-- only works for PostgreSQL >= 8.2.
* INSERT...; $insert_id = SELECT lastval(); -- works for PostgreSQL >= 8.1
* $insert_id = SELECT nextval('foo_seq'); INSERT INTO table (foo...) values ($insert_id...) for older PostgreSQL (and newer PostgreSQL)
pg_last_oid()only works where you have OIDs. OIDs have been off by default since PostgreSQL 8.1.
pg_last_oid()仅适用于您有 OID 的地方。自 PostgreSQL 8.1 以来，OID 已默认关闭。
So, depending on which PostgreSQL version you have, you should pick one of the above method. Ideally, of course, use a database abstraction library which abstracts away the above. Otherwise, in low level code, it looks like:
因此，根据您拥有的 PostgreSQL 版本，您应该选择上述方法之一。理想情况下，当然，使用抽象出上述内容的数据库抽象库。否则，在低级代码中，它看起来像：
Method one: INSERT... RETURNING
方法一：插入...返回
// yes, we're not using pg_insert()
$result = pg_query($db, "INSERT INTO foo (bar) VALUES (123) RETURNING foo_id");
$insert_row = pg_fetch_row($result);
$insert_id = $insert_row[0];
Method two: INSERT; lastval()
方法二：INSERT；最后一个（）
$result = pg_execute($db, "INSERT INTO foo (bar) values (123);");
$insert_query = pg_query("SELECT lastval();");
$insert_row = pg_fetch_row($insert_query);
$insert_id = $insert_row[0];
Method three: nextval(); INSERT
方法三：nextval(); 插入
$insert_query = pg_query($db, "SELECT nextval('foo_seq');");
$insert_row = pg_fetch_row($insert_query);
$insert_id = $insert_row[0];
$result = pg_execute($db, "INSERT INTO foo (foo_id, bar) VALUES ($insert_id, 123);");
The safest bet would be the third method, but it's unwieldy. The cleanest is the first, but you'd need to run a recent PostgreSQL. Most db abstraction libraries don't yet use the first method though.
最安全的选择是第三种方法，但它很笨拙。最干净的是第一个，但您需要运行最新的 PostgreSQL。不过，大多数数据库抽象库还没有使用第一种方法。
回答by aib
Check out the RETURNING optional clausefor an INSERT statement. (Link to official PostgreSQL documentation)
查看INSERT 语句的RETURNING 可选子句。（链接到官方 PostgreSQL 文档）
But basically, you do:
但基本上，你这样做：
INSERT INTO table (col1, col2) VALUES (1, 2) RETURNING pkey_col
and the INSERT statement itself returns the id (or whatever expression you specify) of the affected row.
并且 INSERT 语句本身返回受影响行的 id（或您指定的任何表达式）。
回答by Vertigo
From php.net:
来自 php.net：
$res=pg_query("SELECT nextval('foo_key_seq') as key");
$row=pg_fetch_array($res, 0);
$key=$row['key'];
// now we have the serial value in $key, let's do the insert
pg_query("INSERT INTO foo (key, foo) VALUES ($key, 'blah blah')");
This should always provide unique key, because key retrieved from database will be never retrieved again.
这应该始终提供唯一的密钥，因为从数据库中检索的密钥将永远不会再次检索。
回答by Jenaro
You also can use:
您还可以使用：
$result = pg_query($db, "INSERT INTO foo (bar) VALUES (123) RETURNING foo_id");
$insert_row = pg_fetch_result($result, 0, 'foo_id');
You have to specify in pg_fetch_result the number of the row and the name of the field that you are looking for, this is a more precise way to get the data that you need, but I don't know if this has some penalty in the performance of the query. Remember that this method is for PostgreSQL versions 8.2 and up.
您必须在 pg_fetch_result 中指定要查找的行数和字段名称，这是获取所需数据的更精确的方法，但我不知道这是否有一些惩罚查询的性能。请记住，此方法适用于 PostgreSQL 8.2 及更高版本。
