Programster's Blog

Tutorials focusing on Linux, programming, and open-source

PHP - Using UUID Instead of Auto Increment or Sequence

A universally unique identifier (UUID) is a 128 bit number used to identify information in computer systems. An example where you might see them is in Linux when you run blkid to identify your disks as shown below:

/dev/sda1: UUID="039b9d65-373a-4a64-ba35-696198bc57f1" TYPE="ext4" PARTUUID="f7649116-01"
/dev/sda5: UUID="ef2ece1c-dab1-47e7-9e39-79ba1921a302" TYPE="swap" PARTUUID="f7649116-05"

As you can see, each of the disks is given a UUID which you one use to identify them. This will never change, unlike the drive letter which may.

If you're like me, you've been using integers generated by MySQL auto_increment for the IDs of the rows in your tables. If you are using PostgreSQL you may be using a sequence/serial instead. However, there are many advantages to swapping over to using a UUID instead. The two main ones for me are:

Can assign an object an ID in your application layer and insert it into the database later. This allows batch inserts of objects without needing to run a SELECT query later to get the IDs.
Can copy data across from one database to another without worrying about identifiers clashing. Thus there is no need to change them and worry about maintaining the foreign key relationships.

This tutorial will show you how to generate UUIDs in PHP, and learn to store/retrieve them in either MySQL or PostgreSQL.

Generating UUIDs In PHP

I am going to use a package recommended from an online talk, for generating my UUIDs.

Installation

composer require ramsey/uuid

Example Usage

<?php 
require_once(__DIR__ . '/vendor/autoload.php');

$id = \Ramsey\Uuid\Uuid::uuid4();
echo $id;

Output

634adfdb-0513-4c33-bc41-03eb8c0c0ad1

Performance Improvement

The example above will generate a type 4 UUID, which is very random. It would be better for database performance if the UUIDs generated were sequential. E.g. the "number" always got bigger. This will mean that records are appended to the end of our database tables rather than inserted randomly throughout. This may tempt you to use a type 1 UUID (which is time based), but the code below will allow you to generate sequential type 4 UUIDs.

$factory = new \Ramsey\Uuid\UuidFactory();

$generator = new \Ramsey\Uuid\Generator\CombGenerator(
    $factory->getRandomGenerator(), 
    $factory->getNumberConverter()
);

$codec = new Ramsey\Uuid\Codec\TimestampFirstCombCodec($factory->getUuidBuilder());

$factory->setRandomGenerator($generator);
$factory->setCodec($codec);

Ramsey\Uuid\Uuid::setFactory($factory);
$uuidString1 = Ramsey\Uuid\Uuid::uuid4()->toString();

MySQL Integration

The easiest way to get started, would be to just use a varchar field like below

CREATE TABLE `user`(
    id varchar(37), 
    name varchar(200),
    PRIMARY KEY (`id`)
);

INSERT INTO `user` (`id`, `name`) 
VALUES ('634adfdb-0513-4c33-bc41-03eb8c0c0ad1', 'programster');

... however, perhaps you're like me and the thought of using a varchar type for your primary key makes you cringe. This might be okay for small tables, but not for large ones. We can improve performance by using a binary format.

Creating A Table With Binary ID

We can create the table and then insert the data in binary format by using the unhex function after removing the hyphens:

CREATE TABLE IF NOT EXISTS `table1` (
    `id` binary(16), 
    `name` varchar(200),
    PRIMARY KEY (id)
);

INSERT INTO `table1` (`id`, `name`) 
VALUES 
    (unhex(replace('8a4f12c2-0a8a-40d8-a50f-bcad77cf9622', '-', '')), 'john'),
    (unhex(replace('8a4f12c2-0aa9-47a8-9c9d-3a79570af15a', '-', '')), 'david'),
    (unhex(replace('8a4f12c2-0ab9-4867-ab04-f28aa232f3c9', '-', '')), 'suzy'),
    (unhex(replace('8a4f12c2-0ac1-4676-bdfd-152dfaa6426f', '-', '')), 'edward');

Unfortunately, when you select the data, you will get what appears to be junk out.

SELECT * FROM `table1`;

+------------------+--------+
| id               | name   |
+------------------+--------+
| ï¿½Oï¿½
ï¿½@Ø¥ï¿½ï¿½wÏ–"        | john   |
| ï¿½Oï¿½
ï¿½Gï¿½ï¿½ï¿½:yW
ï¿½Z        | david  |
| ï¿½Oï¿½
ï¿½Hgï¿½ï¿½ï¿½ï¿½2ï¿½ï¿½          | suzy   |
| ï¿½Oï¿½
ï¿½Fvï¿½ï¿½-ï¿½ï¿½Bo        | edward |
+------------------+--------+

Convert Binary To String UUID

Luckily, we can use PHP to convert this binary form into a human readable string with the following logic (courtesy of a Stack Overflow post):

$selectQuery = "SELECT * FROM `table1`";
$result = $db->query($selectQuery);

