汉字转拼音，用户表增加拼音字段，并将汉字姓名对应的拼音赋值给拼音字段

来源：互联网发布：windows 2008ntp服务器编辑：程序博客网时间：2024/05/16 18:26

-- 1.users表里增加姓名拼音列
ALTER TABLE users ADD name_pinyin VARCHAR(64)NULL COMMENT '姓名拼音';

-- 2.创建汉字拼音对照临时表
CREATE TABLE IF NOT EXISTS `t_base_pinyin` (
`pin_yin_` VARCHAR(255) CHARACTER SET gbkNOT NULL,
`code_` INT(11) NOT NULL,
PRIMARY KEY (`code_`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

-- 3.拼音对照临时表插入数据

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

-- 4.建立汉字转换拼音函数
DROP FUNCTION IF EXISTS to_pinyin;
DELIMITER $
CREATE FUNCTION to_pinyin(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)) ASUNSIGNED);
SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2);
SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) ASUNSIGNED);
IF lcode > 128 THEN
SET mycode =65536 - lcode * 256 -rcode ;
SELECT CONCAT(mypy,pin_yin_) INTOmypy FROM t_base_pinyin WHERE CODE_ >= ABS(mycode) ORDER BY CODE_ ASC LIMIT1;
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;
$
DELIMITER ;

-- 5.测试
SELECT to_pinyin('测试')

-- 6.创建存储过程存储对应的拼音字段
DROP PROCEDURE IF EXISTS A;

DELIMITER $
CREATE PROCEDURE A()
BEGIN
DECLARE uid INT; -- id
DECLARE uname VARCHAR(64); -- name
DECLARE id2 INT;
DECLARE name2 VARCHAR(64);
-- 遍历数据结束标志
DECLARE done TINYINT DEFAULT FALSE;
-- 游标
DECLARE cur_account CURSOR FOR SELECT id,NAME FROM users ;
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur_account;
-- 遍历
read_loop:LOOP
-- 取值取多个字段
FETCH NEXT FROM cur_account INTOuid,uname;
IF done THEN
LEAVE read_loop;
END IF;
SELECT id,to_pinyin(NAME) INTOid2,name2 FROM users WHERE id=uid;
-- 你自己想做的操作
UPDATE users SET name_pinyin=name2 WHEREid=id2;
END LOOP;
CLOSE cur_account;
END $

CALL A();

-- 7.执行过程中可能会有中断，或者有的偏僻繁杂的汉字会翻译错误，我的这个就遇到这种情况，生僻字全部翻译成zuo，没办法只好将错误的信息全部导出来手动更改拼音，然后再用公式批量执行update语句，上篇博客写的就是情况。

阅读全文

0 0