A UUID() replacement for mysql_insert_id()

[ilovecm]

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?

[Dan Grossman]

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.

[AnthonySterling]

Can you not have a UUID AND an ID? They would both reference the same record, no?

[ilovecm]

Thanks. I just added a function that calls UUID() in mySql and returns it. Good enough for what I want.

[felgall]

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.