while (($row = $result->fetch_assoc()) != null)
{
    $binary = $row['id'];
    $string = unpack("H*", $binary);

    $uuidArray = preg_replace(
        "/([0-9a-f]{8})([0-9a-f]{4})([0-9a-f]{4})([0-9a-f]{4})([0-9a-f]{12})/", 
        "$1-$2-$3-$4-$5", 
        $string
    );

    $uuidString = array_pop($uuidArray); // should only be one element.
    $name = $row['name'];
    print "$uuidString $name" . PHP_EOL;
}

Convert String UUID To Binary

If you would rather use PHP to convert the UUID string to binary, rather than rely on the MySQL unhex function, then you could use:

$binary =  pack("H*", str_replace('-', '', $uuidString));

Test Script

Please see the appendix if you would like grab an example script that uses all the code samples provided to test that using PHP or MySQL to convert the UUID string to binary and back again does work interchangeably.

PostgreSQL Integration

Luckily, PostgreSQL natively supports the UUID type, which makes life much easier. We do not have to worry about converting to/from binary format as you might with MySQL. We can just insert the UUIDs in string format and trust the database to handle them in an optimal way.

CREATE TABLE IF NOT EXISTS table1 (
    id UUID, 
    name varchar(200),
    PRIMARY KEY (id)
);

INSERT INTO table1 (id, name) 
VALUES 
    ('8a4f12c2-0a8a-40d8-a50f-bcad77cf9622', 'john'),
    ('8a4f12c2-0aa9-47a8-9c9d-3a79570af15a', 'david'),
    ('8a4f12c2-0ab9-4867-ab04-f28aa232f3c9', 'suzy'),
    ('8a4f12c2-0ac1-4676-bdfd-152dfaa6426f', 'edward');

Appendix

MySQL Integration Test Script

<?php

/*
 * In order to be able to run this script, you need to use the following
 * commands in order to install the necessary packages:
 *  composer require ramsey/uuid
 *  composer require moontoast/math 
 */

require_once(__DIR__ . '/vendor/autoload.php');
require_once(__DIR__ . '/settings.php');


# Create MySQL database connection
# These defines were defined in my settings.php file.
$db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

# Create our test table if it does not exist.
$createTableQuery = 
    "CREATE TABLE IF NOT EXISTS `table1` (
        `id` binary(16), 
        `name` varchar(200),
        PRIMARY KEY (id)
    )";

$db->query($createTableQuery);


// Create some UUIDs for inserting into the database.
$factory = new \Ramsey\Uuid\UuidFactory();

$generator = new \Ramsey\Uuid\Generator\CombGenerator(
    $factory->getRandomGenerator(), 
    $factory->getNumberConverter()
);

$codec = new Ramsey\Uuid\Codec\TimestampFirstCombCodec($factory->getUuidBuilder());

$factory->setRandomGenerator($generator);
$factory->setCodec($codec);

Ramsey\Uuid\Uuid::setFactory($factory);
$uuidString1 = Ramsey\Uuid\Uuid::uuid4()->toString();
$uuidString2 = Ramsey\Uuid\Uuid::uuid4()->toString();

print "Generated uuids: {$uuidString1} and {$uuidString2}" . PHP_EOL;

// Insert one of the UUIDs into the database using mysql functions to convert to binary.
$insertQuery1 = 
    "INSERT INTO `table1` (`id`, `name`) 
    VALUES 
        (unhex(replace('" . $uuidString1 . "', '-', '')), 'using MySQL unhex')";

$db->query($insertQuery1);

// convert the other uuid string to binary using PHP and insert that into the database.
$binaryUUID =  pack("H*", str_replace('-', '', $uuidString2));

$insertQuery2 = 
    "INSERT INTO `table1` (`id`, `name`) 
    VALUES 
         ('{$binaryUUID}', 'using PHP conversion to binary')";

$db->query($insertQuery2);


// Select the data from the table and convert it to string form for us to read.
$selectQuery = "SELECT * FROM `table1`";
$result = $db->query($selectQuery);

while (($row = $result->fetch_assoc()) != null)
{
    $binary = $row['id'];
    $string = unpack("H*", $binary);

    $uuidArray = preg_replace(
        "/([0-9a-f]{8})([0-9a-f]{4})([0-9a-f]{4})([0-9a-f]{4})([0-9a-f]{12})/", 
        "$1-$2-$3-$4-$5", 
        $string
    );

    $uuidString = array_pop($uuidArray); // should only be one element.
    $name = $row['name'];
    print "$uuidString $name" . PHP_EOL;
}

Example output:

Generated uuids: 8a54f5b7-1363-4a82-9cc4-c0a73c5f6aee and 8a54f5b7-138a-4e3d-abbf-b50bb884a32f
8a54f5b7-1363-4a82-9cc4-c0a73c5f6aee using MySQL unhex
8a54f5b7-138a-4e3d-abbf-b50bb884a32f using PHP conversion to binary

References

Last updated: 3rd September 2023
First published: 16th August 2018