mysql8 汉字转拼音函数 sql中文转拼音

近期的一个比对项目需要用到汉字到拼音的转换，网上也提供了一些方法。基本上都是查表的方式，不过没有什么现成的高效解决方案。自己动手丰衣足食，仅以此文献给遇到此问题的喷友们。

我的做法原理很简单，代码也不多，效率还过得去。方法如下：找个拼音表，分解查询，合并结果。

先说第一步：这个比较简单，网上有位仁兄提供了下载，好像是重SQLSERVER里弄出来的2.6万多字，包含音标，算是比较全了。具体地址我给忘了，这位仁兄的大名也没记住。人都说吃水不忘挖井人虽然忘了一切，但有一点我记住了，这些编码不是我弄的，嘿嘿。在这里鸣谢一把。

第一步：建个拼音查询表，使用汉字字段弄个索引。

拼音码表我传这里了，。TAB分割PL/SQL里可直接粘贴插入

-- Create table
create table SITE_PY
(
  HZ   NVARCHAR2(1),
  PY   NVARCHAR2(20),
  SM   NVARCHAR2(20),
  YM   NVARCHAR2(20),
  CODE NVARCHAR2(6),
  SD   NUMBER(1),
  ID   NUMBER
)
-- Add comments to the columns 
comment on column SITE_PY.HZ
  is '汉字';
comment on column SITE_PY.PY
  is '拼音';
comment on column SITE_PY.SM
  is '声母';
comment on column SITE_PY.YM
  is '韵母';
comment on column SITE_PY.CODE
  is '代码';
comment on column SITE_PY.SD
  is '音调';
comment on column SITE_PY.ID
  is '序号';
-- Create/Recreate indexes 
create index SITE_PY on SITE_PY (HZ)
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

第二步：要解决把整句汉字分解为一个表，创建一个自定义类型，就这一句没什么好说的。

create or replace type str_split is table of varchar2(4000);

第三步：一个分解函数，功能很简单将一句话拆解成一个表，这是拿别人的改的，原来的函数必须使用分割符，我把它改成有分隔符就按分隔符分割没分隔符就按单字符分隔。

CREATE OR REPLACE FUNCTION splitstr(p_string    IN VARCHAR2,
                                    p_delimiter IN VARCHAR2)
  return STR_SPLIT
  pipelined as
  v_length NUMBER := LENGTH(p_string);
  v_start  NUMBER := 1;
  v_index  NUMBER;
BEGIN
  v_index := 0;
  WHILE (v_start <= v_length) LOOP
    if p_delimiter is null then
--      dbms_output.put_line(v_index);
      v_index := v_index + 1;
    else
      v_index := INSTR(p_string, p_delimiter, v_start);
    end if;
  
    IF v_index = 0 THEN
      PIPE ROW(SUBSTR(p_string, v_start));
      v_start := v_length + 1;
    ELSE
      if p_delimiter is null then
        PIPE ROW(SUBSTR(p_string, v_start, 1));
      ELSE
        PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
      END IF;
    
      v_start := v_index + 1;
    END IF;
  END LOOP;

  RETURN;
END splitstr;

返回值是一个自定义表结构

第四步：重点哦，查询与合并

CREATE OR REPLACE FUNCTION to_py (str IN VARCHAR2)
   RETURN VARCHAR2
IS
   v_str VARCHAR2(4000);
BEGIN
select py into v_str from (
SELECT SYS_CONNECT_BY_PATH(PY,' ') py FROM
(select  RW,CASE WHEN HZ IS NULL THEN TO_CHAR(CF) ELSE TO_CHAR(PY) END PY
from (select rownum rw,column_value cf from table(splitstr(str,''))) a,site_py b
where a.cf=b.hz(+) 
order by rw)
START WITH rownum=1
CONNECT BY rw - 1 = PRIOR rw
order by rownum desc
)
where rownum=1;
return v_str;
END to_py;

代码分解：先调用splitstr函数把传递的中文字符串分解为表，然后用文字表与拼音表进行连接查询取得码表。有拼音的就使用拼音，没有拼音的使用原字符，并使用行号保持原有字符顺序。代码为

 

select  RW,CASE WHEN HZ IS NULL THEN TO_CHAR(CF) ELSE TO_CHAR(PY) END PY
from (select rownum rw,column_value cf from table(splitstr(str,''))) a,site_py b
where a.cf=b.hz(+) 
order by rw

使用SYS_CONNECT_BY_PATH函数将结果行合并为一列，比较重要的一点就是多音字的解决，我采用的是自动忽略使用第一个拼音好了。

关键点就这一句搞定重复时使用第一个

START WITH rownum=1
CONNECT BY rw - 1 = PRIOR rw

原理：rw是拼音比对时生成的行号，在这里代表父子关系，对于多音字这个rw是重复的。CONNECT BY rw - 1 = PRIOR rw，这行的rw-1等于上一行的rw，对于重复的就会自动去掉了。START WITH rownum=1确保仅从第一行开始生成路径，这样第一个字为多音字时也不会产生多个。最后对路径序列倒序排序取排序后第一个。

到此结束。

剩下的一个问题就是多音字的选择问题由于是随机选择有些读音实在不敢恭维。这就是拼音码表的优化问题了。

关于效率：公司的ORACLE数据库，37行数据查询0.156秒瞬出。2400行3.47秒（包含列结果时间），不带函数的列表时间为2.578.

2400行姓名、单位记录2-10字不等，1秒左右的列表差距，扣除列表时间，差距应在0.5秒左右。如果优化一下码表扣掉多余的多音字，使用常用字码表效率应该更高。