mysql 存储过程 汉字取拼音或者首字母

mysql 的存储过程。我更改了 字节码，取重庆的重为“congqing”，而不是“zhongqing”

首先导入数据库表c_pinyin

语句为：

CREATE TABLE `c_pinyin` (
  `code` int(5) NOT NULL DEFAULT '0' COMMENT '代码',
  `pinyin` varchar(10) DEFAULT NULL COMMENT '拼音',
  `first` varchar(1) DEFAULT NULL COMMENT '首字母',
  PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='拼音';


/*!40000 ALTER TABLE `c_pinyin` DISABLE KEYS */;
INSERT INTO `c_pinyin` VALUES (10254,'zuo','z'),(10256,'zun','z'),(10260,'zui','z'),(10262,'zuan','z'),(10270,'zu','z'),(10274,'zou','z'),(10281,'zong','z'),(10296,'zi','z'),(10307,'zhuo','z'),(10309,'zhun','z'),(10315,'zhui','z'),(10322,'zhuang','z'),(10328,'zhuan','z'),(10329,'zhuai','z'),(10331,'zhua','z'),(10519,'zhu','z'),(10533,'zhou','z'),(10544,'cong','c'),(10587,'zhi','z'),(10764,'zheng','z'),(10780,'zhen','z'),(10790,'zhe','z'),(10800,'zhao','z'),(10815,'zhang','z'),(10832,'zhan','z'),(10838,'zhai','z'),(11014,'zha','z'),(11018,'zeng','z'),(11019,'zen','z'),(11020,'zei','z'),(11024,'ze','z'),(11038,'zao','z'),(11041,'zang','z'),(11045,'zan','z'),(11052,'zai','z'),(11055,'za','z'),(11067,'yun','y'),(11077,'yue','y'),(11097,'yuan','y'),(11303,'yu','y'),(11324,'you','y'),(11339,'yong','y'),(11340,'yo','y'),(11358,'ying','y'),(11536,'yin','y'),(11589,'yi','y'),(11604,'ye','y'),(11781,'yao','y'),(11798,'yang','y'),(11831,'yan','y'),(11847,'ya','y'),(11861,'xun','x'),(11867,'xue','x'),(12039,'xuan','x'),(12058,'xu','x'),(12067,'xiu','x'),(12074,'xiong','x'),(12089,'xing','x'),(12099,'xin','x'),(12120,'xie','x'),(12300,'xiao','x'),(12320,'xiang','x'),(12346,'xian','x'),(12359,'xia','x'),(12556,'xi','x'),(12585,'wu','w'),(12594,'wo','w'),(12597,'weng','w'),(12607,'wen','w'),(12802,'wei','w'),(12812,'wang','w'),(12829,'wan','w'),(12831,'wai','w'),(12838,'wa','w'),(12849,'tuo','t'),(12852,'tun','t'),(12858,'tui','t'),(12860,'tuan','t'),(12871,'tu','t'),(12875,'tou','t'),(12888,'tong','t'),(13060,'ting','t'),(13063,'tie','t'),(13068,'tiao','t'),(13076,'tian','t'),(13091,'ti','t'),(13095,'teng','t'),(13096,'te','t'),(13107,'tao','t'),(13120,'tang','t'),(13138,'tan','t'),(13147,'tai','t'),(13318,'ta','t'),(13326,'suo','s'),(13329,'sun','s'),(13340,'sui','s'),(13343,'suan','s'),(13356,'su','s'),(13359,'sou','s'),(13367,'song','s'),(13383,'si','s'),(13387,'shuo','s'),(13391,'shun','s'),(13395,'shui','s'),(13398,'shuang','s'),(13400,'shuan','s'),(13404,'shuai','s'),(13406,'shua','s'),(13601,'shu','s'),(13611,'shou','s'),(13658,'shi','s'),(13831,'sheng','s'),(13847,'shen','s'),(13859,'she','s'),(13870,'shao','s'),(13878,'shang','s'),(13894,'shan','s'),(13896,'shai','s'),(13905,'sha','s'),(13906,'seng','s'),(13907,'sen','s'),(13910,'se','s'),(13914,'sao','s'),(13917,'sang','s'),(14083,'san','s'),(14087,'sai','s'),(14090,'sa','s'),(14092,'ruo','r'),(14094,'run','r'),(14097,'rui','r'),(14099,'ruan','r'),(14109,'ru','r'),(14112,'rou','r'),(14122,'rong','r'),(14123,'ri','r'),(14125,'reng','r'),(14135,'ren','r'),(14137,'re','r'),(14140,'rao','r'),(14145,'rang','r'),(14149,'ran','r'),(14151,'qun','q'),(14159,'que','q'),(14170,'quan','q'),(14345,'qu','q'),(14353,'qiu','q'),(14355,'qiong','q'),(14368,'qing','q'),(14379,'qin','q'),(14384,'qie','q'),(14399,'qiao','q'),(14407,'qiang','q'),(14429,'qian','q'),(14594,'qia','q'),(14630,'qi','q'),(14645,'pu','p'),(14654,'po','p'),(14663,'ping','p'),(14668,'pin','p'),(14670,'pie','p'),(14674,'piao','p'),(14678,'pian','p'),(14857,'pi','p'),(14871,'peng','p'),(14873,'pen','p'),(14882,'pei','p'),(14889,'pao','p'),(14894,'pang','p'),(14902,'pan','p'),(14908,'pai','p'),(14914,'pa','p'),(14921,'ou','o'),(14922,'o','o'),(14926,'nuo','n'),(14928,'nue','n'),(14929,'nuan','n'),(14930,'nv','n'),(14933,'nu','n'),(14937,'nong','n'),(14941,'niu','n'),(15109,'ning','n'),(15110,'nin','n'),(15117,'nie','n'),(15119,'niao','n'),(15121,'niang','n'),(15128,'nian','n'),(15139,'ni','n'),(15140,'neng','n'),(15141,'nen','n'),(15143,'nei','n'),(15144,'ne','n'),(15149,'nao','n'),(15150,'nang','n'),(15153,'nan','n'),(15158,'nai','n'),(15165,'na','n'),(15180,'mu','m'),(15183,'mou','m'),(15362,'mo','m'),(15363,'miu','m'),(15369,'ming','m'),(15375,'min','m'),(15377,'mie','m'),(15385,'miao','m'),(15394,'mian','m'),(15408,'mi','m'),(15416,'meng','m'),(15419,'men','m'),(15435,'mei','m'),(15436,'me','m'),(15448,'mao','m'),(15454,'mang','m'),(15625,'man','m'),(15631,'mai','m'),(15640,'ma','m'),(15652,'luo','l'),(15659,'lun','l'),(15661,'lue','l'),(15667,'luan','l'),(15681,'lv','l'),(15701,'lu','l'),(15707,'lou','l'),(15878,'long','l'),(15889,'liu','l'),(15903,'ling','l'),(15915,'lin','l'),(15920,'lie','l'),(15933,'liao','l'),(15944,'liang','l'),(15958,'lian','l'),(15959,'lia','l'),(16155,'li','l'),(16158,'leng','l'),(16169,'lei','l'),(16171,'le','l'),(16180,'lao','l'),(16187,'lang','l'),(16202,'lan','l'),(16205,'lai','l'),(16212,'la','l'),(16216,'kuo','k'),(16220,'kun','k'),(16393,'kui','k'),(16401,'kuang','k'),(16403,'kuan','k'),(16407,'kuai','k'),(16412,'kua','k'),(16419,'ku','k'),(16423,'kou','k'),(16427,'kong','k'),(16429,'keng','k'),(16433,'ken','k'),(16448,'ke','k'),(16452,'kao','k'),(16459,'kang','k'),(16465,'kan','k'),(16470,'kai','k'),(16474,'ka','k'),(16647,'jun','j'),(16657,'jue','j'),(16664,'juan','j'),(16689,'ju','j'),(16706,'jiu','j'),(16708,'jiong','j'),(16733,'jing','j'),(16915,'jin','j'),(16942,'jie','j'),(16970,'jiao','j'),(16983,'jiang','j'),(17185,'jian','j'),(17202,'jia','j'),(17417,'ji','j'),(17427,'huo','h'),(17433,'hun','h'),(17454,'hui','h'),(17468,'huang','h'),(17482,'huan','h'),(17487,'huai','h'),(17496,'hua','h'),(17676,'hu','h'),(17683,'hou','h'),(17692,'hong','h'),(17697,'heng','h'),(17701,'hen','h'),(17703,'hei','h'),(17721,'he','h'),(17730,'hao','h'),(17733,'hang','h'),(17752,'han','h'),(17759,'hai','h'),(17922,'ha','h'),(17928,'guo','g'),(17931,'gun','g'),(17947,'gui','g'),(17950,'guang','g'),(17961,'guan','g'),(17964,'guai','g'),(17970,'gua','g'),(17988,'gu','g'),(17997,'gou','g'),(18012,'gong','g'),(18181,'geng','g'),(18183,'gen','g'),(18184,'gei','g'),(18201,'ge','g'),(18211,'gao','g'),(18220,'gang','g'),(18231,'gan','g'),(18237,'gai','g'),(18239,'ga','g'),(18446,'fu','f'),(18447,'fou','f'),(18448,'fo','f'),(18463,'feng','f'),(18478,'fen','f'),(18490,'fei','f'),(18501,'fang','f'),(18518,'fan','f'),(18526,'fa','f'),(18696,'er','e'),(18697,'en','e'),(18710,'e','e'),(18722,'duo','d'),(18731,'dun','d'),(18735,'dui','d'),(18741,'duan','d'),(18756,'du','d'),(18763,'dou','d'),(18773,'dong','d'),(18774,'diu','d'),(18783,'ding','d'),(18952,'die','d'),(18961,'diao','d'),(18977,'dian','d'),(18996,'di','d'),(19003,'deng','d'),(19006,'de','d'),(19018,'dao','d'),(19023,'dang','d'),(19038,'dan','d'),(19212,'dai','d'),(19218,'da','d'),(19224,'cuo','c'),(19227,'cun','c'),(19235,'cui','c'),(19238,'cuan','c'),(19242,'cu','c'),(19243,'cou','c'),(19249,'cong','c'),(19261,'ci','c'),(19263,'chuo','c'),(19270,'chun','c'),(19275,'chui','c'),(19281,'chuang','c'),(19288,'chuan','c'),(19289,'chuai','c'),(19467,'chu','c'),(19479,'chou','c'),(19484,'chong','c'),(19500,'chi','c'),(19515,'cheng','c'),(19525,'chen','c'),(19531,'che','c'),(19540,'chao','c'),(19715,'chang','c'),(19725,'chan','c'),(19728,'chai','c'),(19739,'cha','c'),(19741,'ceng','c'),(19746,'ce','c'),(19751,'cao','c'),(19756,'cang','c'),(19763,'can','c'),(19774,'cai','c'),(19775,'ca','c'),(19784,'bu','b'),(19805,'bo','b'),(19976,'bing','b'),(19982,'bin','b'),(19986,'bie','b'),(19990,'biao','b'),(20002,'bian','b'),(20026,'bi','b'),(20032,'beng','b'),(20036,'ben','b'),(20051,'bei','b'),(20230,'bao','b'),(20242,'bang','b'),(20257,'ban','b'),(20265,'bai','b'),(20283,'ba','b'),(20292,'ao','a'),(20295,'ang','a'),(20304,'an','a'),(20317,'ai','a'),(20319,'a','a');

存储过程为：

CREATE DEFINER=`数据库名`@`%` FUNCTION `to_pya`(NAME VARCHAR(255) CHARSET gbk) RETURNS varchar(255) CHARSET gbk
BEGIN
    DECLARE mycode INT;
    DECLARE tmp_lcode VARCHAR(2) CHARSET gbk;
    DECLARE lcode INT;
    DECLARE tmp_rcode VARCHAR(2) CHARSET gbk;
    DECLARE rcode INT;
    DECLARE mypy VARCHAR(255) CHARSET gbk DEFAULT '';
    DECLARE lp INT;
    SET mycode = 0;
    SET lp = 1;
    SET NAME = HEX(NAME);
    WHILE lp < LENGTH(NAME) DO
        SET tmp_lcode = SUBSTRING(NAME, lp, 2);
        SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED); 
        SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);
        SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED); 
        IF lcode > 128 THEN
            SET mycode =65536 - lcode * 256 - rcode ;
            SELECT CONCAT(mypy,pinyin) INTO mypy FROM c_pinyin WHERE code >= ABS(mycode) ORDER BY code ASC LIMIT 1;
            SET lp = lp + 4;
        ELSE
            SET mypy = CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED)));
            SET lp = lp + 2;
        END IF;
    END WHILE;
    RETURN LOWER(mypy);
END;

赏 支付宝打赏 微信打赏

如果文章或资源对您有帮助，欢迎打赏作者。一路走来，感谢有您！