How to search a binary typed column in MySQL using GUID

Question

I have a program in C# that connects to my MySQL DB. The main ID column of a table has datatype binary(16). However, I'm having issues trying to query for that specific table. I use Beekeeper Studio as my GUI to interact with the DB so that binary ID column displays Ids like this:

However, I have my GUIDs formatted like this: 41eb9581-9849-452e-986a-b41fe2a943dd. Does anyone know how I can search for a binary value given my GUID looking like that? My version of MySQL does not have UUID_TO_BIN() so I unfortunately can't use that. The random string of characters: ��AI�.E�j��C� is equal to this value 41eb9581-9849-452e-986a-b41fe2a943dd

Queries I've tried that still give me 0 results

SELECT * FROM commitment_node WHERE commitment_node_uid = (UNHEX(REPLACE('41eb9581-9849-452e-986a-b41fe2a943dd', "-","")))

SELECT * FROM commitment_node WHERE commitment_node_uid = X'41eb95819849452e986ab41fe2a943dd'

SELECT * FROM commitment_node WHERE commitment_node_uid = (UNHEX(REPLACE('41eb9581-9849-452e-986a-b41fe2a943dd', "-","")))

Have you already investigated the result of `SELECT hex(commitment_node_uid) FROM commitment_node` ? — Mihe, Aug 18 '22 at 18:41
@Mihe I tried that just now and it seems to result in this value being displayed: `8195EB4149982E45986AB41FE2A943DD` when searching for the row that I believed to be `41eb9581-9849-452e-986a-b41fe2a943dd`. However, one thing I find interesting is that these two values share the same exact characters but are in a different order. Do you know of a simple way to check to find columns based off solely the fact that the column contains all the characters regardless of order? Thank you so much for the help! — slam505, Aug 18 '22 at 18:57
I think you're discovering why binary data in a database column is *extremely annoying* and not really worth it in the end. Hex or base64 encoded is far more usable, even if you do pay a slight storage cost. — tadman, Aug 18 '22 at 19:00
The `X'...'` approach should be what you need here, assuming you've put in precisely the correct value. Check with `SELECT HEX(commitment_node_uid) FROM commitment_node` to verify you're on the right track. — tadman, Aug 18 '22 at 19:01
@slam505, the question is, do you really know that the correct UUID is `41eb9581-9849-452e-986a-b41fe2a943dd` and if so, how this UUID got into the DB as `8195EB4149982E45986AB41FE2A943DD`? — Mihe, Aug 18 '22 at 19:13
@tadman turns out that the value that I was putting in was incorrect, however, the characters in both values are the same. When I do HEX(commitment_node_uid), I receive `8195EB4149982E45986AB41FE2A943DD` but in my code, the value is stored as `41eb9581-9849-452e-986a-b41fe2a943dd` so it seems like the first 3 set of characters are in a different order but the last 2 sets are in the same order. — slam505, Aug 18 '22 at 19:14
It's pretty strange that the order of the characters matter here. This isn't an endian-impacted data form, it's individual bytes, so there shouldn't be any shuffling occurring. Have you tried inserting something in a known form, like `01020304-0506-...` to be sure it gets encoded correctly? — tadman, Aug 18 '22 at 19:16

score 0 · Answer 1 · answered Aug 19 '22 at 07:05

If you're using MySqlConnector, you can use the GuidFormat connection string option to control how Guid .NET objects are serialized when being stored in MySQL; I think Binary16 will be what you want. Creating a MySqlCommand and calling command.Parameters.AddWithValue(YOUR_GUID) will let you write a parameterized query that finds the GUID.

If you have to do this with raw SQL, you're going to need to deal with GUID endianness. The shortest query I can come up with is:

SELECT *
FROM commitment_node
WHERE UPPER('41eb9581-9849-452e-986a-b41fe2a943dd') =
 CONCAT(
  SUBSTRING(HEX(commitment_node_uid), 7, 2),
  SUBSTRING(HEX(commitment_node_uid), 5, 2),
  SUBSTRING(HEX(commitment_node_uid), 3, 2),
  SUBSTRING(HEX(commitment_node_uid), 1, 2),
  '-',
  SUBSTRING(HEX(commitment_node_uid), 11, 2),
  SUBSTRING(HEX(commitment_node_uid), 9, 2),
  '-',
  SUBSTRING(HEX(commitment_node_uid), 15, 2),
  SUBSTRING(HEX(commitment_node_uid), 13, 2),
  '-',
  SUBSTRING(HEX(commitment_node_uid), 17, 4),
  '-',
  SUBSTRING(HEX(commitment_node_uid), 21, 12));

This will swap the byte order of the first three components to match your GUID as a string. (It's highly unlikely MySQL can optimise this, though, so it will likely perform a full table scan, plus a lot of string manipulation, which may be slow.)

How to search a binary typed column in MySQL using GUID

1 Answers1