TAG 15
作者
digoal
日期
2016-12-06
标签
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吧。
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
