18

This site had helped me a lot in the past, but now I am lost. Thanks in advance for your guidance.

I have a MySQL table that contains a Binary value, like the example below. I cannot change the table. 

CREATE TABLE `test` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `nid` binary(16) NOT NULL,
   `test` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`))

This an example value of nid: ÞFÈ>ZPÎ×jRZ{æ× (not all showing, but all 16 are there)

Now I want to create a SQL Query to look for the id of the row where this value is true.

SELECT id FROM test WHERE nid = 'ÞFÈ>ZPÎ×jRZ{æ×';

... does not work. Any idea?

SOLUTION Obtaining the nid in HEX format did the trick. It results in DE46C83E5A50CED70E6A525A7BE6D709 and when I use this in the query like this ...

SELECT id FROM test WHERE HEX(nid) = 'DE46C83E5A50CED70E6A525A7BE6D709';

I am getting the right result.

Improve this question
3
  • 1
    The field is pid, not nid. I'm not sure if that's a typo on your part? Commented Jan 24, 2013 at 16:10
  • Yep, that was a typo, sorry. Good catch :) Commented Jan 24, 2013 at 16:25
  • 7
    You should use nid = UNHEX('DE46C83E5A50CED70E6A525A7BE6D709') to use any indexes on nid Commented Aug 5, 2013 at 18:53

4 Answers 4

Reset to default
11

Note: This addresses binary data, but not encrypted data. See this answer for searching on encrypted data.

Try adding X, x or 0x in front of binary data used for search:

SELECT id FROM test WHERE pid = '0xÞFÈ>ZPÎ×jRZ{æ×';

EDIT: try also this:

SELECT id FROM test WHERE BINARY pid = 'ÞFÈ>ZPÎ×jRZ{æ×';

OR

SELECT id FROM test WHERE HEX(pid) = BIN2HEX('0xÞFÈ>ZPÎ×jRZ{æ×');

as supposed here: How to select with a binary field ? (php,mysql)

IF NOTHING FROM ABOVE WORKS: Try obtaining the pid in HEX format, like

SELECT id, HEX(pid) pid, test FROM test

and then when searching try only:

SELECT id, test FROM test WHERE HEX(pid) = '{$my_pid}'

But I'm not sure how do You obtain the pid data to PHP or even whether You pass the binary data into Your select - where query... Just guessing due to the php tag...

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

7 Comments

Tested them all and nothing. I did read the mentioned post, that's why I am asking it here. Strange stuff. :)
The binary data You use in the query is comming out from PHP? How did You obtained that data? Are You sure the data from PHP is really a binary data?
Data is already in DB. I am using PHP for my app. I am seeing this in MySQLWorkbench's value editor: BINARY de 46 c8 3e 5a 50 ce d7 0e 6a 52 5a 7b e6 d7 09
@user2007877 And how do the data appear when printed from PHP? I am quite sure that in MySQl they are represented as BINARY, but about PHP? Probably PHP is messing the data up...
SELECT id FROM test WHERE HEX(nid) = 'DE46C83E5A50CED70E6A525A7BE6D709'; works ... thank you thank you. This thing is strange. Isn't it?
|
4

try:

X''   --Hex Content

mysql> SELECT x'4D7953514C'；
    -> 'MySQL'
Improve this answer

Comments

3

The last posting from jixiang pointed me into the right direction for searching a binary field:

SELECT * FROM test WHERE yourBinaryColumn = x'binarystuffdata';

This works for me...

Improve this answer

Comments

3

For me it works without quotes in the binary field

SELECT * FROM `clients_addresses` WHERE client_id = 0x4f8472e23e63404fb8f9f56
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.