MySQL中实现中文转拼音

前言：

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

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

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

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

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

一、创建拼音对照表

-- 创建汉字拼音对照临时表
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 
    );

View Code

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

-- 建立汉字转换拼音函数
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，然后重新执行函数即可：

set global log_bin_trust_function_creators=TRUE;

四、使用方法

select to_pinyin(\'测试\')

来源：https://www.cnblogs.com/zhangzhixi/p/16567143.html
本站部分图文来源于网络，如有侵权请联系删除。