带有二进制数据的SQL查询(PHP和MySQL) [英] SQL Query with binary data (PHP and MySQL)

问题描述

该站点过去对我有很大帮助，但是现在我迷路了.预先感谢您的指导.

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

我有一个包含Binary值的MySQL表，如下面的示例.我不能改变桌子.

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`))

这是nid的示例值:ÞFÈ>ZPÎ×jRZ{æ×(未全部显示，但所有16个都在其中)

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

现在，我想创建一个SQL查询来查找该值为真的行的ID.

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?

解决方案 以HEX格式获取nid可以达到目的.结果是DE46C83E5A50CED70E6A525A7BE6D709，而当我在查询中使用此代码时……

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';

我得到正确的结果.

推荐答案

注意:此地址用于二进制数据，但不用于加密数据.有关搜索加密数据，请参见此答案.

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

尝试在用于搜索的二进制数据前面添加X，x或0x:

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

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

编辑:也请尝试以下操作:

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{æ×');

如此处所述:如何使用二进制字段进行选择? (php，mysql)

如果以上操作均无效::尝试以HEX格式获取pid，例如

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

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

，然后仅搜索时尝试:

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

但是我不确定如何获取PHP的pid数据，甚至不确定是否将二进制数据传递到select - where查询中...只是由于php标记而引起的猜测...

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

这篇关于带有二进制数据的SQL查询(PHP和MySQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！