Python sqlite3不使用索引与LIKE

Question

我有一个只有一列的表，我用两种方式查询：

• SELECT * FROM sequences WHERE seqs="blablabla"
• SELECT * FROM sequences WHERE seqs LIKE "blablabla%"

对于这些使用索引的查询，我似乎需要两个索引（每个查询类型对应一个索引），如下所示：

• CREATE INDEX test_nocol ON sequences(seqs)用于第一个查询。在
• CREATE INDEX seqs_index ON sequences(seqs COLLATE NOCASE)用于第二个查询。在

这很好，但是我添加了python3的sqlite3模块，并开始在那里进行查询，这可以处理原始字符串，但是当我使用参数绑定时，COLLATE索引突然不再使用：

>>> 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')]

我做错什么了？由于这是一个序列化后端，而不是webappdb，我想使用原始字符串时的威胁不那么严重，但我更愿意使用正确的SQL格式。在

Answer 1

documentation上写着：

If the right-hand side is a parameter that is bound to a string, then this optimization is only attempted if the prepared statement containing the expression was compiled with sqlite3_prepare_v2() or sqlite3_prepare16_v2(). The LIKE optimization is not attempted if the right-hand side is a parameter and the statement was prepared using sqlite3_prepare() or sqlite3_prepare16().

pysqlite模块的旧版本使用sqlite3_prepare()。在