15

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

17

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 | |
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 | |
  • 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 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, privacy policy and cookie policy

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