Using UUID as primary key in MySQL/MariaDB databases

Many professionals recommend using UUID (Universally Unique Identifiers) also known as GUID (Globally Unique Identifier). The idea is using them as primary keys rather than the default integers with the AUTO_INCREMENT flag. The reason why is because they are unique across every table, every database and every server, allowing easy merging of records from different databases and easy distribution of databases across multiple servers.

Using UUID in MySQL/MariaDB

Creating an UUID is as easy as using the function UUID(). This generates a 36 characters hexadecimal key (with 4 dashes included). However the string generated is a bit unfriendly, something like this:

584da03d-18d7-4701-9808-eec48f65e797

It’s a bit ugly right? So we are going to convert it into a nice compact and faster 16-byte key. So it can be stored in a BINARY(16) column.

Converting hexadecimal UUID into binary

Let’s make a example of how a binary UUID can be easily created in MySQL or MariaDB. First we create an example table:

CREATE TABLE `Users` (
  id BINARY(16) NOT NULL, 
  user VARCHAR(15) NOT NULL ,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now we just need to create the UUIDs on the fly using the following functions:
UUID(): Generates the 36 characters hexadecimal key.
REPLACE(): We use this function to get rid of all dashes within the key.
UNHEX(): We use this function to finally convert the hex characters into a 16bytes key (128bits).

The final query will look like this:

INSERT INTO Users (id, user) VALUES (UNHEX(REPLACE(UUID(),'-','')), 'Jimmy');

Conclusions

This is just a quick way for generating UUIDs. However a much better approach in order to avoid collisions would be having a dedicated server generating all the UUIDs. If you want to build a serious App I would recommend you to have a look at Twitter’s Snowflake.

Leave a Reply

Your email address will not be published. Required fields are marked *