MySQL UUID storage/presentation

Question

I'm looking at the way UUIDs are stored in MySQL. MySQL 8 still doesn't have a UUID data type, and it still doesn't have custom types, so there are two options - store it as a string or as a binary.

Binary form saves 30% of space and performs much better, in my experience.

Unfortunately binary form is way more problematic for development/maintenance/support, since it's not readable/copyable/pasteable. To the point that it makes binary form unacceptable.

MySQL 8 has new functions that are supposed to make life with binary UUIDs a little easier, but I don't see how someone having to deal with DB on daily basis is going to happily type BIN_TO_UUID(some_id) to display the actual ID and UUID_TO_BIN('...') every time s/he wants to see/update some records.

I'm trying to figure out what would be the acceptable (not painful) way to deal with binary UUIDs in every day life? One way I could think of was to create an alias function, like U(some_id), but MySQL still doesn't have global functions.

Are there any other options? Like setting up some kind of transparent filter in-between DB and client, of within a client, or something like that?

Who is the one doing the BIN_TO_UUID(some_id) that is objecting? U(some_id) looks about the same to me. — johnny, Jul 14 '17 at 16:18
@johnny just about anyone who has to query the DB, think hypothetical support person trying to investigate data inconsistency. And having to type U instead of BIN_TO_UUID 50x a day does make a lot of difference — Oleg Mikheev, Jul 14 '17 at 18:04
I guess I'm old school. You would have to alias it like you said. — johnny, Jul 14 '17 at 18:47
Sounds like you have a pretty clear understanding of the issues already. — Robert Harvey, Jul 14 '17 at 20:28

Robert Harvey · Accepted Answer · 2017-07-15T21:42:30.673

As you have correctly observed, MySQL appears to lack adequate display support for UUID's like other database systems have.

Many folks seem to favor storing UUID's as a 36 character TEXT field. As you have correctly surmised, there are significant performance and storage implications with this approach, described in detail at this blog post.

My preference? Create a BIGINT column for your primary key, and generate keys for it using the UUID_SHORT() function. This function, under the right conditions, guarantees unique values, unlike an ordinary generated UUID, which only assumes that the value will be unique with a very high degree of probability. BIGINTs are easily indexed, and take up half the space of a character-based UUID.

This approach gives you the best of all possible worlds; BIGINT numeric keys, at 20 digits long, are probably more readable than the 36 characters of a hex UUID anyway. If you still feel like displaying them as a UUID, you can wrap them in a HEX function (albeit without displaying the dashes). In practice, most people don't read the entire UUID anyway; they sight the first few characters for matching purposes, and verify the entire value when they see a match.

I've worked in companies that deal heavily in UUID's. The truth is, if your system is architected properly, you should seldom have to work with these numbers directly unless you're doing some sort of diagnostic work.

MySQL UUID storage/presentation

1 Answers1