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)
-
1unfortunately I don't think there is. Also, please discontinue using mysql_* functions and use either mysqli_* functions or PDO objects instead.Brian Driscoll– Brian Driscoll2012-02-21 14:33:40 +00:00Commented Feb 21, 2012 at 14:33
-
well, other than break support for legacy php installs... is there something in PDO for this?ina– ina2012-02-21 14:40:20 +00:00Commented Feb 21, 2012 at 14:40
-
how "legacy" are we talking? Mysqli is pretty well supported.Brian Driscoll– Brian Driscoll2012-02-21 17:18:21 +00:00Commented Feb 21, 2012 at 17:18
Add a comment
|
2 Answers
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.
3 Comments
ina
I had thought auto_increment fails when you shard to different servers, hence the usage of UUID's?
Brian Driscoll
@ina 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.
ina
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.
I found this quite short and simple solution:
set @id=UUID();
insert into <table>(<col1>,<col2>) values (@id,'another value');
select @id;
