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.
UUIDs are 128-bit numbers, but they're usually represented as 32 hexadecimal digits displayed in 5 groups separated by hyphens in the form
Here's some example v1 UUIDs:
You can tell they're v1 from the leading "1" in the
Most common relational databases (ignoring SQLite) have support for UUIDs:
- v4.1.2 (2004) introduced
UUID()to generate v1 UUID strings (see "Generating v4 UUIDs in MySQL" for v4 UUIDs)
- v5.1.20 (2007) introduced
UUID_SHORT()to generate half-sized v1 UUID integers
- v8.0.0 (2016) introduced
Nov 13, 2020 · 3 min read
MySQL's UUID() function generates v1 UUIDs, which have a time component that make them unevenly distributed over short periods of time. We can define our own function to generate v4 UUIDs, which are random and therefore more evenly distributed.
The earliest version of MariaDB is v5.1.38 (2010) which is based on MySQL v5.1.38 which supported
- v8.3.0 (2008) added support for the
- v8.3.0 (2008) introduced the
uuid-osspmodule and its functions such as
v10.1 (2003) introduced
SYS_GUID() which is close enough
Reasons to use UUIDs for primary keys
UUIDs are unique across tables and databases.
There's a lot of value in having an identifier refer to only one entity. Having an auto-incrementing ID such as "1234" isn't meaningful on its own, especially in a database with dozens of tables.
UUIDs can be generated before insert time.
When inserting multiple related entities to database at the same time it's easier to generate IDs ahead of time instead of needing to: start a transaction, insert the parent entity, return the last inserted row ID, then insert all the other entities.
UUIDs are more portable.
Auto-incrementing IDs are only unique to the database and table that they came from. If that data ever needed to be migrated somewhere else, or combined with other data, you're likely to run into collision issues.
Auto-incrementing IDs expose internal information.
If you're designing a database for something like e-commerce, using an auto-incrementing ID could accidentally expose things like: how many orders exist in the system, at what rate orders are made, and so on. The same thinking could be applied to user accounts which is even more dangerous. And if that database is supporting a RESTful web service you are likely to end up with endpoints like
/users/:idthat someone might be able to brute force.
"But primary keys shouldn't be exposed to the front-end!" But you need some kind of ID for the front-end to interact with, so what are you going to use?
Auto-incrementing IDs are difficult in a multi-master system.
First, please don't run a multi-master system. But if you do, and you use auto-incrementing IDs, you'll have to contend with sequence generation that doesn't produce collisions.
Reasons to not use UUIDs for primary keys
UUIDs aren't a magic bullet, there are trade-offs, and it's important to understand them.
UUIDs are larger.
16-byte UUIDs are larger than 4-byte integers or 8-byte big integers. There's a storage cost to the primary key, its index, any foreign keys, and any of their indexes. Those larger indexes could have performance implications such as longer time to search and more memory needed to cache.
Many UUIDs aren't ordered.
There might be a reason why you need to know what order rows were inserted in, preferably in a way that doesn't change. You could accomplish this with a
created_ator similar date & time column, but that isn't ideal, and many rows may share the same value.
Another issue is if your UUID primary key is clustered (such as with MySQL's InnoDB) there is a cost to row re-ordering when inserting random UUIDs.
UUIDs might sort slower.
If your UUIDs are being stored as strings they are likely to sort much slower than auto-incrementing IDs. See "Making UUIDs More Performant in MySQL" for a workaround.
Making UUIDs More Performant in MySQL
Nov 14, 2020 · 3 min read
MySQL does not have a native UUID type, so strings are often used instead, which come with large storage and performance costs.
Personally I believe the benefits of using UUIDs for primary keys outweigh the costs, but it really depends on the database you're using, the size of your data, and its access patterns.