LAST_INSERT_ID without AUTO_INCREMENT I have following table, which does have an UUID as primary key, which also acts as foreign key.

CREATE TABLE `xxx` (
    `guid` char(36) NOT NULL,
    ....
    PRIMARY KEY (`guid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT ;

CREATE TRIGGER before_insert_xxx
  BEFORE INSERT ON xxx
  FOR EACH ROW
  SET new.guid = uuid();


Every time when I insert a new dataset, I need to know which UUID was generated, so I can use this UUID for references. Alas, LAST_INSERT_ID() does not work.

INSERT INTO `xxx`(...) VALUES (...);
SELECT LAST_INSERT_ID();


I also tried to modify the trigger like this:

CREATE TRIGGER before_insert_xxx
  BEFORE INSERT ON xxx
  FOR EACH ROW
  SET new.guid = LAST_INSERT_ID(uuid());


But it doesn't help either, and other people have the same problem.

A few solutions I can think of, but I don't like them very much ...

Solution A:

Generate the UUID by the application which writes the data. Not a good solution, because:


it is hard to generate a real UUID (which fulfils all criteria of RFC 4122) in PHP.
Also, I want to ensure (directly in the DBMS) that people get an unique UUID.
With the trigger, I cannot use a custom UUID in the INSERT statement


Solution B:


Add a INT primary key and let GUID be UNIQUE only.
After inserting, receive the INT primary key with LAST_INSERT_ID
Run a SELECT statement, which searches the GUID for the given INT primary key.


Since I want to insert a lot of datasets in short time, having a SELECT after each INSERT would make the progress much slower, since the amount of queries doubles.

Is there an acceptable solution C?
What are the 'requirements' of your UUID?  Why isn't an AUTO_INCREMENT sufficient?  I believe the use (in C) of mysql_insert_id() does not incur an extra round-trip to the server, but rather provides the value from the previous INSERT.  See http://dev.mysql.com/doc/refman/5.6/en/mysql-insert-id.html .

If that does not suffice, consider

INSERT ... VALUES (..., @uuid = UUID(), ...);
SELECT @uuid; -- or simply use @uuid in whatever needs it next.


A note about UUIDs -- They are very "random".  That is, for tables/indexes too large to be cached, they incur a lot of I/O.  More discussion in my uuid blog.