MySQL中实现中文转拼音

南飓风 · 2022-9-16 17:14:34

前言：

今天有个业务需求，需要将用户的密码统一进行设置，现在只有用户的昵称(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('测试')

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

		自动登录	找回密码
密码			立即注册

MySQL中实现中文转拼音

本帖子中包含更多资源

0 个回复

快速回复

楼主热帖

标签云

浏览过的版块