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.

Comments 3

  1. i beg to differ! storing the raw UUID completely negates to purpose of storing in as a binary! if you store it raw, then use it raw. doing both wastes space and processing power.

    to convert back to UUID from BIN, you can do the following:

    SELECT
    LOWER(CONCAT(
    SUBSTR(HEX(id), 1, 8), ‘-‘,
    SUBSTR(HEX(id), 9, 4), ‘-‘,
    SUBSTR(HEX(id), 13, 4), ‘-‘,
    SUBSTR(HEX(id), 17, 4), ‘-‘,
    SUBSTR(HEX(id), 21)
    ))
    FROM ;

    this will restore the UUID to it’s usual format for user consumption.

Leave a Reply to David T Cancel reply

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