如何解决数据库分词的拼写纠正问题 - PostgreSQL Hunspell 字典 复数形容词动词等变异还原
背景
在英语中，名词通常都有复数，表示多个；形容词，过去式，动词等。 有large, larger, largest, stories, eating, did, doing, hacked这样的。
这可能会给分词带来一定的困扰，例如我们来看看PG默认的ts config怎么处理它的。
比如english tsconfig是这么处理的
postgres=# SELECT * FROM ts_debug('english', 'larger');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+--------+----------------+--------------+----------
asciiword | Word, all ASCII | larger | {english_stem} | english_stem | {larger}
(1 row)
postgres=# SELECT * FROM ts_debug('english', 'large');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+-------+----------------+--------------+---------
asciiword | Word, all ASCII | large | {english_stem} | english_stem | {larg}
(1 row)
postgres=# SELECT * FROM ts_debug('english', 'largest');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+---------+----------------+--------------+-----------
asciiword | Word, all ASCII | largest | {english_stem} | english_stem | {largest}
(1 row)
postgres=# SELECT * FROM ts_debug('english', 'stories');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+---------+----------------+--------------+---------
asciiword | Word, all ASCII | stories | {english_stem} | english_stem | {stori}
(1 row)
很显然，它没有很好的处理这几个词, large, larger, largest, stories。
默认的parser支持的token类型
postgres=# select * from ts_token_type('default');
tokid | alias | description
-------+-----------------+------------------------------------------
1 | asciiword | Word, all ASCII
2 | word | Word, all letters
3 | numword | Word, letters and digits
4 | email | Email address
5 | url | URL
6 | host | Host
7 | sfloat | Scientific notation
8 | version | Version number
9 | hword_numpart | Hyphenated word part, letters and digits
10 | hword_part | Hyphenated word part, all letters
11 | hword_asciipart | Hyphenated word part, all ASCII
12 | blank | Space symbols
13 | tag | XML tag
14 | protocol | Protocol head
15 | numhword | Hyphenated word, letters and digits
16 | asciihword | Hyphenated word, all ASCII
17 | hword | Hyphenated word, all letters
18 | url_path | URL path
19 | file | File or path name
20 | float | Decimal notation
21 | int | Signed integer
22 | uint | Unsigned integer
23 | entity | XML entity
(23 rows)
实际上从PostgreSQL 9.6开始，就支持了拼写的纠正字典，参考
https://www.postgresql.org/docs/9.6/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY
通过affix, dict文件进行纠正。
例子
The .affix file of Ispell has the following structure:
prefixes
flag *A:
. > RE # As in enter > reenter
suffixes
flag T:
E > ST # As in late > latest
[^AEIOU]Y > -Y,IEST # As in dirty > dirtiest
[AEIOU]Y > EST # As in gray > grayest
[^EY] > EST # As in small > smallest
And the .dict file has the following structure:
lapse/ADGRS
lard/DGRS
large/PRTY
lark/MRS
postgrespro开源了一个插件，实现了一些国家语言的fix , 可以用来处理这类拼写纠正。
Hunspell Dictionaries
https://github.com/postgrespro/hunspell_dicts
git clone https://github.com/postgrespro/hunspell_dicts
cd hunspell_dicts
ll
total 28K
drwxr-xr-x 5 digoal users 4.0K Dec 6 19:53 hunspell_de_de
drwxr-xr-x 5 digoal users 4.0K Dec 6 19:53 hunspell_en_us
drwxr-xr-x 5 digoal users 4.0K Dec 6 19:53 hunspell_fr
drwxr-xr-x 5 digoal users 4.0K Dec 6 19:53 hunspell_nl_nl
drwxr-xr-x 5 digoal users 4.0K Dec 6 19:53 hunspell_nn_no
drwxr-xr-x 5 digoal users 4.0K Dec 6 19:53 hunspell_ru_ru
-rw-r--r-- 1 digoal users 1.3K Dec 6 19:53 README.md
cd hunspell_en_us
ll
total 560K
-rw-r--r-- 1 digoal users 3.1K Dec 6 19:53 en_us.affix -- 纠正拼写的语法
-rw-r--r-- 1 digoal users 531K Dec 6 19:53 en_us.dict -- 纠正字典
drwxr-xr-x 2 digoal users 4.0K Dec 6 19:53 expected
-rw-r--r-- 1 digoal users 804 Dec 6 19:53 hunspell_en_us--1.0.sql
-rw-r--r-- 1 digoal users 150 Dec 6 19:53 hunspell_en_us.control
drwxr-xr-x 2 digoal users 4.0K Dec 6 19:53 license
-rw-r--r-- 1 digoal users 370 Dec 6 19:53 Makefile
drwxr-xr-x 2 digoal users 4.0K Dec 6 19:53 sql
make USE_PGXS=1 install
目前支持的几个字典如下
| Module | Dictionary | Configuration |
|---|---|---|
| hunspell_de_de | german_hunspell | german_hunspell |
| hunspell_en_us | english_hunspell | english_hunspell |
| hunspell_fr | french_hunspell | french_hunspell |
| hunspell_nl_nl | dutch_hunspell | dutch_hunspell |
| hunspell_nn_no | norwegian_hunspell | norwegian_hunspell |
| hunspell_ru_ru | russian_hunspell | russian_hunspell |
通过模块安装这些字典
psql
CREATE EXTENSION hunspell_en_us;
postgres=# select * from pg_ts_config;
cfgname | cfgnamespace | cfgowner | cfgparser
------------------+--------------+----------+-----------
simple | 11 | 10 | 3722
danish | 11 | 10 | 3722
dutch | 11 | 10 | 3722
english | 11 | 10 | 3722
finnish | 11 | 10 | 3722
french | 11 | 10 | 3722
german | 11 | 10 | 3722
hungarian | 11 | 10 | 3722
italian | 11 | 10 | 3722
norwegian | 11 | 10 | 3722
portuguese | 11 | 10 | 3722
romanian | 11 | 10 | 3722
russian | 11 | 10 | 3722
spanish | 11 | 10 | 3722
swedish | 11 | 10 | 3722
turkish | 11 | 10 | 3722
english_hunspell | 2200 | 10 | 3722 -- 新增
(17 rows)
解决复数，形容词问题
postgres=# SELECT * FROM ts_debug('english_hunspell', 'stories');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+---------+---------------------------------+------------------+---------
asciiword | Word, all ASCII | stories | {english_hunspell,english_stem} | english_hunspell | {story}
(1 row)
postgres=# SELECT * FROM ts_debug('english_hunspell', 'large');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+-------+---------------------------------+------------------+---------
asciiword | Word, all ASCII | large | {english_hunspell,english_stem} | english_hunspell | {large}
(1 row)
postgres=# SELECT * FROM ts_debug('english_hunspell', 'larger');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+--------+---------------------------------+------------------+---------
asciiword | Word, all ASCII | larger | {english_hunspell,english_stem} | english_hunspell | {large}
(1 row)
postgres=# SELECT * FROM ts_debug('english_hunspell', 'largest');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+---------+---------------------------------+------------------+---------
asciiword | Word, all ASCII | largest | {english_hunspell,english_stem} | english_hunspell | {large}
(1 row)
一个小的插件，反映的是PostgreSQL社区生态，以及PG社区圈子热衷贡献的精神。还有很多很多这样的例子，在程序实现要花不少时间的问题，可能在PG圈就能找到插件帮你解决。快来用PG吧。