22

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

Improve this question

4 Answers 4

Reset to default
23

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();
Improve this answer
0
3

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.

Improve this answer
2

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.

Improve this answer
4
  • 1
    Integers break down if you need records to be unique across your database.
    – Cfreak
    May 13, 2015 at 15:18
  • 1
    I 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
    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 than INT but much better than VARCHAR(36)
    – miknik
    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
    Dec 14, 2018 at 1:31
2

I just decided to include the UUID() command on the insert from my application.

Thanks all.

Improve this answer

Your Answer

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

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