Store UUID v4 in MySQL

Solution 1:

Store it as VARCHAR(36) if you're looking to have an exact fit, or VARCHAR(255) which is going to work out with the same storage cost anyway. There's no reason to fuss over bytes here.

Remember VARCHAR fields are variable length, so the storage cost is proportional to how much data is actually in them, not how much data could be in them.

Storing it as BINARY is extremely annoying, the values are unprintable and can show up as garbage when running queries. There's rarely a reason to use the literal binary representation. Human-readable values can be copy-pasted, and worked with easily.

Some other platforms, like Postgres, have a proper UUID column which stores it internally in a more compact format, but displays it as human-readable, so you get the best of both approaches.

Solution 2:

If you always have a UUID for each row, you could store it as CHAR(36) and save 1 byte per row over VARCHAR(36).

uuid CHAR(36) CHARACTER SET ascii

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes. https://dev.mysql.com/doc/refman/5.7/en/char.html

Though be careful with CHAR, it will always consume the full length defined even if the field is left empty. Also, make sure to use ASCII for character set, as CHAR would otherwise plan for worst case scenario (i.e. 3 bytes per character in utf8, 4 in utf8mb4)

[...] MySQL must reserve four bytes for each character in a CHAR CHARACTER SET utf8mb4 column because that is the maximum possible length. For example, MySQL must reserve 40 bytes for a CHAR(10) CHARACTER SET utf8mb4 column. https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

Solution 3:

Question is about storing an UUID in MySQL.

Since version 8.0 of mySQL you can use binary(16) with automatic conversion via UUID_TO_BIN/BIN_TO_UUID functions: https://mysqlserverteam.com/mysql-8-0-uuid-support/

Be aware that mySQL has also a fast way to generate UUIDs as primary key:

INSERT INTO t VALUES(UUID_TO_BIN(UUID(), true))

Solution 4:

Most efficient is definitely BINARY(16), storing the human-readable characters uses over double the storage space, and means bigger indices and slower lookup. If your data is small enough that storing as them as text doesn't hurt performance, you probably don't need UUIDs over boring integer keys. Storing raw is really not as painful as others suggest because any decent db admin tool will display/dump the octets as hexadecimal, rather than literal bytes of "text". You shouldn't need to be looking up UUIDs manually in the db; if you have to, HEX() and x'deadbeef01' literals are your friends. It is trivial to write a function in your app – like the one you referenced – to deal with this for you. You could probably even do it in the database as virtual columns and stored procedures so the app never bothers with the raw data.

I would separate the UUID generation logic from the display logic to ensure that existing data are never changed and errors are detectable:

function guidv4($prettify = false)
{
    static $native = function_exists('random_bytes');

    $data = $native ? random_bytes(16) : openssl_random_pseudo_bytes(16);
    $data[6] = chr(ord($data[6]) & 0x0f | 0x40); // set version to 0100
    $data[8] = chr(ord($data[8]) & 0x3f | 0x80); // set bits 6-7 to 10
    if ($prettify) {
        return guid_pretty($data);
    }
    return $data;
}

function guid_pretty($data)
{
    return strlen($data) == 16 ?
        vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4)) :
        false;
}

function guid_ugly($data)
{
    $data = preg_replace('/[^[:xdigit:]]+/', '', $data);
    return strlen($data) == 32 ? hex2bin($data) : false;
}

Edit: If you only need the column pretty when reading the database, a statement like the following is sufficient:

ALTER TABLE test ADD uuid_pretty CHAR(36) GENERATED ALWAYS AS (CONCAT_WS('-', LEFT(HEX(uuid_ugly), 8), SUBSTR(HEX(uuid_ugly), 9, 4), SUBSTR(HEX(uuid_ugly), 13, 4), SUBSTR(HEX(uuid_ugly), 17, 4), RIGHT(HEX(uuid_ugly), 12))) VIRTUAL;

Solution 5:

The most space-efficient would be BINARY(16) or two BIGINT UNSIGNED.

The former might give you headaches because manual queries do not (in a straightforward way) give you readable/copyable values. The latter might give you headaches because of having to map between one value and two columns.

If this is a primary key, I would definitely not waste any space on it, as it becomes part of every secondary index as well. In other words, I would choose one of these types.

For performance, the randomness of random UUIDs (i.e. UUID v4, which is randomized) will hurt severely. This applies when the UUID is your primary key or if you do a lot of range queries on it. Your insertions into the primary index will be all over the place rather than all at (or near) the end. Your data loses temporal locality, which was a helpful property in various cases.

My main improvement would be to use something similar to a UUID v1, which uses a timestamp as part of its data, and ensure that the timestamp is in the highest bits. For example, the UUID might be composed something like this:

Timestamp | Machine Identifier | Counter

This way, we get a locality similar to auto-increment values.