閱讀248 返回首頁    go 阿裏雲 go 技術社區[雲棲]

PostgreSQL漢字轉拚音

標簽

PostgreSQL , 拚音 , 漢字轉拚音

背景

在有些應用中，可能會有對拚音搜索、拚音首字母搜索、中文搜索共存的需求。在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;

最後更新：2017-10-28 23:03:41

  上一篇：go  PostgreSQL 事件觸發器應用 - DDL審計
  下一篇：go  HBase基礎