mysql_insert_id替代postgresql

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....

对于PostgreSQL，是否有mysql_insert_id（）php函数的替代方案？大多数框架通过查找ID中使用的序列的当前值来部分解决问题。但是，有时主键不是串行列....

4 个解决方案

#1

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()

$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

$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。但是大多数db抽象库还没有使用第一种方法。

#2

Check out the RETURNING optional clause for 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（或您指定的任何表达式）。

#3

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.

这应始终提供唯一键，因为从数据库检索的密钥将永远不会再次检索。

#4

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及更高版本。