在SQLite中快速搜索blob起始字节

Question

是否有一种方法来索引blob字段，并将索引用于blob搜索的开始?

目前我有哈希存储为十六进制文本字段。这些十六进制形式的哈希值长度为32个字符，构成了数据库中的大部分数据。

问题是，它们通常按起始字节搜索，如

select * from mytable where hash like '00a1b2%'

我想将它们存储为blob，因为这样可以节省大约30%的数据库大小。然而,

select * from mytable where hex(hash) like '00a1b2%'

的工作，它也慢得多，似乎不使用索引。

查找精确的blob匹配确实使用了索引，所以索引是有效的。

是否有一种方法来执行搜索blob开始(与二进制/memcmp"collation")，将使用索引?

我也尝试了substr()，它显然比hex()快，但仍然没有索引

select * from mytable where substr(hash, 1, 6) = x'00a1b2'

Answer 1

为了能够为LIKE使用索引，表列必须具有TEXT亲和性，并且索引必须不区分大小写:

CREATE TABLE mytable(... hash TEXT, ...);
CREATE INDEX hash_index ON mytable(hash COLLATE NOCASE);

像hex或substr这样的函数防止使用索引。

---

blob可以像其他类型一样被索引和比较。这允许您用两个比较来表示前缀搜索:

SELECT * FROM mytable WHERE hash >= x'00a1b2' AND hash < x'00a1b3'