标签

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开始，就支持了拼写的纠正字典，参考

通过affix, dict文件进行纠正。

例子

The .affix file of Ispell has the following structure:prefixesflag *A:    .           >   RE      # As in enter > reentersuffixesflag 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 > smallestAnd the .dict file has the following structure:lapse/ADGRSlard/DGRSlarge/PRTYlark/MRS

postgrespro开源了一个插件，实现了一些国家语言的fix , 可以用来处理这类拼写纠正。

Hunspell Dictionaries

git clone https://github.com/postgrespro/hunspell_dictscd hunspell_dictslltotal 28Kdrwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_de_dedrwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_en_usdrwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_frdrwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_nl_nldrwxr-xr-x 5 digoal users 4.0K Dec  6 19:53 hunspell_nn_nodrwxr-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.mdcd hunspell_en_uslltotal 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.controldrwxr-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 Makefiledrwxr-xr-x 2 dege.zzz users 4.0K Dec  6 19:53 sqlmake USE_PGXS=1 install

目前支持的几个字典如下

通过模块安装这些字典

psqlCREATE 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吧。