1

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)

ina
  • 19,167
  • 39
  • 122
  • 201
  • 1
    unfortunately I don't think there is. Also, please discontinue using mysql_* functions and use either mysqli_* functions or PDO objects instead. – Brian Driscoll Feb 21 '12 at 14:33
  • well, other than break support for legacy php installs... is there something in PDO for this? – ina Feb 21 '12 at 14:40
  • how "legacy" are we talking? [Mysqli](http://us.php.net/manual/en/book.mysqli.php) is pretty well supported. – Brian Driscoll Feb 21 '12 at 17:18

2 Answers2

2

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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • 1
    I had thought auto_increment fails when you shard to different servers, hence the usage of UUID's? – ina Feb 21 '12 at 15:58
  • @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. – Brian Driscoll Feb 21 '12 at 17:06
  • 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. – ina Feb 24 '12 at 03:25
1

I found this quite short and simple solution:

set @id=UUID();
insert into <table>(<col1>,<col2>) values (@id,'another value');
select @id;
Fusca Software
  • 709
  • 6
  • 11