sql - 复合词全文检索
问题描述
我正在研究 PostgreSQL 全文搜索,并想知道是否可以搜索复合词的第二部分。
当我搜索“蛋糕”时,有没有办法得到“芝士蛋糕”?
-- Lets have a table like this:
CREATE TABLE IF NOT EXISTS table1(
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
col1 TEXT,
col1_tsv TSVECTOR
);
CREATE INDEX IF NOT EXISTS col1_index ON table1 USING gin(col1_tsv);
-- Insert some data into it:
INSERT INTO table1 (col1, col1_tsv)
VALUES ('Blacklist', TO_TSVECTOR('Blacklist')),('Cheesecake', TO_TSVECTOR('Cheesecake'));
如果我搜索“蛋糕”或“列表”,我没有得到任何结果。
SELECT col1 FROM table1 WHERE col1_tsv @@ to_tsquery('english', 'list');
SELECT col1 FROM table1 WHERE col1_tsv @@ to_tsquery('english', 'cake');
用 ts_lexize 检查它:
select ts_lexize('english_stem','Blacklist');
select ts_lexize('english_stem','Cheesecake');
输出:
ts_lexize
-------------
{blacklist}
(1 row)
ts_lexize
-------------
{cheesecak}
(1 row)
按设计工作,但有没有办法仅仅通过搜索蛋糕来获得芝士蛋糕?(我不是这个意思)
select * from table1 where col1 like '%cake%';
当我选择整张桌子时,芝士蛋糕也被切成芝士蛋糕。
select * from table1;
id | col1 | col1_tsv
----+------------+---------------
1 | Blacklist | 'blacklist':1
2 | Cheesecake | 'cheesecak':1
解决方案
对于这种情况,有一个解决方案:您需要一个Hunspell字典,用于您想要支持的语言。这些词典还必须定义复合词规则。如果满足这些要求,Postgres 可以将复合词分解为它们的组件并索引它们,以便它们变得可查找。
我在这里展示了一个德语的例子,其中使用了许多复合词:
首先,我们需要一个合适的带有复合词规则的Hunspell词典。经过一段时间的研究,我找到了一个:https ://github.com/vpikulik/hunspell_de_compounds 。您可以看到,如果 Hunspell 字典的
*.aiff
文件包含类似compoundwords controlled _
.将文件扩展名重命名为
*.affix
and*.dict
。Postgres 期望它是这样的。Postgres 希望 Hunspell 字典是 UTF8 编码的。因此,我使用Sublime Text、call 、为这两个文件打开
*.affix
和文件。*.dict
File
Save with Encoding
UTF-8
您需要将这两个文件复制到数据库机器(或容器等)。在数据库机器上打开一个终端,并将文件移动到正确的位置:
destination=$(echo $(pg_config --sharedir)/tsearch_data) mv de_DE.affix $destination mv de_DE.dict $destination
在这里,
pg_config --sharedir
产生 Postgres 安装的共享目录。字典的目的地是tsearch_data
子目录。连接到您的数据库(本地或远程),例如通过
psql
命令在本地连接。现在,我们在 Postgres 中创建 (a) 我们自己的文本搜索字典和 (b) 我们自己的文本搜索配置。我们两者都叫
german_hunspell
。这是代码:我们删除了之前创建的配置 + 字典。以防万一,我们想重复这个过程,例如因为我们想使用另一个字典。
DROP TEXT SEARCH DICTIONARY german_hunspell CASCADE;
我们创建字典:
CREATE TEXT SEARCH DICTIONARY german_hunspell (TEMPLATE = ispell, DictFile = de_DE, AffFile = de_DE, Stopwords = german);
在这里,
DictFile = de_DE
Postgres 需要一个文件de_DE.dict
;对于AffFile = de_DE
Postgres 需要一个文件de_DE.affix
。german
我们通过从Postgres 中提供的配置派生来创建一个新的文本搜索配置:CREATE TEXT SEARCH CONFIGURATION german_hunspell (COPY = german);
接下来,我们修改我们之前创建的配置。我们定义 Postgres 应该对各种单词使用我们的新配置。如果我们的 Hunspell 词典没有针对特定单词的任何规则,我们会将请求转发到德语的默认词干分析器:
ALTER TEXT SEARCH CONFIGURATION german_hunspell ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, hword_part WITH german_hunspell, german_stem;
完毕。我们可以使用以下
ts_debug
命令测试它是否有效:SELECT * FROM ts_debug('german_hunspell', 'Wettersystemsimulationssoftware');
这里,
Wettersystemsimulationssoftware
是一个德语复合词。它被分成wetter
,system
,simulation
,software
。例如,当用户搜索 时system
,Postgres 会找到这个条目。为了使用我们的配置,您必须为任何
to_tsvector
、to_tsquery
、websearch_to_tsquery
等命令指定它。这里有一些例子:SELECT to_tsvector('german_hunspell', 'content goes here'); SELECT to_tsquery('german_hunspell', 'query goes here'); SELECT websearch_to_tsquery('german_hunspell', 'query goes here'); ...
它也适用于大多数(任何?)语言,例如使用 C#,只要驱动程序允许您指定要使用的配置。一些驱动程序,如 C# 驱动程序,使用二进制接口与数据库通信。在这种情况下,您不能通过名称来寻址配置,例如
german_hunspell
。相反,您必须像这样查询它的 OID:SELECT oid from pg_catalog.pg_ts_config where cfgname = 'german_hunspell';
然后,您可以缓存此 OID 并使用它。
您可以使用任何您想要的语言重复此过程。不幸的是,没有适用于所有可用语言的 Hunspell 词典。我想要一本英语词典。假设我的记录包含单词Spaceship
,那么我希望用户能够搜索Ship
并找到该记录。不幸的是,经过几个小时的研究,我还没有找到合适的英语词典。这是开源社区可以活跃起来的地方......
推荐阅读
- react-native - 未定义,null 不是反应原生获取位置的对象错误
- java - restservices 使用 JWT 密钥登录不起作用
- javascript - 没有初始组件/渲染的 Vue 3 运行时包
- javascript - 如何在 Jest 中期待空字符串?
- reactjs - 为什么将观察者添加到 react-query 会导致它忽略其“启用”属性?
- salesforce - Salesforce Sandbox 播种工具是否通过一些点击将(目标)数据传播到较低的环境真的很有帮助
- reactjs - 升级到 4.7.0 后的 Kendo React Dropdowns 问题
- sql - 我应该在外键上为选择创建索引吗?
- regex - 为什么我的正则表达式显示为一个空字符串?
- flutter - Flutter:在类中保存变量以在不同的小部件中显示它