Working on a recent project that dealt with data synchronization across disparate systems, I realized I could not use the oh-so-easy
INT for primary keys. Instead I chose to use the emerging best practice of employing UUIDs for distributed databases.
However, the library functions in Go are not friendly with for MySQL’s
NULLs thus it helps to avoid
NULLs in your schemas when writing Go apps that use MySQL.
So how to represent a “null” UUID? The value I chose was simply a zeroed UUID:
// UUID_TO_BIN() requires MySQL 8+ UUID_TO_BIN('00000000-0000-0000-0000-000000000000',true)
Too much typing tempts typos
But doing so created a conundrum; how to reference it in views and queries without:
- ) Having to type so much each time I reference said value in a query, and
- ) More importantly, how to ensure against accidental typos?
What I needed as a UUID literal for a zeroed UUID, and you think such a thing would be easily findable via a google, but no dice. (I guess most people don’t avoid
A short “literal”
CAST(0 AS BINARY) kept failing because MySQL was interpreting
'0' and encoding it as
30 hexadecimal. What I needed as a actually binary
b'0' to the rescue.
CAST(b'0' AS BINARY) was failing; why? Ah, because that expression returns the shortest binary. What I needed was
BINARY(16), hence the following is exactly what I needed:
// Equal to UUID_TO_BIN('00000000-0000-0000-0000-000000000000',true) CAST(b'0' AS BINARY(16))
My primary use for this need was for use in the
WHERE clause, so I found several other expressions that worked for that purpose too. But I did not use them because they are all cryptic and would hide my intention to a causal reader of the code.
Still, there were interesting to me so I thought I would share them:
WHERE parent_guid=(SELECT uuid FROM uuid LIMIT 1)
WHERE parent_guid=CAST(b'0' AS BINARY(16))
How do those work?
HEX()on a zeroed UUID gives us
'00000000000000000000000000000000'. So clearly
HEX()on our zeroed UUID will equal
- If you
TRIM()the zeros off of
'00000000000000000000000000000000'you get an empty string:
- If you
SOUNDEX()a string of zero characters you get an empty string:
''. This was my favorite alternate simply because if was the shortest.
- If you create a one record, one field table containing a zeroed UUID value, you can of course compare against it in a query.
- Lastly, this is how I am using my “literal” for a zeroed UUID in the
WHEREclauses of my MySQL
VIEWs that need them.
And if you know of a better way, please share in the comments.