带有二进制数据的 SQL 查询（PHP 和 MySQL）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14505673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query with binary data (PHP and MySQL)
提问by user2007877
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.
我有一个包含二进制值的 MySQL 表，如下例所示。我不能换桌子。
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: TFè>ZP?×jRZ{?×(not all showing, but all 16 are there)
这是 nid 的示例值：（TFè>ZP?×jRZ{?×并非全部显示，但所有 16 个都在那里）
Now I want to create a SQL Query to look for the id of the row where this value is true.
现在我想创建一个 SQL 查询来查找该值为 true 的行的 id。
SELECT id FROM test WHERE nid = 'TFè>ZP?×jRZ{?×';
... does not work. Any idea?
...不起作用。任何的想法？
SOLUTIONObtaining the nid in HEX format did the trick. It results in DE46C83E5A50CED70E6A525A7BE6D709 and when I use this in the query like this ...
解决方案获得十六进制格式的 nid 成功了。它导致 DE46C83E5A50CED70E6A525A7BE6D709 并且当我在这样的查询中使用它时......
SELECT id FROM test WHERE HEX(nid) = 'DE46C83E5A50CED70E6A525A7BE6D709';
I am getting the right result.
我得到了正确的结果。
采纳答案by shadyyx
Note: This addresses binary data, but not encrypteddata. See this answerfor searching on encrypted data.
注意：这解决了二进制数据，而不是加密数据。请参阅此答案以搜索加密数据。
Try adding X, xor 0xin front of binary data used for search:
尝试在用于搜索的二进制数据前添加X,x或0x：
SELECT id FROM test WHERE pid = '0xTFè>ZP?×jRZ{?×';
EDIT:try also this:
编辑：也试试这个：
SELECT id FROM test WHERE BINARY pid = 'TFè>ZP?×jRZ{?×';
OR
或者
SELECT id FROM test WHERE HEX(pid) = BIN2HEX('0xTFè>ZP?×jRZ{?×');
as supposed here: How to select with a binary field ? (php,mysql)
在这里假设：如何使用二进制字段进行选择？(php,mysql)
IF NOTHING FROM ABOVE WORKS:Try obtaining the pidin HEXformat, like
如果没有从上面的工作原理：尝试获取pid的HEX格式，如
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 piddata to PHP or even whether You pass the binary data into Your select - wherequery... Just guessing due to the phptag...
但是我不确定您如何将pid数据获取到 PHP，甚至不确定您是否将二进制数据传递到您的select - where查询中......只是由于php标签而猜测......
回答by jixiang
try:
尝试：
X'' --Hex Content
mysql> SELECT x'4D7953514C'；
-> 'MySQL'
回答by Snow
The last posting from jixiang pointed me into the right direction for searching a binary field:
jixiang 的最后一个帖子为我指明了搜索二进制字段的正确方向：
SELECT * FROM test WHERE yourBinaryColumn = x'binarystuffdata';
This works for me...
这对我有用...
回答by murilosandiego
For me it works without quotes in the binary field
对我来说，它在二进制字段中没有引号
SELECT * FROM `clients_addresses` WHERE client_id = 0x4f8472e23e63404fb8f9f56
