Mysql中文汉字转拼音的实现（每个汉字转换全拼）

博主：茶白
发布时间：2023 年 10 月 25 日
9432 次浏览
暂无评论
9030字数
分类： mysql

**一、创建拼音对照表**

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

**二、插入对照数据**

```sql
-- 插入数据
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(网上流传)
  ```sql
  -- 建立汉字转换拼音函数
  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 ;
  ```
- 版本2(版本1不能用我自己修改的)
  ```sql
  -- 建立汉字转换拼音函数
  DROP FUNCTION IF EXISTS to_pinyin;
  DELIMITER $
  CREATE FUNCTION to_pinyin(NAME VARCHAR(255) CHARSET gbk)
  RETURNS VARCHAR(255) CHARSET gbk
  DETERMINISTIC
  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

```

最后修改：2025 年 10 月 14 日

如果觉得我的文章对你有用，请随意赞赏

此处评论已关闭

姓名
GPT-4 currently has a cap of 25...
曲阜
有没有兴趣来我们公司工作？可联系微信geezzi
Rorinl
谢谢ヾ(≧∇≦*)ゝ
rorinl
该评论仅登录用户及评论双方可见

Mysql中文汉字转拼音的实现（每个汉字转换全拼）

茶白 • 2023 年 10 月 25 日

**一、创建拼音对照表**

**二、插入对照数据**

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

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

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

```

Mysql中文汉字转拼音的实现（每个汉字转换全拼）

在线影视视频网站合集

js奇淫技巧（一）

常见的app scheme大全

uniapp图片未加载成功前处理方案,防止闪烁

浏览器console引入jq

uniapp微信小程序radio默认样式修改

CSS中position属性介绍(新增sticky)

Go websocket 跨域

springboot 屏蔽掉返回数据中的某个字段

Python的打包神器—Nuitka！

Mysql中文汉字转拼音的实现（每个汉字转换全拼）