MySQL - Alter table to automatically put in a UUID

Question

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

Answer 1

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();

Answer 2

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.

Answer 3

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.

• Cfreak: Integers break down if you need records to be unique across your database.
• Avarkx: 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.
• miknik: 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)
• TamusJRoyce: 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.

Answer 4

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

Thanks all.