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?

Solution 1

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.

Solution 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

Comments

[nawfal]

Yes that is, but I could get marginal performance improvements when I relied on application's own guid generation and unhexing and replacing (in my case, .NET)

[Marc L.]

@nawfal, may be an oblique answer to OP, but would really like to see your comment fleshed out with examples.

[Zack Jannsen]

I would add to the comment some material I dug up. Considerations around UUID in MySQL should think about performance as well as uniqueness. While slightly older an interesting performance test was here: kccoder.com/mysql/uuid-vs-int-insert-performance - This shows a point of sensitivity around MySQL and ensuring "UNIQUE" values in MySQL. I am sure there have been improvements but the size of the field, the structure of the contained index, etc. should be contemplated if you have the opportunity. BINARY(16) / CHAR(16) does seem to be the way to go.

[Zack Jannsen]

Doing some more in depth research I also came to a good link on "handling BINARY indexes". Here is the link: mysqlserverteam.com/storing-uuid-values-in-mysql-tables - I'd encourage anyone looking at a conversion to MySQL to review this blog as well. Some great points are brought up to consider around how to 'optimally' store the BINARY id. Your use case may vary based on how you implement UUID but great points on ordering bits and using calculated columns for any 'human readable' needs.

[Marc L.]

I'm confused why this is accepted, when the terminology and assumptions of the answer seem to be exclusive to MS SQL, not MySQL.

[Mike Shiyan]

BIN_TO_UUID() must be called with the 2nd arg also TRUE if UUID_TO_BIN() is called with it.