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:
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');
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.
Thank you. Now how do you covert it to UUID? What would be the opposite function?
You don’t convert it. What most people do is create an extra (unindexed) column to store the raw uuid.
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:
SUBSTR(HEX(id), 1, 8), ‘-‘,
SUBSTR(HEX(id), 9, 4), ‘-‘,
SUBSTR(HEX(id), 13, 4), ‘-‘,
SUBSTR(HEX(id), 17, 4), ‘-‘,
this will restore the UUID to it’s usual format for user consumption.
For those of you still reading – MariaDB 10.7 (expected to have the official release 2021-01-28) will have a proper UUID column type. Woohoo!
Information about it and how to insert/select from/to different formats is covered here https://mariadb.com/kb/en/uuid-data-type/
A note for anyone pre-10.7 who uses UUID as described in this article – if you use DBeaver as your database tool (if you don’t, you should), you can right click the column and set its display type to be as a string UUID. This comes in handy often.
Correction: scheduled 2022-01-28, I forgot we have a new year here