mysql - LAST_INSERT_ID( ) returning multiple rows of 0?

Question

Working in phpMyAdmin for now:

order table strucure:

OrderID     int(11)  auto_increment
CustomerID  varchar(50)
BillAddr    varchar(200)
ShipAddr    varchar(200)
Date            date
Total           double

The table currently has 4 rows of data, with different OrderIDs.

SQL:

SELECT LAST_INSERT_ID() FROM `order`

Result:

LAST_INSERT_ID()
0
0
0
0

I was expecting the fourth rows OrderID - just one number but got a 0 for each row in phpMyAdmin.

Answer 1

LAST_INSERT_ID() returns the id of the last inserted row and is not bound to any table. So if you create a new row:

INSERT INTO table VALUES('a', 'b', 'c');

It will return the last id (whatever value the new primary key has).

SELECT LAST_INSERT_ID();
=> 123 

For details, please take a look at the manual:

LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

If you just want to get last ID in a table, you can do it like this:

SELECT id FROM table ORDER BY id DESC LIMIT 1;