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