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

score 23 · Answer 1 · edited Dec 24 '14 at 09:29

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

edited Dec 24 '14 at 09:29

Jack Douglas

39,869
15
101
176

answered Dec 23 '14 at 13:27

Robert Gabriel

403
4
10

score 3 · Answer 2 · answered Jan 22 '14 at 17:10

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.

score 2 · Answer 3 · edited May 23 '17 at 12:40

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.

edited May 23 '17 at 12:40

Community

1

answered Jan 22 '14 at 17:11

Avarkx

2,363
12
22

1

Integers break down if you need records to be unique across your database. – Cfreak May 13 '15 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 '15 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 '17 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 '18 at 01:31

score 2 · Answer 4 · answered Jan 23 '14 at 15:40

2

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

Thanks all.

answered Jan 23 '14 at 15:40

Matt Winer

395
1
3
7

MySQL - Alter table to automatically put in a UUID

4 Answers4

Linked