LAST_INSERT_ID without AUTO_INCREMENT

Question

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:

1. it is hard to generate a real UUID (which fulfils all criteria of RFC 4122) in PHP.
2. Also, I want to ensure (directly in the DBMS) that people get an unique UUID.
3. 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?

Answer 1

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.