Hacker News Reader

Comment by dozenal | Hacker News Reader

I've always seen UUIDs stored as strings. What's the suspect part? Favoring human readability over optimal machine storage utilization?

View on HN

Human readability is a concern of a client and is independent of the storage mechanism. Every modern database stores integers in binary format, for instance, but clients display them as a decimal string of characters as opposed to a binary or hexadecimal representation. Timestamps are similarly stored in binary fashion, but often formatted for human readability in the client.

If you're using an SQL database you're presumably doing so so that humans can run ad-hoc reports (otherwise there are better datastores). So the UX they get for that is important. And in mysql (yes, not the best choice these days, but a reasonable one when the decision was made), if you store UUIDs as binary (there's no native UUID type) then you do not provide a good UX.

MySQL itself should not be the main interface; there should be some kind of model layer on top of that which does the translation of things like that, such as (for example) ActiveRecord if you were on a Rails stack. That gets you the best of both worlds.

OR you could store the uuid twice, once "natively" and once as a computed column. Searches on the native field would be faster vs. an index on a string column.

> you're presumably doing so so that humans can run ad-hoc reports (otherwise there are better datastores)

oh dear, someone has drank the NoSQL punch... Storing data relationally is NOT something only suitable for ad-hoc queries by end-users! :O

UUID is not a universally available primitive type like integer or date.

Someone hasn't used Postgres. http://www.postgresql.org/docs/9.1/static/datatype-uuid.html

And anyway, all a UUID is, ultimately, is a big number. It's a simple transcoding to get it into base-10 integer format and back.

Reply on news.ycombinator.com