MySQL拼音首字母查询（支持三个中文以内的查询）

          #参考这篇博客:http://blog.youkuaiyun.com/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，示例代码如下：感谢这位博主：http://blog.youkuaiyun.com/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));  
   }