Store UUID v4 in MySQL

Question

I'm generating UUIDs using PHP, per the function found here.

Now I want to store that in a MySQL database. What is the best/most efficient MySQL data type for storing UUID v4?

I currently have varchar(256), but I'm pretty sure that's much larger than necessary. I've found lots of almost-answers, but they're generally ambiguous about what form of UUID they're referring to, so I'm asking for the specific format.

Answer 1

There are already some answers here that focus on VARCHAR vs. BINARY representation. In that aspect: for small tables use VARCHAR for its convenience to be able to read values directly. For large (more than say ten thousand rows) tables that need to scale and be performant, use the binary format (there are conversion methods making access to the fields pretty easy, albeit a tad more cumbersome than if you could directly read them).

However, the perhaps more important aspect for performance is the structure of your UUIDs. If they are generated randomly that can mess up the indexing as well. Preferably the generated UUIDs follow a ordering such that UUIDs produced later come later in that ordering. This article has some nice performance comparison chart between using a bigint, an random based uuid and a time-ordered uuid (both with binary representation) as a primary key: https://www.percona.com/blog/store-uuid-optimized-way/ Here is one of the graphs as illustration, but checkout the article for a more extensive overview.

Answer 2

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;

Answer 3

This is a fairly old post but still relevant and comes up in search results often, so I will add my answer to the mix. Since you already have to use a trigger or your own call to UUID() in your query, here are a pair of functions that I use to keep the UUID as text in for easy viewing in the database, but reducing the footprint from 36 down to 24 characters. (A 33% savings)

delimiter //

DROP FUNCTION IF EXISTS `base64_uuid`//
DROP FUNCTION IF EXISTS `uuid_from_base64`//


CREATE definer='root'@'localhost' FUNCTION base64_uuid() RETURNS varchar(24)
DETERMINISTIC
BEGIN
    /* converting INTO base 64 is easy, just turn the uuid into binary and base64 encode */
    return to_base64(unhex(replace(uuid(),'-','')));
END//

CREATE definer='root'@'localhost' FUNCTION uuid_from_base64(base64_uuid varchar(24)) RETURNS varchar(36)
DETERMINISTIC
BEGIN
    /* Getting the uuid back from the base 64 version requires a little more work as we need to put the dashes back */
    set @hex = hex(from_base64(base64_uuid));
    return lower(concat(substring(@hex,1,8),'-',substring(@hex,9,4),'-',substring(@hex,13,4),'-',substring(@hex,17,4),'-',substring(@hex,-12)));
END//

Answer 4

This works like a charm for me in MySQL 8.0.26

create table t (
    uuid BINARY(16) default (UUID_TO_BIN(UUID())),
)

When querying you may use

select BIN_TO_UUID(uuid) uuid from t;

The result is:

# uuid
'8c45583a-0e1f-11ec-804d-005056219395'

Answer 5

This could be useful if you use binary(16) data type:

INSERT INTO table (UUID) VALUES
   (UNHEX(REPLACE(UUID(), "-","")))

Answer 6

I just found a nice article going in more depth on these topics: https://www.xaprb.com/blog/2009/02/12/5-ways-to-make-hexadecimal-identifiers-perform-better-on-mysql/

It covers the storage of values, with the same options already expressed in the different answers on this page:

• One: watch out for character set
• Two: use fixed-length, non-nullable values
• Three: Make it BINARY

But also adds some interesting insight about indexes:

• Four: use prefix indexes

In many but not all cases, you don’t need to index the full length of the value. I usually find that the first 8 to 10 characters are unique. If it’s a secondary index, this is generally good enough. The beauty of this approach is that you can apply it to existing applications without any need to modify the column to BINARY or anything else—it’s an indexing-only change and doesn’t require the application or the queries to change.

Note that the article doesn't tell you how to create such a "prefix" index. Looking at MySQL documentation for Column Indexes we find:

[...] you can create an index that uses only the first N characters of the column. Indexing only a prefix of column values in this way can make the index file much smaller. When you index a BLOB or TEXT column, you must specify a prefix length for the index. For example:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

[...] the prefix length in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements is interpreted as number of characters for nonbinary string types (CHAR, VARCHAR, TEXT) and number of bytes for binary string types (BINARY, VARBINARY, BLOB).

• Five: build hash indexes

What you can do is generate a checksum of the values and index that. That’s right, a hash-of-a-hash. For most cases, CRC32() works pretty well (if not, you can use a 64-bit hash function). Create another column. [...] The CRC column isn’t guaranteed to be unique, so you need both criteria in the WHERE clause or this technique won’t work. Hash collisions happen quickly; you will probably get a collision with about 100k values, which is much sooner than you might think—don’t assume that a 32-bit hash means you can put 4 billion rows in your table before you get a collision.

Answer 7

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.

Answer 8

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))

Answer 9

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

Answer 10

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.