PHP mysql_insert_id() for MySQL UUID() primary keys?

Question

Is there some equivalent to PHP mysql_insert_id to fetch the last inserted UUID() primary key? (I always get 0. It works for auto_inc integers though)

Adria

unfortunately I don't think there is. Also, please discontinue using mysql_* functions and use either mysqli_* functions or PDO objects instead. – Smorgasbord

well, other than break support for legacy php installs... is there something in PDO for this? – Adria

how "legacy" are we talking? Mysqli is pretty well supported. – Smorgasbord

Answer 1

No, last_insert_id() only retrieves that last generated auto_increment fields. You'll have to do a select uuid() first, then do an insert using that uuid.

However, note that uuids can't be guaranteed to be unique - they're simply very unlikely to collide. If you do require uniqueness, then go with an auto_increment - they'll never be re-used within any single table.

Pietrek

I had thought auto_increment fails when you shard to different servers, hence the usage of UUID's? – Adria

@Adria yes, sharding (whether or not it is to different servers) can cause auto_increment failure, if you switch partitions. But, when you partition ("shard") a table your PK effectively becomes the auto_increment value plus whatever column you're partitioning on. – Smorgasbord

I'm still trying to understand why many systems use UUID for a user's primary id. I had thought it was because they assumed the database would scale across different servers, and the mac-address in the UUID helps determine which server the data is on. But, if an id is guaranteed to be unique and can scale, why use UUID's. – Adria

Answer 2

I found this quite short and simple solution:

set @id=UUID();
insert into <table>(<col1>,<col2>) values (@id,'another value');
select @id;

Vetiver