如何利用拼音首字母查询数据库

 

这个问题其实挺有点意思的，以前做东西的时候从没想过这方面的问题，但是最近接了一个项目客户指定要求对姓名检索的时候必须能够按拼音首字母进行检索，想想也是，汉字里面发音相同的字那么多，按汉字检索的时候就必须得知道具体用的是那个字，再加上中国有那么多的方言，有时候想搞明白别人的名字究竟用的是那个字确实挺纠结的，而且大家用的汉字输入法还各不相同，拼音、五笔的都有，尤其用拼音的有时候为了找一个字得翻半天，要是能直接用拼音字母查询就没这问题了，所以这应该还算是一个操作方面比较实用的小技巧吧。

 

那么怎么实现按拼音首字母查询呢，要是搁以前，首先想到的肯定是在建库的时候多弄一个字段叫“简码”或者“速查码”之类的用来存放拼音首字母，在输入数据的时候让操作员多录入一点数据，现在见到仍然有很多系统是这么做的，当然这不失为一种简单易行的好办法，唯一的缺点就是操作员得多录入点数据。不过很可惜的是这次的项目要查询的数据是从别人另一个系统共享过来的，而且我们还不能进行修改，所以在单弄个字段显然是不可能的了，那有没有别的办法不加这样的字段也能实现呢，想了想没有什么头绪，没办法跑到CSDN上问了问，别人给提供了一段SQL Server的自定义函数代码

if object_id('[memberinfo]') is not null drop table [memberinfo]
go
create table [memberinfo]([ID] int,[name] varchar(4))
insert [memberinfo]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'贺六'

create function   f_GetPy(@str   nvarchar(4000))
returns   nvarchar(4000)
as
begin
declare   @strlen   int,@re   nvarchar(4000)
declare   @t   table(chr   nchar(1)   collate   Chinese_PRC_CI_AS,letter   nchar(1))
insert   into   @t(chr,letter)
    select   '吖 ', 'A '   union   all   select   '八 ', 'B '   union   all
    select   '嚓 ', 'C '   union   all   select   '咑 ', 'D '   union   all
    select   '妸 ', 'E '   union   all   select   '发 ', 'F '   union   all
    select   '旮 ', 'G '   union   all   select   '铪 ', 'H '   union   all
    select   '丌 ', 'J '   union   all   select   '咔 ', 'K '   union   all
    select   '垃 ', 'L '   union   all   select   '嘸 ', 'M '   union   all
    select   '拏 ', 'N '   union   all   select   '噢 ', 'O '   union   all
    select   '妑 ', 'P '   union   all   select   '七 ', 'Q '   union   all
    select   '呥 ', 'R '   union   all   select   '仨 ', 'S '   union   all
    select   '他 ', 'T '   union   all   select   '屲 ', 'W '   union   all
    select   '夕 ', 'X '   union   all   select   '丫 ', 'Y '   union   all
    select   '帀 ', 'Z '
    select   @strlen=len(@str),@re= ' '
    while   @strlen> 0
    begin
        select   top   1   @re=letter+@re,@strlen=@strlen-1
            from   @t   a   where   chr <=substring(@str,@strlen,1)
            order   by   chr   desc
        if   @@rowcount=0
            select   @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
    end
    return(@re)
end

---查询---
select
  *
from
  [memberinfo]
where
  dbo.f_GetPy(name)='ZS'

试了一下，没问题完全可以实现，不过就是速度嘛太慢鸟，才不到4000条记录查询要4、5秒钟的时间，那要是400万条记录查询一次还不得等到明年去了，没辙还得另想办法。仔细研究一下上面那段代码，发现人家是遍历了memberinfo表里的所有记录，然后通过f_GetPy函数提取出每条记录name字段的拼音首字母然后和输入的的内容比较，相同的就返回，像这样一条一条记录的转换再比较，难怪速度这么慢呢。

 

不过看了人家这段代码倒是给我带来了一些灵感，我们都知道数据库查询里面用Order By关键字对汉字进行排序的时候是按照拼音字母顺序进行排序的，既然是按照拼音顺序排列的那么汉字的大小比较也是按拼音来比较的，通过上面那段程序我们不难发现发音为A的第一个汉字是“吖”，发音为B的第一个汉字为“八”，如果我们要查询name字段拼音首字母以A开头的数据直接用

select * from [memberinfo] where [name]>='吖' and [name]<'八'

同理，上面的“select * from   [memberinfo] where  dbo.f_GetPy(name)='ZS'”改成

select * from [memberinfo] where len([name])>=2 and left([name],1)>='帀' and right(left([name],2),1)>='仨' and right(left([name],2),1)<'他'

查询速度瞬间提升了N倍，爽啊！虽然构造出来的查询语句变得复杂了，但是查询的效率真不是盖的，反正客户只要求支持姓名的拼音首字母查询，鉴于中国人除部分少数民族的名字比较长以外基本都是2-3个汉字的，所以构造出来的查询语句即便在复杂也复杂不到哪去，至于上面的“Z”“S”是怎么变成“帀”和“仨”“他”的我就不多说了，除非你不是搞数据库编程的。有兴趣的童鞋可以把这个改成存储过程或者函数，到时候用起来更方便。