MySQL中实现中文转拼音

阅读目录

• 前言：
• 一、创建拼音对照表
• 二、插入对照数据
• 三、创建汉字转换拼音函数
• 四、使用方法

 

---

回到顶部

前言：

今天有个业务需求，需要将用户的密码统一进行设置，现在只有用户的昵称(nickname)，

用户的username跟password字段为空。遂就用到了中文转拼音~

1、先将nickname转拼音并赋值给username字段

2、使用username字段配合加密函数，对该用户的密码进行赋值

?

1 2 3 4

-- 加密-按照用户名进行加密 select  HEX(AES_ENCRYPT('123456','admin')) -- 解密-按照用户名进行解密 SELECT AES_DECRYPT(UNHEX("F8833383029D93ACBD66F6C8D81602DD"),'admin')

回到顶部

一、创建拼音对照表

?

1 2 3 4 5 6

-- 创建汉字拼音对照临时表 CREATE TABLE IF NOT EXISTS `t_base_pinyin` (   `pin_yin_` varchar(255) CHARACTER SET gbk NOT NULL,   `code_` int(11) NOT NULL,   PRIMARY KEY (`code_`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

回到顶部

二、插入对照数据

-- 插入数据
INSERT INTO t_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 
    );

 

回到顶部

三、创建汉字转换拼音函数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

-- 建立汉字转换拼音函数 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)) 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,pin_yin_) INTO mypy FROM t_base_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; $ DELIMITER ;

如果出现错误：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de

 

执行下面SQL，然后重新执行函数即可：

?

1	set global log_bin_trust_function_creators=TRUE;

回到顶部

四、使用方法

?

1	select to_pinyin('测试')