问题描述
我有一个带有单列的表，我用两种方式查询它:
I have a table with a single column, which I query in two ways:
-
SELECT * FROM sequences WHERE seqs="blablabla" -
SELECT * FROM sequences WHERE seqs LIKE "blablabla%"
SELECT * FROM sequences WHERE seqs="blablabla"SELECT * FROM sequences WHERE seqs LIKE "blablabla%"
要使这些查询使用索引，我似乎需要两个索引(每种查询类型一个)，例如:
For these queries to use an index I seem to need two indices (one for each query type), as such:
-
CREATE INDEX test_nocol ON sequences(seqs)用于第一个查询. -
CREATE INDEX seqs_index ON sequences(seqs COLLATE NOCASE)用于第二个查询.
CREATE INDEX test_nocol ON sequences(seqs)for the first query.CREATE INDEX seqs_index ON sequences(seqs COLLATE NOCASE)for the second query.
这很好，但是随后我添加了python3的sqlite3模块，并开始在那里查询，该模块适用于原始字符串，但是当我使用参数绑定时，突然不再使用COLLATE索引:
That's all nice, but then I add python3's sqlite3 module, and start querying there instead, which works with raw strings, but when I use parameter bindings the COLLATE index is suddenly no longer used:
>>> sql = 'explain query plan\n select seqs from sequences where seqs="blabla"'
>>> c3.execute(sql).fetchall()
[(0, 0, 0, 'SEARCH TABLE sequences USING COVERING INDEX test_nocol (seqs=?)')]
>>> sql = 'explain query plan\n select seqs from sequences where seqs=?'
>>> c3.execute(sql, ('hahahah',)).fetchall()
[(0, 0, 0, 'SEARCH TABLE sequences USING COVERING INDEX test_nocol (seqs=?)')]
>>> sql = 'explain query plan\n select seqs from sequences where seqs like "hahahah%"'
>>> c3.execute(sql).fetchall()
[(0, 0, 0, 'SEARCH TABLE sequences USING COVERING INDEX seqs_index (seqs>? AND seqs<?)')]
>>> sql = 'explain query plan\n select seqs from sequences where seqs like ?'
>>> c3.execute(sql, ('hahahah',)).fetchall()
[(0, 0, 0, 'SCAN TABLE sequences')]
我在这里做错了什么?由于这是序列化后端，而不是webapp数据库，因此我认为使用原始字符串时的威胁不太严重，但是我宁愿使用正确的SQL格式.
What am I doing wrong here? Since this is a serialization backend and not a webapp DB, I guess the threat when using raw strings is less severe, but I'd much rather use the proper SQL formatting.
推荐答案
文档说:
pysqlite模块的旧版本使用sqlite3_prepare().
Old versions of the pysqlite module use sqlite3_prepare().
这篇关于Python sqlite3不与LIKE一起使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！