Thomas’ Developer Blog

March 14, 2008

LAST_INSERT_ID() and mySQL

Filed under: Asp.Net Framework 2.0, sql — Tags: , , , , , , — sanzon @ 8:45 am

Well some people think I’m anal about this stuff… but when I program I try to think of every single possible action that could occur and how it could affect the performance of my site.

One issue I have is Last_Insert_ID() in mySQL.  I’m not a big fan of this method, unless you create a procedure.  I don’t like SQL procedures either since it requires manually going into the database each time you want to make changes, which is a pain to do at times if your SQL server is on a different machine, or VPC.  Plus on top of that you have to worry about a lot of other small things.

 In other words, try to keep the programing as far away from the database as possible.  For me a database stores information, and should be only be used for the primary purpose.  It’s alright to have functions for encryption and so forth for security reasons, but over all I hate adding anything to a database since it is far less accessable.  Plus god forbid you have a client or company that has seperate departments for the database from programing.  You have to email someone in charge of the database explain why you’re making the change, and it’s just becomes a pain in the but. 

So getting back onto topic.  I try to avoid statements like, Last_Insert_ID() since it creates a “gap” in your code.  Meaning after the table is updated you then grab the last ID.  Well that’s fine on an average day, but you come to some sites where people are constantly updating that database it can become a HUGE mess.  What if the code messes up for some reason and there is a lag, then it calls the SQL function and grabs the wrong ID.  Not good! 

What I’m getting at, is try to use other methods.  I prefer myself to insert the values into the database and find a unique ID.  In a classic case of a username, grab the username!  Only that person has that name!  So you can’t mess up by doing a search for it and grabbing the primary key from there.  It’s really just about the same amount of coding as well and is 99.9999% fail proof.  Yes there is that .0001 chance something may blow up!  Otherwise you can’t mess it up.

Just to continue a quick bit, also remember to always check your radiobuttonlist for being checked!  Now a days it’s not hard for someone who almost no knowledge of javascript to use a add-on like firebug to change javascript values to change a selected true, to false.  Which leaves me to one last statment: NEVER VALIDATE IN JAVASCRIPT ALONE!!!

Advertisements

1 Comment »

  1. The MySQL “SELECT LAST_INSERT_ID()” is implemented on a per-connection basis, so the result is independent of any other client inserting/updating the table in question.
    If your code uses the db connection in a single thread, then you can retrieve the most recent auto-increment value before you issue any statements that might insert new records.

    Comment by MikeD — July 29, 2008 @ 10:34 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: