How to query BINARY fields in MySQL?

Question

I'm trying to SELECT data from a table that stores Java UUIDs as binary(16).

For example, the UUID 76572de1-aa8c-435b-afe4-8e260e19466b looks like this in phpMyAdmin: 0x76572de1aa8c435bafe48e260e19466b.

Now, how can I query any values from that table by using a simple string?:

SELECT ... WHERE uuid = '76572de1-aa8c-435b-afe4-8e260e19466b'
SELECT ... WHERE uuid = '76572de1aa8c435bafe48e260e19466b'
SELECT ... WHERE uuid = '0x76572de1-aa8c-435b-afe4-8e260e19466b'
SELECT ... WHERE BINARY uuid = '76572de1aa8c435bafe48e260e19466b'
SELECT ... WHERE BINARY uuid = '76572de1-aa8c-435b-afe4-8e260e19466b'
and a lot of other things utilizing CONVERT or BINARY keywords

Try BIN_TO_UUID

Paul Spiegel
– Paul Spiegel

2019-03-05 11:38:58 +00:00
Commented Mar 5, 2019 at 11:38 — Paul Spiegel
– Paul Spiegel, Commented Mar 5, 2019 at 11:38

gandaliter · Accepted Answer · 2019-03-05 11:41:58Z

15

Hexadecimal literals in MySQL look like this: X'01AF' or this: 0x01AF (case insensitive in both cases.

One option would be SELECT ... WHERE uuid = X'76572de1aa8c435bafe48e260e19466b'

edited Mar 5, 2019 at 11:41

answered Mar 5, 2019 at 11:39

gandaliter

10.1k1 gold badge19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

K. D. Over a year ago

Thank you. I've tried that as well, but this just throws an Syntax error when trying to run that Query in phpMyAdmin.

gandaliter Over a year ago

Just edited to remove the hyphens from the guid, which are not valid syntax of course

mtness Feb 13 at 16:53

I just found out about the function SELECT ... WHERE uuid = UNHEX(76572de1aa8c435bafe48e260e19466b)

Paul Spiegel · Accepted Answer · 2019-03-05 11:46:42Z

4

Use BIN_TO_UUID() and UUID_TO_BIN() functions.

SELECT ... WHERE uuid = UUID_TO_BIN('76572de1-aa8c-435b-afe4-8e260e19466b')

edited Mar 5, 2019 at 11:46

answered Mar 5, 2019 at 11:43

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

4 Comments

K. D. Over a year ago

Thank you very much. At least on my current server the BIN_TO_UUID function is not defined, so I'm going with the X'76572de1aa8c435bafe48e260e19466b' approach.

Paul Spiegel Over a year ago

If you already know the binary value, why didn't you just use what you have? 0x76572de1aa8c435bafe48e260e19466b

K. D. Over a year ago

what do you mean? The queries above aren't working, but it's working with the X.

Paul Spiegel Over a year ago

I mean: WHERE uuid = 0x76572de1aa8c435bafe48e260e19466b - But never mind. Its the same as X'...'

Collectives™ on Stack Overflow

How to query BINARY fields in MySQL?

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related