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

-- 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语句，上篇博客写的就是情况。

• 感谢您的阅读。如果感觉文章对您有用，麻烦您动动手指点个赞，以资鼓励。谢谢！

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

-- 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语句，上篇博客写的就是情况。

• 感谢您的阅读。如果感觉文章对您有用，麻烦您动动手指点个赞，以资鼓励。谢谢！