之所以写这个是因为前端需要进行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('我是中国人');
- 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