Auto increment is a terrible idea

Re:Auto increment is a terrible idea by tkyjonathan on Thursday November 30, 2017 @08:23AM (#15815479)

So, you would rather have your primary keys be a 36 character UUID string instead of a 4-8 byte integer?

This is for every single row in your database and will increase the size of your database substantially.

[ Parent | on HN ]

Re:Auto increment is a terrible idea by daigoba66 on Thursday November 30, 2017 @08:32AM (#15815538)

Don’t store UUIDs as strings.

Use the native UUID data type, which only uses 16 bytes (UUIDs are basically just 128 bit integers). That is still double the size of a 64 bit integer, but the trade off is that it can be random and universally unique.

[ Parent | on HN ]

Re:Auto increment is a terrible idea by 33degrees on Thursday November 30, 2017 @10:03AM (#15816227)

... for databases that have a native UUID type. MySQL doesn't, and the choices are either store them as strings or store them as non readable bytes. Not the greatest options.

[ Parent | on HN ]

Re:Auto increment is a terrible idea by dragonwriter on Thursday November 30, 2017 @06:30PM (#15820631)

You can store them as bytes and use a function to convert them to/from a display format (which is what DBs with a built in UUID type do under the covers.)

[ Parent | on HN ]

Re:Auto increment is a terrible idea by 33degrees on Wednesday December 06, 2017 @03:42PM (#15864783)

Yes, and I've done that in the past; it's still somewhat laborious though.

[ Parent | on HN ]

Re:Auto increment is a terrible idea by jasode on Thursday November 30, 2017 @08:31AM (#15815537)

>36 character UUID string

To clarify, UUID is stored in the db as 16 bytes (128 bits): https://en.wikipedia.org/wiki/Universally_unique_identifier#...

You would not store the 32 hex characters plus 4 hyphen characters as strings in the db. That UUID string format is for human readable display not disk storage.

(To downvoters, if 16 bytes is incorrect, please reply with what you believe to be the correct # of bytes for UUID.)

[ Parent | on HN ]

Re:Auto increment is a terrible idea by tkyjonathan on Thursday November 30, 2017 @01:42PM (#15818302)

on MySQL or any database that doesn't have it as a datatype, its 36 char on latin charset. If you leave everything as UTF-8, then its x3 that amount.

Try it for yourself.

[ Parent | on HN ]

Re:Auto increment is a terrible idea by jasode on Thursday November 30, 2017 @03:06PM (#15819018)

I didn't downvote your previous comment but I'm guessing it got downvotes because the blog post you're criticizing is about PostgreSQL not MySQL. PostgreSQL can store UUID in 16 bytes.

As a side comment about MySQL, there are various techniques to store UUID more compactly as 16 bytes inside of a "binary(16)" type like this:

https://stackoverflow.com/a/28252188

... or similar technique shown at the bottom of the Percona blog:

https://www.percona.com/blog/2014/12/19/store-uuid-optimized...

[ Parent | on HN ]