8

Is it possible to generate a UUID version 4 (completely random) in MySQL? I would like to use it as a primary key.

Use Case

  • Many systems will insert to the table, some may specify a UUID when inserting, but some will not. They will generate their own UUID, then insert it later (offline insert).
  • I already have systems that use MySQL, and I would prefer to stay with the same DBMS.
  • I prefer UUID v4 because of its physical machine independence.

Alternatives

I could simply create a trigger that calls the uuid function, as outlined in MySQL - Alter table to automatically put in a UUID, but the uuid function generates UUID v1 ids.

I could always generate the UUID externally, but I would prefer a built-in default.

This is easy to do in Postgres, and some other DBMSes, but is there a way to do it in MySQL?

Community
  • 1
Justin Howard
  • 181
  • 1
  • 1
  • 6
  • Why is one version of UUID preferred over another? Each has a very high probability (but not 100%) of being world-wide unique. – Rick James May 03 '16 at 01:48
  • And there is zero overlap between type 1 and type 4 UUIDs because of the 4-bit version number being different (1 vs 4). – Rick James May 03 '16 at 01:53
  • I didn't know that about the version number. UUID v1 might be a good option then. I suppose it's a minor quibble to complain about patterns in the IDs. – Justin Howard May 03 '16 at 04:36
  • @RickJames v1 is predictable. If you generate two in quick succession they are almost identical and easily guessed. v4 UUIDs are impossible to guess. This can have serious security implications (see various TCP/IP vulnerabilities). – Abhi Beckert Sep 18 '16 at 07:03
  • 1
    Only v1 is available off-the-shelf in MySQL. – Rick James Sep 18 '16 at 14:14
  • 1
    I was researching a similar question, and found a different post that might be helpful. The answer appears to have a custom MySQL function that will generate a v4 UUID. Perhaps you could integrate it in with your trigger? https://stackoverflow.com/questions/32965743/how-to-generate-a-uuidv4-in-mysql – Jon Watson Oct 02 '18 at 17:09
  • Worth noting that UUIDv1 has 48 bits based on a MAC address so when many systems are generating these, they don't conflict either. – danblack Nov 06 '21 at 22:08

0 Answers0