php mysql 二进制数据,带有二进制数据的SQL查询（PHP和MySQL）

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.

解决方案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{æ×');

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...