Generate UUID v4 automatically in MySQL

Question

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?

• Justin Howard: 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.
• Jon Watson: 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/…

Answer 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';