数据库端拼音码函数

      之所以写这个是因为前端需要进行PinYin码快捷输入，基本行为模式就是在输入框中键入一系列的拼音首字母去与数据库中的某字段的拼音码进行模糊适配，就是这样。

早期版本中的解决方案基本上是，查询过程找那个将目标字典动态生成拼音码，然后再进行适配。但是那个效率呀。。。。

中期版本中的解决方案是添加一个辅助字段“拼音码”，使用函数提前生成拼音码，并在拼音码字段上建立索引，然后进行简单的模糊适配

MySql：

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS t_hz2py;
CREATE TABLE t_hz2py (
  PY char(1) CHARACTER SET utf8 NOT NULL,
  HZ char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (PY)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

INSERT INTO t_hz2py VALUES ('A', '骜');
INSERT INTO t_hz2py VALUES ('B', '簿');
INSERT INTO t_hz2py VALUES ('C', '错');
INSERT INTO t_hz2py VALUES ('D', '鵽');
INSERT INTO t_hz2py VALUES ('E', '樲');
INSERT INTO t_hz2py VALUES ('F', '鳆');
INSERT INTO t_hz2py VALUES ('G', '腂');
INSERT INTO t_hz2py VALUES ('H', '夻');
INSERT INTO t_hz2py VALUES ('J', '攈');
INSERT INTO t_hz2py VALUES ('K', '穒');
INSERT INTO t_hz2py VALUES ('L', '鱳');
INSERT INTO t_hz2py VALUES ('M', '旀');
INSERT INTO t_hz2py VALUES ('N', '桛');
INSERT INTO t_hz2py VALUES ('O', '沤');
INSERT INTO t_hz2py VALUES ('P', '曝');
INSERT INTO t_hz2py VALUES ('Q', '囕');
INSERT INTO t_hz2py VALUES ('R', '鶸');
INSERT INTO t_hz2py VALUES ('S', '蜶');
INSERT INTO t_hz2py VALUES ('T', '箨');
INSERT INTO t_hz2py VALUES ('W', '鹜');
INSERT INTO t_hz2py VALUES ('X', '鑂');
INSERT INTO t_hz2py VALUES ('Y', '韵');
INSERT INTO t_hz2py VALUES ('Z', '咗');



DELIMITER $
DROP FUNCTION IF EXISTS func_getpycodes;
CREATE FUNCTION func_getpycodes( words   varchar(255)) RETURNS varchar(255) CHARSET utf8
BEGIN   
  declare wordspy varchar(255) ;
  declare fpy char(1);   
  declare pc char(1);   
  declare cc char(4);   
  
	set @wordspy='';
	
	while CHAR_LENGTH(words)>0 do
	  set @fpy = UPPER(left(words,1));   
	  set @pc = (CONVERT(@fpy   USING   gbk));   
	  set @cc = hex(@pc);   
	  if @cc >= "8140" and @cc <="FEA0" then  
	    begin   
	      select PY from t_hz2py where hz>=@pc limit 1 into @fpy;
	    end;   
	  end   if;  
	set @wordspy = CONCAT(@wordspy,@fpy);
	set words=right(words,CHAR_LENGTH(words)-1);
	end while;
  Return    @wordspy;  
END

测试脚本：SELECT dbo.func_getpycodes('我是中国人');

SqlServer：

if exists (select 1 from dbo.sysobjects where id=object_id(N'func_getpycodes'))
DROP FUNCTION func_getpycodes; 
go

CREATE   function dbo.func_getpycodes(@str nvarchar(255))
returns nvarchar(255)
as
begin
declare @word nchar(1),@PY nvarchar(255)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T 
where word>=@word collate Chinese_PRC_CS_AS_KS_WS 
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end

GO

测试脚本：SELECT dbo.func_getpycodes('我是中国人');