SELECT LAST_INSERT_ID() FROM LEGEND

I don't know why or who was the first who introduced this legend, but if you review source code from time to time, you see one weird MYSQL SQL statement showing up regularly: SELECT LAST_INSERT_ID() FROM some table.

If you google for »SELECT LAST_INSERT_ID() FROM« you'll get 98,100 hits. And if you use Google Code you actually find all the public available sources using this phrase: 216 times in some PHP code, 120 times in Perl code, 102 times in Java code, 21 times in Python code, and finally 1 time in Ruby code.

But what's so bad about this statement? Let's go into the lab:

mysql> SELECT \* FROM pages;
+----+---------+-----------------------------------------+
| id | name    | content                                 |
+----+---------+-----------------------------------------+
|  1 | welcome | Dear Traveler, welcome to my AMP world! | 
|  2 | about   | This is about AMP!                      | 
|  3 | team    | Apache, MySQL, and PHP.                 | 
+----+---------+-----------------------------------------+
3 rows in set (0.00 sec)

A simple table I used in an earlier blog entry. Now let's add some data:

mysql> INSERT INTO pages (name,content) VALUES('faq','Typical mistakes');
Query OK, 1 row affected (0.00 sec)
And now we want to know which id our new data set got:
mysql> SELECT LAST_INSERT_ID() FROM pages;
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 | 
|                4 | 
|                4 | 
|                4 | 
+------------------+
4 rows in set (0.00 sec)

4 times a »4«? Sure, because LAST_INSERT_ID() got calculated for every row in that table. And since we have 4 rows in that table, we got the LAST_INSERT_ID() for 4 times. In this case it's not a big problem, but in case of a really large table this can create some serious issues. Especially if you know that this whole list of last IDs is also transferred from the server to the client.

The statement is syntactically correct, but it's definitely not what you want.

To get what you want, simply omit the FROM and the table name:

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 | 
+------------------+
1 row in set (0.00 sec)

Good.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Kai 'Oswald' Seidler writes about his life as co-founder of Apache Friends, creator of XAMPP, and technology evangelist for web tier products at Sun Microsystems.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today