在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(‘我是测试’);

 

 

结果正确