在utf8字符集下,获取汉字拼音(mysql) (转)

【魄爺】

　　
主题: 在utf8字符集下,获取汉字拼音(mysql)


 
第一步:建立拼音对应表
 
DROP   TABLE   IF   EXISTS   `pyk`;     
  CREATE   TABLE   `pyk`   (     
      `PY`   varchar(1)    ,     
      `HZ1`   int  ,  
      `HZ2`   int        
  ) ;  
      
INSERT   INTO   `pyk`   (`PY`,`HZ1`,`HZ2`)   VALUES       
('A',-20319,-20284),  
('B',-20283,-19776),  
('C',-19775,-19219),  
('D',-19218,-18711),  
('E',-18710,-18527),  
('F',-18526,-18240),  
('G',-18239,-17923),  
('H',-17922,-17418),                 
('J',-17417,-16475),                
('K',-16474,-16213),                
('L',-16212,-15641),                
('M',-15640,-15166),                
('N',-15165,-14923),                
('O',-14922,-14915),                
('P',-14914,-14631),                
('Q',-14630,-14150),                
('R',-14149,-14091),                
('S',-14090,-13319),                
('T',-13318,-12839),                
('W',-12838,-12557),                
('X',-12556,-11848),                
('Y',-11847,-11056),                
('Z',-11055,-10247); 
 
 
第二步:建立函数
DROP FUNCTION IF EXISTS `hzcode`;
CREATE FUNCTION `hzcode` (s CHAR(255)) RETURNS char
BEGIN
                DECLARE hz_code int;
                DECLARE hz_py char;
                declare str varchar(400);
                SET hz_code = ord(convert(substring(s,1,1) using gbk))-65536 ;
                select py into hz_py from pyk where  hz_code>=pyk.hz1 and hz_code<=pyk.hz2;              
                RETURN hz_py;
 
        END;
 
特别注意SET hz_code = ord(convert(substring(s,1,1) using gbk))-65536 ;这句一定要convert函数,网上很多都没有这个函数
结果是在别的字符集下的数据库没有办法得到正确的结果.
 
测试一下
Select hz_code(‘曾’)
 
第三步:建立可以处理字符串的函数
drop function if exists hzcode_str;
create function hzcode_str(s varchar(30)) returns varchar(30)
begin
declare m_len int;
declare i int;
declare m_return varchar(50);
 
set i=1;
set m_len=char_length(s);
set m_return="";
 
while i<=m_len do
         #set m_return=hzcode(s);
         if hzcode(mid(s,i,1)) is not null then
                   set m_return = concat(m_return,hzcode(mid(s,i,1)));
         end if;
         set i=i+1;
end while;
return m_return;
end;
 
测试一下
Select hzcode_str(‘我是测试’);
 
 
结果正确