Storing MySQL GUID/UUIDs

mysql binary guid uuid
thr picture thr · Feb 13, 2009 · Viewed 20.6k times · Source

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

Quassnoi picture Quassnoi · Feb 13, 2009

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.

Related questions

Inserting and selecting UUIDs as binary(16)

I don't understand why SELECT UUID(); Returns something like: 3f06af63-a93c-11e4-9797-00505690773f But if I insert it into a binary(16) field (the UUID() function) with for instance a BEFORE INSERT trigger and run a select, …

Read More
Varbinary vs Blob in MySQL

I have about 2k of raw binary data that I need to store in a table, but don't know whether to choose the Varbinary or Blob type. I have read through the descriptions in the MySQL docs but didn't find …

Read More
What effects does using a binary collation have?

While answering this question, I became uncertain about something that I didn't manage to find a sufficient answer to. What are the practical differences between using the binary utf8_bin and the case insensitive utf8_general_ci collations? I can …

Read More