MySQL 自定义函数 - 根据输入汉字获取拼音的全拼

 

 

?

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

CREATE DEFINER=`root`@`%` 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