lastinsertid mysql_可以選擇“PDO::lastInsertId”/“mysql_insert_id”

I always hear that using "lastInsertId" (or mysql_insert_id() if you're not using PDO) is evil. In case of triggers it obviously is, because it could return something that's totally not the last ID that your INSERT created.

我總是聽說使用“lastInsertId”(或mysql_insert_id()(如果不使用PDO))是不好的。對於觸發器，顯然是這樣的，因為它可能返回的東西完全不是插入創建的最后一個ID。

$DB->exec("INSERT INTO example (column1) VALUES ('test')");

// Usually returns your newly created ID.

// However when a TRIGGER inserts into another table with auto-increment:

// -> Returns newly created ID of trigger's INSERT

$id = $DB->lastInsertId();

What's the alternative?

另一種是什么?

6 个解决方案

#1

2

If you go the route of ADOdb (http://adodb.sourceforge.net/), then you can create the insert ID before hand and explicitly specific the ID when inserting. This can be implemented portably (ADOdb supports a ton of different databases...) and guarantees you're using the correct insert ID.

如果您選擇ADOdb (http://adodb.sourceforge.net/)，那么您可以在手動之前創建插入ID，並在插入時顯式地指定ID。這可以通過可移植性實現(ADOdb支持大量不同的數據庫…)，並保證您正在使用正確的插入ID。

The PostgreSQL SERIAL data type is similar except that it's per-table/per-sequence, you specify the table/sequence you want the last insert ID for when you request it.

PostgreSQL串行數據類型是類似的，除了它是每個表/每個序列，您指定在請求它時所需的最后插入ID的表/序列。

#2

4

IMHO it's only considered "evil" because hardly any other SQL database (if any) has it.

它只被認為是“邪惡的”，因為幾乎沒有任何其他SQL數據庫(如果有的話)擁有它。

Personally I find it incredibly useful, and wish that I didn't have to resort to other more complicated methods on other systems.

就我個人而言，我發現它非常有用，我希望我不必在其他系統上使用其他更復雜的方法。

#3

3

One alternative is to use sequences instead, so you generate the ID yourself before you do the insert.

另一種選擇是使用序列，所以在插入之前生成ID。

Unfortunately they are not supported in MySQL but libraries like Adodb can emulate them using another table. I think however, that the emulation itself will use lastInsertId() or equivalent... but at least you are less likely to have a trigger on a table which is purely used for a sequence

不幸的是，MySQL不支持它們，但是像Adodb這樣的庫可以使用另一個表來模擬它們。然而，我認為仿真本身將使用lastInsertId()或等效的……但至少您不太可能在表上有一個觸發器，而這個觸發器純粹用於序列

#4

1

I guess it's not really state of the art either but I use write locks to make sure that I really get the last inserted ID.

我想這也不是最先進的技術，但是我使用寫鎖來確保我真的得到了最后一個插入的ID。

#5

0

It's not sophisticated and it's not efficient, but if the data you've inserted include unique fields, then a SELECT can obviously yield what you're after.

它並不復雜，效率也不高，但是如果您插入的數據包含惟一字段，那么選擇顯然可以產生您想要的結果。

For example:

例如:

INSERT INTO example (column1) VALUES ('test');

SELECT id FROM example WHERE column1 = 'test';

#6

-3

You could try this:

你可以試試這個:

$sql = "SELECT id FROM files ORDER BY id DESC LIMIT 1";

$PS = $DB -> prepare($sql);

$PS -> execute();

$result = $PS -> fetch();