Storing MySQL GUID/UUIDs

Question

This is the best way I could come up with to convert a MySQL GUID/UUID generated by UUID() to a binary(16):

UNHEX(REPLACE(UUID(),'-',''))

And then storing it in a BINARY(16)

Are there any implications of doing it this way that I should know of?

Answer 1

I would hash it into a 8-byte integer and store the integer using a low-collision high-efficiency one-way hash algorithm like MurmurHash64A. This uses a lot less space and can be indexed and/or partitioned on. There is a SourceForge project that includes MemCached functions for mySQL (http://forge.mysql.com/projects/project.php?id=250) which might include MurmurHash64A, since Memchached uses it, but I don't know. Or look at this implementation of FNV for mySQL: http://www.xaprb.com/blog/2008/03/09/a-very-fast-fnv-hash-function-for-mysql/

Answer 2

From MySQL 8.0 and above you could use UUID_TO_BIN:

UUID_TO_BIN(string_uuid), UUID_TO_BIN(string_uuid, swap_flag)

Converts a string UUID to a binary UUID and returns the result. (The IS_UUID() function description lists the permitted string UUID formats.) The return binary UUID is a VARBINARY(16) value.

CREATE TABLE t (id binary(16) PRIMARY KEY);

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

SELECT *, BIN_TO_UUID(id) FROM t;

DB-Fiddle.com Demo

Answer 3

Not many implications. It will slow down the queries a little, but you will hardly notice it.

UNIQUEIDENTIFIER is stored as 16-byte binary internally anyway.

If you are going to load the binary into a client and parse it there, note the bit order, it may have other string representation than the initial NEWID().

Oracle's SYS_GUID() function is prone to this issue, converting it to a string gives different results on client and on server.