Using In a Database¶
Tip
ramsey/uuid-doctrine allows the use of ramsey/uuid as a Doctrine field type. If you use Doctrine, it’s a great option for working with UUIDs and databases.
There are several strategies to consider when working with UUIDs in a database. Among these are whether to store the string representation or bytes and whether the UUID column should be treated as a primary key. We’ll discuss a few of these approaches here, but the final decision on how to use UUIDs in a database is up to you since your needs will be different from those of others.
Note
All database code examples in this section assume the use of MariaDB and PHP Data Objects (PDO). If using a different database engine or connection library, your code will differ, but the general concepts should remain the same.
Storing As a String¶
Perhaps the easiest way to store a UUID to a database is to create a char(36)
column and store the UUID as a string. When stored as a string, UUIDs require
no special treatment in SQL statements or when displaying them.
The primary drawback is the size. At 36 characters, UUIDs can take up a lot of space, and when handling a lot of data, this can add up.
CREATE TABLE `notes` (
`uuid` char(36) NOT NULL,
`notes` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Using this database table, we can store the string UUID using code similar to this (assume some of the variables in this example have been set beforehand):
use Ramsey\Uuid\Uuid;
$uuid = Uuid::uuid4();
$dbh = new PDO($dsn, $username, $password);
$sth = $dbh->prepare('
INSERT INTO notes (
uuid,
notes
) VALUES (
:uuid,
:notes
)
');
$sth->execute([
':uuid' => $uuid->toString(),
':notes' => $notes,
]);
Storing As Bytes¶
In the previous example, we saw how
to store the string representation of a UUID to a char(36) column. As
discussed, the primary drawback is the size. However, if we store the UUID in
byte form, we only need a char(16) column, saving over half the space.
The primary drawback with this approach is ease-of-use. Since the UUID bytes are stored in the database, querying and selecting data becomes more difficult.
CREATE TABLE `notes` (
`uuid` char(16) NOT NULL,
`notes` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Using this database table, we can store the UUID bytes using code similar to this (again, assume some of the variables in this example have been set beforehand):
$sth->execute([
':uuid' => $uuid->getBytes(),
':notes' => $notes,
]);
Now, when we SELECT the records from the database, we will need to convert
the notes.uuid column to a ramsey/uuid object, so that we are able to use
it.
use Ramsey\Uuid\Uuid;
$uuid = Uuid::uuid4();
$dbh = new PDO($dsn, $username, $password);
$sth = $dbh->prepare('SELECT uuid, notes FROM notes');
$sth->execute();
foreach ($sth->fetchAll() as $record) {
$uuid = Uuid::fromBytes($record['uuid']);
printf(
"UUID: %s\nNotes: %s\n\n",
$uuid->toString(),
$record['notes']
);
}
We’ll also need to query the database using the bytes.
use Ramsey\Uuid\Uuid;
$uuid = Uuid::fromString('278198d3-fa96-4833-abab-82f9e67f4712');
$dbh = new PDO($dsn, $username, $password);
$sth = $dbh->prepare('
SELECT uuid, notes
FROM notes
WHERE uuid = :uuid
');
$sth->execute([
':uuid' => $uuid->getBytes(),
]);
$record = $sth->fetch();
if ($record) {
$uuid = Uuid::fromBytes($record['uuid']);
printf(
"UUID: %s\nNotes: %s\n\n",
$uuid->toString(),
$record['notes']
);
}
Using As a Primary Key¶
In the previous examples, we didn’t use the UUID as a primary key, but it’s
logical to use the notes.uuid field as a primary key. There’s nothing wrong
with this approach, but there are a couple of points to consider:
InnoDB stores data in the primary key order
All the secondary keys also contain the primary key (in InnoDB)
We’ll deal with the first point in the section, Insertion Order and Sorting. For the
second point, if you are using the string version of the UUID (i.e.,
char(36)), then not only will the primary key be large and take up a lot of
space, but every secondary key that uses that primary key will also be much
larger.
For this reason, if you choose to use UUIDs as primary keys, it might be worth
the drawbacks to use UUID bytes (i.e., char(16)) instead of the string
representation (see Storing As Bytes).
Hint
If not using InnoDB with MySQL or MariaDB, consult your database engine documentation to find whether it also has similar properties that will factor into your use of UUIDs.
Using As a Unique Key¶
Instead of using UUIDs as a primary key, you may choose to
use an AUTO_INCREMENT column with the int unsigned data type as a
primary key, while using a char(36) for UUIDs and setting a UNIQUE KEY
on this column. This will aid in lookups while helping keep your secondary keys
small.
CREATE TABLE `notes` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`uuid` char(36) NOT NULL,
`notes` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `notes_uuid_uk` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Insertion Order and Sorting¶
UUID versions 1, 2, 3, 4, and 5 are not monotonically increasing. If using these versions as primary keys, the inserts will be random, and the data will be scattered on disk (for InnoDB). Over time, as the database size grows, lookups will become slower and slower.
Tip
See Percona’s “Storing UUID Values in MySQL” post, for more details on the performance of UUIDs as primary keys.
To minimize these problems, two solutions have been devised:
Note
We previously recommended the use of the timestamp-first COMB or ordered-time codecs to solve these problems. However, UUID versions 6 and 7 were defined to provide these solutions in a standardized way.