基于postgres的应用软件的拼音搜索实现

最新推荐文章于 2023-08-23 11:37:19 发布

zeeeitch

最新推荐文章于 2023-08-23 11:37:19 发布

阅读量1.4k

点赞数

分类专栏： database 文章标签： character integer function 正则表达式 null 数据库

本文链接：https://blog.csdn.net/zeeeitch/article/details/6087281

版权

database 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

在做软件时，实现拼音搜索可以极大方便用户，提升用户体验。这里的实现采用的是基于汉字拼音数据表，结合触发器、正则表达式实现的拼音搜索。

拼音数据表准备（网上可以下到，我用的是6000多字的）：

postgres=# select * from s_hz; hzbm | hz | hzpy | hzbh | hzbsbh | hzwz ------+----+--------+------+--------+------ | 啊 | a | | | | 阿 | a | | | | 埃 | ai | | | | 挨 | ai | | | | 哎 | ai | | | | 唉 | ai | | | | 哀 | ai | | |

写一函数，输入汉字，输出拼音，如“耗材”，返回“hao_cai”，下划线为了便于兼容全拼搜索和首字母搜索：

CREATE FUNCTION f_pinyin(as_inputstring character varying) RETURNS character varying LANGUAGE plpgsql AS $_$declare as_InputString ALIAS FOR $1; i integer; ls_ch varchar; ls_py varchar; ls_returnStr varchar; begin i:=1; select c_py into ls_returnStr from t_correct_py where c_station =as_InputString; if found then return ls_returnStr; end if; ls_returnStr:=''; loop if i>Length(as_InputString) then exit; end if; ls_ch:=substr(as_InputString , i ,1); select hzpy into ls_py from s_hz where hz=ls_ch; if ls_py is null then ls_py =''; end if; if i=1 then ls_py:=lower(ls_py); else ls_py:='_'||lower(ls_py); end if; ls_returnStr := ls_returnStr||ls_py; i:=i+1; end loop; RETURN ls_returnStr; end;$_$;

触发器，对需要进行拼音搜索的字段，在插入数据库时，获得拼音，写入数据表。插入时生成拼音可避免搜索时的时间花费：

CREATE FUNCTION py_in() RETURNS trigger LANGUAGE plpgsql AS $DECLARE BEGIN if NEW.z_comment is not null and trim(NEW.z_comment)<>'' then NEW.z_py := f_pinyin(NEW.z_comment); end if; RETURN NEW; END;$;

模糊匹配函数，如“耗材”保存的拼音是hao_cai，可用hc、haoc、hcai都可以搜索到，而hai不会搜索到：

CREATE FUNCTION py_match(as_inputstring character varying, as_py character varying, as_pinyin character varying) RETURNS boolean LANGUAGE plpgsql AS $declare i integer; c integer; ls_ch varchar; ls_RegStr varchar; begin --if trim(as_py) = trim(as_inputstring) then -- return true; --end if; i:=1; ls_RegStr:=''; loop if i>Length(as_InputString) then exit; end if; ls_ch:=substr(as_InputString , i ,1); if i=1 then ls_ch:=lower(ls_ch); else ls_ch:='(?:.*_)?'||lower(ls_ch); end if; ls_RegStr :=ls_RegStr|| ls_ch; i:=i+1; end loop; return as_pinyin~ls_RegStr; end;$;

最终，在用户输入拼音后，生成过滤sql：

where ... and py_match(用户输入的拼音,'',预存储的汉字对应的拼音字段)

以上方法可用于C/S或Web程序。