Chris Weldon bio photo

Chris Weldon

A savvy software engineer and agilist, Chris slings code in C#, but has also been known for commanding fleets of systems. He's currently a Tech Lead at Wolters Kluwer.

Email Twitter Facebook Github

Okay, I feel silly for writing this post, but as many of you know, this blog is not only for other’s enrichment, but for me document what I find for my own purposes and future uses. It also helps me to remember things I find cool, interesting, highly important, etc.

Well, for those of you who use databases (particularly MySQL) and don’t have as vast of a background as you’d like to have (like myself), you find yourself writing ridiculous queries to obtain the previous row of data you inserted into a table.

For example:

mysql> INSERT INTO users (name, email, phone) 
       VALUES
       ("Chris Weldon", "chris@chrisweldon.net", "232-353-4544");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT id FROM users 
       WHERE name = "Chris Weldon" 
       AND email = "chris@chrisweldon.net" 
       AND phone = "232-353-4544";
+------------------+
| id               |
+------------------+
|            12373 |
+------------------+
1 row in set (0.03 sec)

Well, with what I have finally found by purusing the MySQL Reference Manual - you no longer have to write that second assinine query which could potentially take a while to retreive the data, especially if the users table (or whatever table in question is being queried on) is large and could potentially have fields that are not indexed.

Here’s your nice solution:

mysql> INSERT INTO users (name, email, phone) 
       VALUES 
       ("Chris Weldon", "chris@chrisweldon.net", "232-353-4544");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|            12373 |
+------------------+
1 row in set (0.00 sec)

All I can say about this is “Where have you been all my life?”.