Posted on by Mike Schinkel

How to specify a zeroed UUID in MySQL

Photo by Chris Barbalis on Unsplash

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:

  1. ) Having to type so much each time I reference said value in a query, and
  2. ) 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 NULLs?)

A short “literal”

The “obvious” CAST(0 AS BINARY) kept failing because MySQL was interpreting 0 as '0' and encoding it as 30 hexadecimal. What I needed as a actually binary 0, this b'0' to the rescue.

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

Honorable mentions

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:

  1. WHERE HEX(parent_guid)=REPEAT('0',32)
  2. WHERE REPLACE(HEX(parent_guid),'0','')=''
  3. WHERE SOUNDEX(HEX(parent_guid))=''
  4. WHERE parent_guid=(SELECT uuid FROM uuid LIMIT 1)
  5. WHERE parent_guid=CAST(b'0' AS BINARY(16))

How do those work?

  1. Using HEX() on a zeroed UUID gives us '00000000000000000000000000000000'. So clearly HEX() on our zeroed UUID will equal REPEAT('0',32).
  2. If you TRIM() the zeros off of '00000000000000000000000000000000' you get an empty string: ''
  3. If you SOUNDEX() a string of zero characters you get an empty string: ''. This was my favorite alternate simply because if was the shortest.
  4. If you create a one record, one field table containing a zeroed UUID value, you can of course compare against it in a query.
  5. Lastly, this is how I am using my “literal” for a zeroed UUID in the WHERE clauses of my MySQL VIEWs that need them.

And if you know of a better way, please share in the comments.