#参考这篇博客:/naruto1021/article/details/17502783,不过这个只支持查询一个中文字母，这是不太符合我的要求，改写如下：
如果要实现查询名字的完整，请参考如下我的程序：
# 查询中文拼音首字母： 支持查询三个中文以内中文，比如 ”张三枫“，输入‘zs’，就可以查询出来
select name,get_first_pinyin_char(name) from retire_infor re
where get_first_pinyin_char(re.name) = 'zs' ;
#首先创建如下函数：支持查询三个中文以内的首字母
BEGIN
DECLARE V_RETURN VARCHAR(255);
DECLARE V_RETURN_TWO VARCHAR(255);
DECLARE V_RETURN_THREE VARCHAR(255);
DECLARE V_FIRST_CHAR VARCHAR(2);
DECLARE V_TWO_CHAR VARCHAR(2);
DECLARE V_THREE_CHAR VARCHAR(2);
#第一个中文首字母匹配
SET V_FIRST_CHAR = UPPER(LEFT(PARAM,1));
SET V_RETURN = V_FIRST_CHAR;
IF LENGTH( V_FIRST_CHAR) <> CHARACTER_LENGTH( V_FIRST_CHAR ) THEN
SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(PARAM 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');
END IF;
#第二个中文首字母匹配
SET V_TWO_CHAR = UPPER(SUBSTR(PARAM,2,1));
SET V_RETURN_TWO = V_TWO_CHAR;
IF LENGTH( V_TWO_CHAR) <> CHARACTER_LENGTH( V_TWO_CHAR ) THEN
SET V_RETURN_TWO = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(UPPER(SUBSTR(PARAM,2,1)) 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');
END IF;
#第三个中文首字母匹配
SET V_THREE_CHAR = UPPER(SUBSTR(PARAM,3,1));
SET V_RETURN_THREE= V_THREE_CHAR;
IF LENGTH( V_THREE_CHAR) <> CHARACTER_LENGTH( V_THREE_CHAR ) THEN
SET V_RETURN_THREE = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(UPPER(SUBSTR(PARAM,3,1)) 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');
END IF;
#三个字母进行拼接
RETURN CONCAT(V_RETURN,V_RETURN_TWO,V_RETURN_THREE);
END
第二步：既然数据库可以查询对应的拼音了，所以前端，就要支持，当用户输入中文或者英文，都需要转成拼音，这样才能放到数据库中进行查询，java层面，也有一个jar可以将中文转成拼音，是这个jar包：pinyin4j.jar，示例代码如下：感谢这位博主：/liusong0605/article/details/22754293：
/**
* 获取汉字串拼音首字母，英文字符不变
*
* @param chinese 汉字串
* @return 汉语拼音首字母
*/
public static String cn2FirstSpell(String chinese) {
StringBuffer pybf = new StringBuffer();
char[] arr = chinese.toCharArray();
HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);
defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
for (int i = 0; i < arr.length; i++) {
if (arr[i] > 128) {
try {
String[] _t = PinyinHelper.toHanyuPinyinStringArray(arr[i], defaultFormat);
if (_t != null) {
pybf.append(_t[0].charAt(0));
}
} catch (BadHanyuPinyinOutputFormatCombination e) {
e.printStackTrace();
}
} else {
pybf.append(arr[i]);
}
}
return pybf.toString().replaceAll("\\W", "").trim().toUpperCase();
}
/**
* 获取汉字串拼音，英文字符不变
*
* @param chinese 汉字串
* @return 汉语拼音
*/
public static String cn2Spell(String chinese) {
StringBuffer pybf = new StringBuffer();
char[] arr = chinese.toCharArray();
HanyuPinyinOutputFormat defaultFormat = new HanyuPinyinOutputFormat();
defaultFormat.setCaseType(HanyuPinyinCaseType.LOWERCASE);
defaultFormat.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
for (int i = 0; i < arr.length; i++) {
if (arr[i] > 128) {
try {
pybf.append(PinyinHelper.toHanyuPinyinStringArray(arr[i], defaultFormat)[0]);
} catch (BadHanyuPinyinOutputFormatCombination e) {
e.printStackTrace();
}
} else {
pybf.append(arr[i]);
}
}
return pybf.toString();
}
public static void main(String[] args)
{
String cnStr = "柳松";
System.out.println(cn2FirstSpell(cnStr));
System.out.println(cn2Spell(cnStr));
}
如果觉得《mysql 拼音首字母_MySQL拼音首字母查询（支持三个中文以内的查询）》对你有帮助，请点赞、收藏，并留下你的观点哦！
