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

来源：互联网发布：排序算法及时间复杂度编辑：程序博客网时间：2024/05/17 03:24

#参考这篇博客:http://blog.csdn.net/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.csdn.net/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));     }

阅读全文

0 0