<p>公认的答案是好的(完全归功于他),这里有一个小小的变化,虽然没有公认的复杂案例的完整性,但有助于理解以下观点:</p>
<pre><code>import sqlite3
db = sqlite3.connect(':memory:')
db.enable_load_extension(True)
db.load_extension('./spellfix')
c = db.cursor()
c.execute("CREATE VIRTUAL TABLE mytable2 USING fts4(description text)")
c.execute("CREATE VIRTUAL TABLE mytable2_terms USING fts4aux(mytable2)")
c.execute("CREATE VIRTUAL TABLE mytable3 USING spellfix1")
c.execute("INSERT INTO mytable2 VALUES ('All the Carmichael numbers')") # populate the table
c.execute("INSERT INTO mytable2 VALUES ('They are great')")
c.execute("INSERT INTO mytable2 VALUES ('Here some other numbers')")
c.execute("INSERT INTO mytable3(word) SELECT term FROM mytable2_terms WHERE col='*'")
def search(query):
# Correcting each query term with spellfix table
correctedquery = []
for t in query.split():
spellfix_query = "SELECT word FROM mytable3 WHERE word MATCH ? and top=1"
c.execute(spellfix_query, (t,))
r = c.fetchone()
correctedquery.append(r[0] if r is not None else t) # correct the word if any match in the spellfix table; if no match, keep the word spelled as it is (then the search will give no result!)
correctedquery = ' '.join(correctedquery)
# Now do the FTS
fts_query = 'SELECT * FROM mytable2 WHERE description MATCH ?'
c.execute(fts_query, (correctedquery,))
return {'result': c.fetchall(), 'correctedquery': correctedquery, 'query': query}
print(search('NUMBBERS carmickaeel'))
print(search('some HERE'))
print(search('some qsdhiuhsd'))
</code></pre>
<p>结果如下:</p>
<blockquote>
<p>{'query': 'NUMBBERS carmickaeel', 'correctedquery': u'numbers carmichael', 'result': [(u'All the Carmichael numbers',)]}<br/>
{'query': 'some HERE', 'correctedquery': u'some here', 'result': [(u'Here some other numbers',)]}<br/>
{'query': 'some qsdhiuhsd', 'correctedquery': u'some qsdhiuhsd', 'result': []} </p>
</blockquote>
<p>备注:可以注意到,<em>“用spellfix table更正每个查询项”部分是通过每个术语一个SQL查询完成的。研究了这个与单个联合SQL查询的性能<a href="https://stackoverflow.com/questions/52803014/sqlite-with-real-full-text-search-and-spelling-mistakes-ftsspellfix-together/52944559#comment92671346_52860714">here</a>。在</p>