如何在SQLite中搜索特定的BLOB? [英] How to search for a specifc BLOB in SQLite?

问题描述

我将图片中的二进制数据写入SQLite数据库中的BLOB字段"icondata"中.

I write binary data from pictures into my SQLite database into a BLOB field called "icondata".

CREATE TABLE pictures(id INTEGER PRIMARY KEY AUTOINCREMENT, icondata BLOB)

用于写入二进制数据的代码段为:

The code snippet to write the binary data is:

SQLcommand.CommandText = "INSERT INTO pictures (id, icondata) VALUES (" & MyInteger & "," & "@img)"
SQLcommand.Prepare()
SQLcommand.Parameters.Add("@img", DbType.Binary, PicData.Length)
SQLcommand.Parameters("@img").Value = PicData

这很好，我可以使用 SQlite间谍.

但是如何搜索特定的"icondata" BLOB?

But how can I search for a specific "icondata" BLOB?

如果我尝试使用:

SQLcommand.CommandText = "SELECT id FROM pictures WHERE icondata=@img"

，并具有与上述相同的参数:

and with the same Parameters like above:

SQLcommand.Prepare()
SQLcommand.Parameters.Add("@img", DbType.Binary, PicData.Length)
SQLcommand.Parameters("@img").Value = PicData

Dim SQLreader As SQLite.SQLiteDataReader = SQLcommand.ExecuteReader()
While SQLreader.Read()
    ... Process found database entries....
End While

我没有任何结果.

如果将SELECT查询更改为'LIKE'而不是'='，我将获得所有带有BLOB的条目，而不仅仅是匹配的条目.

If I change the SELECT query to 'LIKE' instead of '=' I get all entries with BLOBs, not only the matching one.

我该如何编写SELECT查询以找到特定BLOB的1个完全匹配的条目?

How do I have to write the SELECT query to find the 1 exactly matching entry for a specific BLOB?

推荐答案

您可以在BLOB上搜索以下示例:-

You can search on BLOBs here's some examples :-

DROP TABLE IF EXISTS pictures;
CREATE TABLE IF NOT EXISTS pictures (id INTEGER PRIMARY KEY, icondata columntypedoesnotmatter);
INSERT INTO pictures (icondata) VALUES
    (x'fff1f2f3f4f5f6f7f8f9f0fff1f2f3f4f5f6f7f8f9f0'),
    (x'ffffffffffffffffffff'),
    (x'010203040506070809'),
    (x'010203040506070809010203040506070809')
    ;
SELECT id, hex(icondata) FROM pictures WHERE icondata = x'010203040506070809' OR icondata = x'FFFFFFFFFFFFFFFFFFFF';
SELECT id, hex(icondata) FROM pictures WHERE icondata LIKE '%'||x'F0'||'%';
SELECT id, hex(icondata) FROM pictures WHERE hex(icondata) LIKE '%F0%';

结果为:-

第一个查询按预期方式找到2行(第二和第三行)

First query as expected finds the 2 rows (2nd and 3rd)

即SELECT id, hex(icondata) FROM pictures WHERE icondata = x'010203040506070809' OR icondata = x'FFFFFFFFFFFFFFFFFFFF';产生:-

第二个查询无法正常工作，它是如何不使用LIKE 的一个示例:- 即SELECT id, hex(icondata) FROM pictures WHERE icondata LIKE '%'||x'F0'||'%';导致意外的两行:-

The Second query does not work as expected and is an example of how not to use LIKE :- i.e. SELECT id, hex(icondata) FROM pictures WHERE icondata LIKE '%'||x'F0'||'%'; results in the unexpected two rows :-

• 我相信这种行为是由于仅检查了高/低位，但我找不到相关文档.

第三个查询，但是使用 hex 函数将存储的blob转换为十六进制字符串表示形式，并将其与F0的字符串表示形式进行比较

The third query, however converts the stored blob into it's hexadecimal string representation using the hex function and compares that against the string representation of F0

即SELECT id, hex(icondata) FROM pictures WHERE hex(icondata) LIKE '%F0%';产生预期的单行:-

我从没使用过vb.net，所以不确定是否将上面的代码编码为传递的参数.

I've never used vb.net so I'm not sure how you'd code the above as passed parameters.

这篇关于如何在SQLite中搜索特定的BLOB?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！