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?
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.