Ok, I know I've done this before. But I cannot for the life of me figure it out. I created a table. One of the columns is labled "LogID", it is also the primary key.
How do I alter it so that this column generates a UUID for each new entry?
Thanks
Add a comment |
4 Answers
Just create a trigger to run before insert to generate the UUID for the given column.
CREATE TRIGGER before_insert_mytable BEFORE INSERT ON mytable FOR EACH ROW SET new.LogID = uuid(); 
The UUID() expression generates a UUID when called.
Unfortunately (AFAIK anyway) MySQL won't allow expressions as a default value for a field. As a work around, you could always set the field to default null and have a trigger that updates the field with a UUID on insert.
I'm pretty sure you still can't, actually. I would seriously consider not using a UUID as a primary key, instead using a slimmer, nicer data type like INT. You can add your UUID as a separate column and update it via a TRIGGER, if that suits you.
- 1Integers break down if you need records to be unique across your database.Cfreak– Cfreak2015-05-13 15:18:12 +00:00Commented May 13, 2015 at 15:18
- 1I would suspect there is a larger model design or chosen tool issue if requirements are dictating the engine storage pattern at that level. Having the UUID as a separate column is actually a best of both worlds solution in this case.Avarkx– Avarkx2015-05-14 13:01:24 +00:00Commented May 14, 2015 at 13:01
- UUID gets bloated once formatted for humans. If you want to use a UUID as a primary key then strip the dashes and unhex it. If you need the human formatted version you could add a generated column to the table.
SELECT LENGTH(UNHEX(REPLACE(UUID(),'-','')))= 16 bytes binary. Still bigger thanINTbut much better thanVARCHAR(36)miknik– miknik2017-12-29 19:13:57 +00:00Commented Dec 29, 2017 at 19:13 - Bad advice, miknik. Your database will scale terribly if you use a UUID as a primary key. Might as well have 16 separate integer columns as a compound primary key. And index fragmentation galore! Your primary key is now random. If you do, don't use foreign keys. Joins change from O(n) to O(n^2). Two joins from O(n log10(n)) to O(n^3). Replace n with 100,000 and subtract the difference.TamusJRoyce– TamusJRoyce2018-12-14 01:31:00 +00:00Commented Dec 14, 2018 at 1:31
I just decided to include the UUID() command on the insert from my application.
Thanks all.