如何使用FTS3 SQLite中

问题描述

我有表有近20万项。当我试图搜索与LIKE，这是非常缓慢的。现在，我决定使用FTS。因此，我创建在哪里搜索将举行两项指标。然后，我创建了FTS虚拟表。

I have table with almost 200k entries. When I tried search with LIKE, it was very slow. Now I decided to use FTS. So I created two indexes where search will be held. Then I created fts virtual table.

`CREATE TABLE [search_eng] (
  [id] INTEGER PRIMARY KEY AUTOINCREMENT, 
  [entry_id] INTEGER, 
  [re_value] TEXT, 
  [ke_value] TEXT, 
  [g_value] TEXT);

CREATE INDEX idx_se_re ON search_eng (re_value);

CREATE INDEX idx_se_gv ON search_eng (g_value);


CREATE VIRTUAL TABLE search_eng_fts USING fts3(id, entry_id, re_value, ke_value, g_value);`

我不知道如何使用新创建FTS表。所以我的问题是如何使用该虚拟表进行搜索？你能举个例子吗？

I have no idea how to use new created FTS table. So my questions is how to use that virtual table to make search? Can you give an example?

推荐答案

这是在的文档。

您不需要两个指标为FTS搜索。

You do not need the two indexes for FTS searches.

您应该声明 ID 列 INTEGER PRIMARY KEY 。你可能不需要 entry_id 列在FST表。

You should declare the id column as INTEGER PRIMARY KEY.You probably don't need the entry_id column in the FST table.

复制文本到FTS表：

INSERT INTO search_eng_fts(id, re_value, ke_value, g_value)
SELECT id, re_value, ke_value, g_value FROM search_eng;

然后就可以使用 MATCH 运营商在该表进行搜索：

Then you can use the MATCH operator to search in that table:

SELECT id FROM search_eng_fts WHERE re_value MATCH 'hello';

这篇关于如何使用FTS3 SQLite中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！