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/MRSpostgrespro开源了一个插件，实现了一些国家语言的fix , 可以用来处理这类拼写纠正。
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 dege.zzz users 3.1K Dec 6 19:53 en_us.affix -- 纠正拼写的语法 -rw-r--r-- 1 dege.zzz users 531K Dec 6 19:53 en_us.dict -- 纠正字典 drwxr-xr-x 2 dege.zzz users 4.0K Dec 6 19:53 expected -rw-r--r-- 1 dege.zzz users 804 Dec 6 19:53 hunspell_en_us--1.0.sql -rw-r--r-- 1 dege.zzz users 150 Dec 6 19:53 hunspell_en_us.control drwxr-xr-x 2 dege.zzz users 4.0K Dec 6 19:53 license -rw-r--r-- 1 dege.zzz users 370 Dec 6 19:53 Makefile drwxr-xr-x 2 dege.zzz users 4.0K Dec 6 19:53 sql make USE_PGXS=1 install目前支持的几个字典如下
ModuleDictionaryConfigurationhunspell_de_degerman_hunspellgerman_hunspellhunspell_en_usenglish_hunspellenglish_hunspellhunspell_frfrench_hunspellfrench_hunspellhunspell_nl_nldutch_hunspelldutch_hunspellhunspell_nn_nonorwegian_hunspellnorwegian_hunspellhunspell_ru_rurussian_hunspellrussian_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吧。
