如何在H2数据库中使用全文搜索？

Question

考虑以下示例。

CREATE ALIAS IF NOT EXISTS FT_INIT FOR "org.h2.fulltext.FullText.init";
CALL FT_INIT();
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR);
INSERT INTO TEST VALUES(1, 'Hello World');
CALL FT_CREATE_INDEX('PUBLIC', 'TEST', NULL);

我已经执行了以下查询。

SELECT * FROM FT_SEARCH('Hello', 0, 0);

但是这个查询返回的结果是"PUBLIC"."TEST" WHERE "ID"=1。

1. 我是否需要再次执行"PUBLIC"."TEST" WHERE "ID"=1来获取包含'Hello'单词的记录？
2. 在FT_Search中，搜索所有包含'ell'单词的记录的查询是什么，例如H2本机全文搜索中的like %ell%？

- Vivek

Answer 1

1. Yes, each row in a query using FT_SEARCH represents a schema-table-row where one of the key words was found. The search is case insensitive, and the text parameter to FT_SEARCH may include more than one word. For example,

   DELETE FROM TEST;
   INSERT INTO TEST VALUES(1, 'Hello World');
   INSERT INTO TEST VALUES(2, 'Goodbye World');
   INSERT INTO TEST VALUES(3, 'Hello Goodbye');
   CALL FT_REINDEX();
   SELECT * FROM FT_SEARCH('hello goodbye', 0, 0);
   

   returns only row three:

   QUERY                           SCORE  
   "PUBLIC"."TEST" WHERE "ID"=3    1.0
   

   Also note that FT_SEARCH_DATA may be used to retrieve the data itself. For example,

   SELECT T.* FROM FT_SEARCH_DATA('hello', 0, 0) FT, TEST T
   WHERE FT.TABLE='TEST' AND T.ID=FT.KEYS[0];
   

   returns both rows containing the keyword:

   ID   NAME  
   1    Hello World
   3    Hello Goodbye
   

2. Apache Lucene supports wildcard searches, although leading wildcards (e.g. *ell) tend to be expensive.

- trashgod

---

这正是我想要的。谢谢。 - Vivek

请问有没有办法使用Lucene对H2数据库进行索引，如果有，请告诉我。 - Vivek

使用Lucene全文搜索非常相似，您只需要在类路径中添加Lucene库。 - trashgod

Answer 2

我需要再次执行 "PUBLIC"."TEST" WHERE "ID"=1 才能获取包含“Hello”单词的记录吗？是的，除非您使用像trashgod描述的连接。原因是：通常行比仅有两个单词要大得多。例如，一行包含一个文档的CLOB。如果全文搜索的结果包含数据，则全文搜索将变得更慢。

如何查询所有FT_Search中包含'ell'单词的记录？例如H2本地全文搜索中的like %ell%。本地全文搜索不能直接执行此操作。原因是：全文搜索仅索引整个单词。 （顺便说一句：Google是否支持搜索如果您只知道单词的一部分？Apache Lucene确实支持它）实际上，对于H2，有一种方法：首先，在单词表（FT.WORDS）中搜索匹配项，然后使用常规搜索。

- Thomas Mueller

---

是的，Lucene支持通配符搜索；我已经修复了我的回答中（之前）损坏的链接。在搜索部分匹配时，使用FT.WORDS是个好主意。 - trashgod

实际上，由于常规全文搜索使用AND连接搜索术语，因此仅在单个查询中查找FT.WORDS的结果可能无法正常工作。假设您想搜索“AB％”，则FT.WORDS查询可能会返回“ABCDE”和“ABXYZ”作为候选词。现在，对于“ABCDE ABXYZ”的常规全文搜索将返回交集，最有可能是空集。 要使用FT.WORDS方法，您需要首先扩展您的“AB％”术语，然后为每个扩展调用全文搜索。如果您有多个搜索术语，则必须搜索所有组合。 - Hendrik