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?

Improve this question
3
  • 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. Commented May 3, 2016 at 4:36
  • 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? stackoverflow.com/questions/32965743/… Commented Oct 2, 2018 at 17:09
  • By the way, know that the UUID spec was rewritten in 2024: RFC 9562. Invents two new versions for efficient indexing in databases: Version 6 and Version 7. Commented Oct 12 at 22:14

1 Answer 1

Reset to default
1

https://github.com/pluots/udf-suite

In this UDF suite there is a uuid_generate_v4 function enabling in MySQL generated UUIDv4.

CREATE OR REPLACE FUNCTION uuid_generate_v4 RETURNS string SONAME 'libudf_uuid.so';

And to return a UUID:

SELECT uuid_generate_v4();

For compressed storage of this in a BINARY(16), these function transform to/from the test form:

CREATE OR REPLACE FUNCTION uuid_to_bin RETURNS string SONAME 'libudf_uuid.so'; CREATE OR REPLACE FUNCTION uuid_from_bin RETURNS string SONAME 'libudf_uuid.so';
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.