SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A UUID() replacement for mysql_insert_id()

    Hi. I'm changing from using auto-incrementing integers as the primary key for some of my tables to using a UUID for the primary key. I recognize that there will be a performance trade-off for this - but I am designing the system to support offline mobile clients who will periodically re-synchronize. For those tables that the off-line clients will be generating new records for, I have decided to take the hit and use a UUID as the primary key.

    That said, I have code like the following:
    $query=("INSERT INTO my_table VALUES(UUID(), 'foo', 'bar', 'baz');");
    $result=mysql_query($query);

    I had previously then called mysql_insert_id() to get the primary key of the last record that I had inserted. However, this doesn't work when I've changed the primary key to a varchar(36) instead of some auto-incrementing integer.

    What is the best way to get the UUID for the last row I inserted?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Unless you have another way to determine what the last row inserted was, you can't do it the same way. Generate the UUID first, then insert, as separate actions. Either using UUID() or by generating it in your PHP code, which would save you a round trip to the DB.

  3. #3
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Can you not have a UUID AND an ID? They would both reference the same record, no?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  4. #4
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I just added a function that calls UUID() in mySql and returns it. Good enough for what I want.

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Just remember that mysql_insert_id() doesn't return last id added to the database, it returns the last one that was added by the current process and there may have been later ones added by other processes. You can't duplicate the processing using any sort of call to the database, the value being set has to be set in the process in the first place in order to know what it is after adding it if it isn't an autoincrement id.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •