INSERT INTO uuid_test VALUES
('b0dbb53f-d7f3-11ed-b4ed-56000464509c'),
('b0dbb53fd7f3-11edb4ed56000464509c'),
('b-0-d-bb53fd7f311edb4ed56000464509c'),
('b0dbb53f-d7f3-11ed-b4ed-56000464509c');
> SELECT * FROM uuid_test;
+--------------------------------------+
| uuid |
+--------------------------------------+
| b0dbb53f-d7f3-11ed-b4ed-56000464509c |
| b0dbb53f-d7f3-11ed-b4ed-56000464509c |
| b0dbb53f-d7f3-11ed-b4ed-56000464509c |
| b0dbb53f-d7f3-11ed-b4ed-56000464509c |
+--------------------------------------+
Inserting UUIDs as literals can be useful if they are generated by external services, but most of the times you will want MariaDB to generate them automatically. You can do it with the
UUID()
function, or with the
SYS_GUID()
function. The latter has been added for Oracle compatibility. The only difference is that
UUID()
returns string representations of UUIDs with dashes, and the second omits the dashes. But, as mentioned above, when inserting values this difference is irrelevant. Another function,
SHORT_UUID()
, returns shortened UUIDs, but these values are not of the
UUID
type.
> INSERT INTO uuid_test
VALUES
(UUID()),
(SYS_GUID())
RETURNING uuid
+--------------------------------------+
| uuid |
+--------------------------------------+
| 48b264c0-d7f6-11ed-b4ed-56000464509c |
| 48b26634-d7f6-11ed-b4ed-56000464509c |
+--------------------------------------+
The
RETURNING
syntax is very useful if you need to know the automatically generated UUIDs that you insert. See our article on
MariaDB RETURNING Statements
.
A way to generate a UUID and use it multiple times is the following:
START TRANSACTION;
-- theoretically it's unnecessary to run this SELECT in a transaction
-- but if we use ProxySQL or MaxScale we want all queries to be sent
-- to the same MariaDB instance
SELECT @uuid := UUID();
INSERT INTO band (uuid, name) VALUES (@uuid, 'Deep Purple');
INSERT INTO album (uuid, band_uuid, title) VALUES (UUID(), @uuid, 'Machine Head');
COMMIT;
Note that a
UUID
value takes 16 bytes. If you store the same value in the form of a string representation, it will be 36 characters. If you use the
utf8mb4
character set, it will take 72 bytes. If you use
ascii
, it will take 36 characters. For more discussions on this topic, see
Working with MD5 or other hashes
.
UUIDs as primary keys
UUID
s can be used as primary keys. First of all, let’s see what the pros and cons are compared to alternatives. Then we’ll discuss how to use
UUID
primary keys in practice.
UUID versus AUTO_INCREMENT
In InnoDB tables, primary key values should be inserted in a chronological order. This is true for any B-Tree index with any storage engine, because these indexes are ordered data structures, and inserting a value in the middle of an ordered data structure is not very efficient. But in InnoDB, tables are physically ordered by the primary key. For more details, see
Why Tables need a Primary Key in MariaDB and MySQL
.
An easy way to satisfy this requirement is to use an
AUTO_INCREMENT
primary key. In this way, integer numbers will be generated in order. But such keys have some problems:
The generation of
AUTO_INCREMENT
values is governed by a lock at a table level.
For write-intensive workloads this can result in frequent waits. See the
MariaDB documentation
.
When the maximum value is reached, trying to insert new rows will result in an error. Even if there are holes, the missing number won’t be regenerated unless you run
ALTER TABLE tab AUTO_INCREMENT = num
. But even this statement will fail if some numbers exist in the table that are higher than
num
. You might think that, if you use
BIGINT UNSIGNED
, the limit will never be reached. Yet, in many cases even that limit is reached. This doesn’t necessarily mean that a table has billions of rows:
AUTO_INCREMENT
values are wasted if rows are deleted, or if a transaction that inserts rows rolls back.
To find out how to make sure that the maximum won’t be reached, see
How to monitor that AUTO_INCREMENT columns won’t reach their limit
.
It was mentioned before that primary key values should be inserted in a chronological order. If you know UUID formats, you surely know that the timestamp bits are “shuffled”, so UUIDs don’t follow a chronological order. That’s why the Laravel PHP framework implements a non-standard
ordered UUID
. MariaDB uses standard UUIDs of type 1, but its timestamp bits are written on disk in a format that follows the chronological order. So, while you’ll see regular, non-ordered UUIDs, the
UUID
type follows the requirements for an efficient primary key.
Some might object that UUID values consist of 16 bytes, while
BIGINT
values are 8 bytes long, and
INT
values are only 4 bytes long. This has several consequences:
Most consultants don’t provide such recommendations, because it would involve skills that are beyond the database itself: understanding how databases are used, collaborating with different teams, etc. So they tend to overstate the importance of minor tweaks and pretend there is nothing more they can do. At Vettabase, we don’t use this approach.
Using UUID primary keys
To create
UUID
primary keys, it’s a good idea to specify a
DEFAULT
value:
CREATE OR REPLACE TABLE film (
uuid UUID NOT NULL DEFAULT UUID(),
title VARCHAR(200)
In this case, applications can simply ignore UUID columns when they insert values, as they are generated automatically:
INSERT INTO film (title) VALUES
('Restless Natives'),
('The Angels\' Share'),
('Edie'),
('Trainspotting');
> SELECT * FROM film;
+--------------------------------------+-------------------+
| uuid | title |
+--------------------------------------+-------------------+
| a473548d-d8be-11ed-b7c0-56000464509c | Restless Natives |
| a47359ac-d8be-11ed-b7c0-56000464509c | The Angels' Share |
| a4735aba-d8be-11ed-b7c0-56000464509c | Edie |
| a4735b46-d8be-11ed-b7c0-56000464509c | Trainspotting |
+--------------------------------------+-------------------+
Conclusion
UUIDs are a modern way to generate unique identifiers. MariaDB introduced a handy UUID data type to store this information efficiently. It is particularly useful as a primary key, because it eliminates the inconvenience of using AUTO_INCREMENT IDs. I recommend that you start using UUIDs as identifiers in your applications.
If you’re interested in MariaDB 10.11 features, make sure you don’t miss my next webinar on April 27th: MariaDB 10.11, key features overview for DBAs.
Federico Razzoli
All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer.
In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software.
As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.
