See " Generating v4 UUIDs in MySQL" for a way to generate potentially more desirable UUIDs. If everything went well, at the end you should have output similar to: +-+-+ VALUES (uuid_to_bin(uuid()), 'Larry Page') Here's an example where a table users uses binary UUIDs for its primary key: CREATE TABLE users It supports generating UUIDs in version 1, version 3, version 4, and version 5. This package comes with support for different UUID version. The following table lists the currently-defined versions for variant 2 UUID also known as RFC 4122/DCE 1. The version field bits are the most significant 4 bits of octet 6 of UUIDs. , bin_to_uuid( 0xF7C26694AAC6433391C972C4441430E2) AS bin_uuid +-+-+ The type of UUID is determine by the version field bits. MySQL v8.0.0 (2016) added the function UUID_TO_BIN() which does the same thing: SELECT uuid_to_bin("f7c26694-aac6-4333-91c9-72c4441430e2") AS uuid_bin +-+Īnd it's easy to perform the reverse here: SELECT lower( insert( insert( insert( insert( Even better, comparison and sorting will use the numeric value of the column, which will perform much better than a string.Īll versions of MySQL can use UNHEX() and REPLACE() to do this: SELECT UNHEX(REPLACE("f7c26694-aac6-4333-91c9-72c4441430e2", "-", "")) AS unhex_replace +-+ The BINARY type isn't affected by the table character set (such as utf8mb4), it uses the binary character set and collation. If we strip the hyphens and convert the remaining 32 characters to BINARY, we can store UUIDs in only 16 bytes. UUIDs are supposed to be only 16 bytes, can't we do better? Binary UUID columns The optimised approach is actually slower for lookups in a small table, but it outperforms the normal binary.
Using this approach, we can see following result.
We need to use some other indirect solutions: 1. There's one important thing to note: this time related bits are only available in UUID version 1. According to the rfc4122 specification, UUID is a 128 bits long identifier used to uniquely identify objects in various applications and systems.Many programming languages gives us ability to generate UUID using API, however Oracle dont provide a function which can do this directly. Universally unique identifiers (UUIDs) are 128-bit (16-byte) numbers that are designed to be globally unique, and as a result they make for great primary keys. And it seems that MySQL is especially fond of ordered data when creating indices. Why You Should Use UUIDs for Your Primary Keys