Jack Douglas
I want to store a v4 UUID and enforce integrity at the database level. I'm aware of the [new UUID functions](https://dev.mysql.com/blog-archive/mysql-8-0-uuid-support/) like [`BIN_TO_UUID`](https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_bin-to-uuid), which look like they might help.
What's the best way of doing this?
Top Answer
Jack Douglas
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](https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_is-uuid)) are much use for constraining the data. For that we can just use the bitwise operators that [now work](https://dev.mysql.com/doc/refman/8.0/en/bit-functions.html) 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](https://en.wikipedia.org/wiki/Universally_unique_identifier#Nil_UUID), though in most cases you probably don't:
<>https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=625fb9301f36028f2b3e3c96c5b7c54d