fristpinyin ： 此函数是将一个中文字符串的第一个汉字转成拼音字母 （例如："中国人"->z）
create function `fristpinyin`(p_name varchar(255)) returns varchar(255) charset utf8
begin
declare v_return varchar(255);
set v_return = elt(interval(conv(hex(left(convert(p_name using gbk),1)),16,10),
0xb0a1,0xb0c5,0xb2c1,0xb4ee,0xb6ea,0xb7a2,0xb8c1,0xb9fe,0xbbf7,
0xbfa6,0xc0ac,0xc2e8,0xc4c3,0xc5b6,0xc5be,0xc6da,0xc8bb,
0xc8f6,0xcbfa,0xcdda,0xcef4,0xd1b9,0xd4d1),
'a','b','c','d','e','f','g','h','j','k','l','m','n','o','p','q','r','s','t','w','x','y','z');
return v_return;
end
pinyin ：此函数是将一个中文字符串对应拼音母的每个相连 (例如："中国人"->zgr)
create function `pinyin`(p_name varchar(255)) returns varchar(255) charset utf8
begin
declare v_compare varchar(255);
declare v_return varchar(255);
declare i int;
SET I = 1;
SET V_RETURN = '';
while I SET V_COMPARE = SUBSTR(P_NAME, I, 1);
IF (V_COMPARE != '') THEN
#SET V_RETURN = CONCAT(V_RETURN, ',', V_COMPARE);
SET V_RETURN = CONCAT(V_RETURN, fristPinyin(V_COMPARE));
#SET V_RETURN = fristPinyin(V_COMPARE);
END IF;
SET I = I + 1;
end while;
IF (ISNULL(V_RETURN) or V_RETURN = '') THEN
SET V_RETURN = P_NAME;
END IF;
RETURN V_RETURN;
END
示例：
mysql> select p.province, fristPinyin(p.province), pinyin(p.province) from province p;
+------------------+-------------------------+--------------------+
| province | fristPinyin(p.province) | pinyin(p.province) |
+------------------+-------------------------+--------------------+
| 北京市 | B | BJS |
| 天津市 | T | TJS |
| 河北省 | H | HBS |
| 山西省 | S | SXS |
| 内蒙古自治区 | N | NMGZZQ |
+------------------+-------------------------+--------------------+
5 rows in set
