标签

PostgreSQL , 拼音 , 汉字转拼音

背景

在有些应用中，可能会有对拼音搜索、拼音首字母搜索、中文搜索共存的需求。在PostgreSQL中如何实现这个需求呢？

关键是函数转拼音和首字母，方法很简单，将映射关系存入数据库。创建一个函数来转换。

映射文件请到以上文章的末尾下载。

方法

1、创建映射表

create table pinyin (hz varchar(1), py varchar(6), zm varchar(1));    create index idx_pinyin_hz on pinyin(hz);    create unique index idx_pinyin_hz_py on pinyin(hz, py);

2、写入一些测试映射，仅供演示用。映射文件请到以上文章的末尾下载。

test=# insert into pinyin values ('你','ni','n');  INSERT 0 1  test=# insert into pinyin values ('好','hao','h');  INSERT 0 1

3、将字符串转换为拼音以及单字的函数

create or replace function get_hzpy(vhz text) returns text[] as $$  declare    res text[];    tmp_py text;    tmp_zm text;  begin  for i in 1..length(vhz)   loop    select py,zm into tmp_py,tmp_zm from pinyin where hz=substring(vhz, i, 1);    if not found then      res := array_cat(res, array[substring(vhz, i, 1)]);    else      res := array_cat(res, array[tmp_py, tmp_zm, substring(vhz, i, 1)]);    end if;  end loop;  return res;  end;  $$ language plpgsql strict immutable;

4、测试该函数，输入一个字符串，返回了它的 单字和所有单字的拼音、首字母，没有的话只输出单字。

test=# select get_hzpy('你好abx呵呵, ');                       get_hzpy                       --------------------------------------------------   {ni,n,你,hao,h,好,a,b,x,","," "}  (1 row)

测试索引加速 单字、拼音、首字母 搜索

1、创建一张测试表，包含一个字符串

create table test(id int, info text);

2、创建函数倒排索引

test=# create index idx on test using gin (get_hzpy(info));  CREATE INDEX

3、写入测试数据

test=# insert into test values (1, '你好abx呵呵, ');  INSERT 0 1

4、按 "单字、拼音、首字母" 查询测试，使用倒排索引

test=# explain select * from test where get_hzpy(info) @> array['ni'];   -- 包含ni的记录                              QUERY PLAN                               -------------------------------------------------------------------   Bitmap Heap Scan on test  (cost=4.10..20.92 rows=26 width=36)     Recheck Cond: (get_hzpy(info) @> '{ni}'::text[])     ->  Bitmap Index Scan on idx  (cost=0.00..4.09 rows=26 width=0)           Index Cond: (get_hzpy(info) @> '{ni}'::text[])  (4 rows)

5、字典有的，可以查到，字典没有的，只能通过中文查

test=# select * from test where get_hzpy(info) @> array['ni'];   id |     info        ----+---------------    1 | 你好abx呵呵,   (1 row)    test=# select * from test where get_hzpy(info) @> array['he'];   id | info   ----+------  (0 rows)

6、补齐字典，注意补齐字典并不影响已有的数据，因为只在数据发生变化时才会重算 get_hzpy(info) 的值，并更新索引。

下面这个例子可以清晰的表明这个意思。

test=# insert into pinyin values ('呵','he','h');  INSERT 0 1    test=# select * from test where get_hzpy(info) @> array['he'];   id | info   ----+------  (0 rows)    test=# update test set id=2 where id=1;  UPDATE 1    test=# select * from test where get_hzpy(info) @> array['he'];   id | info   ----+------  (0 rows)

只有更新了info字典，才会更新索引内容。

test=# update test set info='呵呵,xi' where id=2;  UPDATE 1  test=# select * from test where get_hzpy(info) @> array['he'];   id |  info     ----+---------    2 | 呵呵,xi  (1 row)

7、因此，建议字典越全面越好。将来如果字典更新了，需要重建一下索引，方法如下：

create index CONCURRENTLY idx_test_2 on test using gin(get_hzpy(info));  drop index idx;