1

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?

4
  • 1
    option C: a stored procedure? Oct 16, 2015 at 19:28
  • Generate the UUID in your app code, it is standard enough. Autoincrement has to be generated by the DB, but that does not hold for UUID. If you have no library to generate it, then just run SELECT UUID() before, it will give you one. Last_insert_id() would net be known in before-insert trigger anyway afaik and there is no reason to use the trigger. The uniquenes of the UUID is very probable and the minimal chance of it being duplicate is guarded against by the primary key.
    – jkavalik
    Oct 16, 2015 at 21:04
  • I agree with the option C of @ypercube 's comment.
    – oNare
    Oct 18, 2015 at 16:02
  • @ypercube How would this stored procedure look like? I never worked with stored procedures before. Oct 18, 2015 at 19:36

1 Answer 1

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.

5
  • Is there any document about "mysql_insert_id() does not incur an extra round-trip to the server"? Does this implies that INSERT query result contains the AUTO_INCREMENT value?
    – petertc
    Dec 18, 2015 at 8:15
  • Don't know if there is a document. Yes, the last_insert_id comes back with the ACK for the INSERT. Are you reaching across a WAN? (Otherwise, it should not matter.)
    – Rick James
    Dec 18, 2015 at 17:15
  • I am not worry about security. I just cannot verify it by tcpdump or other network sniffer, i.e., I cannot see the AUTO_INCREMENT value in response.
    – petertc
    Dec 20, 2015 at 10:19
  • Would you care to share the tcpdump output?
    – Rick James
    Dec 20, 2015 at 22:20
  • Did the tcpdump show the SELECT LAST_INSERT_ID() go across the wire?
    – Rick James
    Dec 20, 2015 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.