Clever Geek Handbook

What are the different options and their tradeoffs for storing UUIDs in a MYSQL table?

I am planning on using the customer provided UUID as the primary key on multiple tables in a MySQL database.

I have come across various mechanisms for storing UUIDs in a MySQL database, but nothing that compares them to each other. These include:

  • BINARY (16)
  • CHAR (16)
  • CHAR (36)
  • UAXNAQ (36)
  • 2 x BIGINT

Are there any better options for how the parameters compare to each other in terms of:

  • storage size?
  • overhead per request? (problems with indexes, joins, etc.).
  • ease of inserting and updating values ​​from client code? (usually Java via JPA)

Are there any differences depending on which version of MySQL is running, or the storage engine? We are currently working 5.1 and plan to use InnoDB. I would welcome any comments based on practical experience with UUIDs. Thank.

+2


source to share


2 answers


I used the UUID for smart client online storage and data sync, and for databases I knew would need to be merged at some point. I've always used char (36) or char (32) (no dash). You get a slight performance boost over varchar and almost all databases support char. I've never tried binary or large. One thing to be aware of is that char will fill in spaces unless you use 36 or 32 characters. Period, don't write a unit test that sets an object id to "test" and then tries to find it in the database.;)



+1


source


I would like to store it in a binary (16) column if you are really determined to use UUIDs at all. something like 2x bigint would be quite cumbersome to manage. Also, I've heard people reverse them because the start of the UUID on the same computer is usually the same at the beginning and different parts at the end, so if you reverse them your indexes will be more efficient ,



Of course, my instinct says that you should use automatic integers unless you have a really good reason to use UUIDs. One good reason is to create unique keys across different databases. Another option is that you plan on having more records than INT can store. Not many applications really need this kind of thing though. Not only is it a big loss in efficiency when integers are not used for your keys, but they are also more difficult to work with. they are too long to type, and spreading them in your urls makes the urls really long. So, go with a UUID if you need it, but try to stay away.

+3


source






More articles:


All Articles