I want to store a v4 UUID and enforce integrity at the database level. I’m aware of the new UUID functions like BIN_TO_UUID, which look like they might help.
What’s the best way of doing this?
Use binary(16) with a check constraint that checks the 6 identifying bits for v4 UUIDs are set correctly.
binary(16) is the natural choice for storing a UUID given there is no native type.
BIN_TO_UUID and UUID_TO_BIN are useful for converting to and from a readable format, but none of the new functions (including IS_UUID) are much use for constraining the data. For that we can just use the bitwise operators that now work with binary:
In MySQL 8.0, bit functions and operators permit binary string type arguments (BINARY, VARBINARY, and the BLOB types) and return a value of like type
The following example assumes you also want to permit the Nil UUID, though in most cases you probably don’t:
<>https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=625fb9301f36028f2b3e3c96c5b7c54d
