快捷導(dǎo)航

mysql如何按首字母進(jìn)行檢索數(shù)據(jù)

更新時(shí)間：2024年11月28日 14:25:26 作者：農(nóng)碼天下

這篇文章介紹了如何根據(jù)學(xué)生的首字母檢索學(xué)生信息的需求,并提供了一種不增加表字段的實(shí)現(xiàn)方法,通過(guò)利用漢字的拼音排序特性,結(jié)合數(shù)據(jù)庫(kù)的排序和轉(zhuǎn)換函數(shù),實(shí)現(xiàn)了根據(jù)首字母模糊匹配檢索學(xué)生信息的功能

一、介紹

最近有個(gè)這樣的需求，一張有大量數(shù)據(jù)元素的表，這里就暫且舉例為 student 表，現(xiàn)在要按照學(xué)生的首字母來(lái)進(jìn)行檢索學(xué)生信息。

比如用戶(hù)輸入“ZS”，獲得的學(xué)生列表的姓名第一個(gè)字拼音以“Z”開(kāi)頭，第二個(gè)字以“S”開(kāi)頭。

我想這個(gè)應(yīng)該大家都明白什么需求，對(duì)于這個(gè)需求我之前項(xiàng)目中沒(méi)有遇到過(guò)，至于有沒(méi)有一些搜索系統(tǒng)或者第三方解決這個(gè)問(wèn)題，我不太清楚，下面是我就這個(gè)需求進(jìn)行的實(shí)現(xiàn)，也為以后自己遇到類(lèi)似需求做參考。

二、分析

以首字母來(lái)查詢(xún)有兩種情況：

1、可以增加表字段，即將student表中添加一個(gè)firstwords字段來(lái)記錄學(xué)生名的首字母，這樣就可以直接拿此字段進(jìn)行模糊匹配檢索（name：“張三”，firstwords:"ZS"）。

2、不能增加表字段，工作中很多表或者數(shù)據(jù)庫(kù)都是客戶(hù)的或者是長(zhǎng)時(shí)間不動(dòng)的表，基本不建議修改的情況，無(wú)法增加首字母的相關(guān)字段，此時(shí)就需要用代碼來(lái)實(shí)現(xiàn)此需求。

這里只介紹第二種情況的實(shí)現(xiàn)，第一種情況太簡(jiǎn)單就不說(shuō)了。

三、實(shí)現(xiàn)

剛開(kāi)始我看需求時(shí)，我上網(wǎng)各種查詢(xún)，發(fā)現(xiàn)很多方法都不能用或者說(shuō)我用不好吧，不過(guò)通過(guò)閱讀網(wǎng)上各種方法，我自己總結(jié)了一個(gè)搜索方式，測(cè)試過(guò)沒(méi)發(fā)現(xiàn)什么問(wèn)題。

我們知道數(shù)據(jù)庫(kù)查詢(xún)時(shí)可以排序查詢(xún)，比如ASC關(guān)鍵字排序，那么漢子是通過(guò)什么排序呢，最后通過(guò)網(wǎng)上查閱資料，如果存儲(chǔ)漢字的字段編碼使用的是GBK字符集的話(huà)，其采用的是拼音排序的方法，UTF-8的字符集目前我沒(méi)去研究，雖然mysql存儲(chǔ)數(shù)據(jù)基本都是utf-8，但可以在查詢(xún)的時(shí)候轉(zhuǎn)為GBK，一樣可以查詢(xún)，下面是A—Z的字符集對(duì)應(yīng)漢子的范圍

 static {
        wordsMap = new HashMap<>();
        wordsMap.put("a","45217,45252");wordsMap.put("b","45253,45760");wordsMap.put("c","45761,46317");
        wordsMap.put("d","46318,46825");wordsMap.put("e","46826,47009");wordsMap.put("f","47010,47296");
        wordsMap.put("g","47297,47613");wordsMap.put("h","47614,48118");wordsMap.put("j","48119,49061");
        wordsMap.put("k","49062,49323");wordsMap.put("l","49324,49895");wordsMap.put("m","49896,50370");
        wordsMap.put("n","50371,50613");wordsMap.put("o","50614,50621");wordsMap.put("p","50622,50905");
        wordsMap.put("q","50906,51386");wordsMap.put("r","51387,51445");wordsMap.put("s","51446,52217");
        wordsMap.put("t","52218,52697");wordsMap.put("w","52698,52979");wordsMap.put("x","52980,53640");
        wordsMap.put("y","53689,54480");wordsMap.put("z","54481,55289");
    }

這是段代碼，代碼意思為每個(gè)首字母對(duì)應(yīng)漢子的包含范圍，比如：首字母為“a”的漢子ASC編碼范圍都在45217和45252之間，有了這個(gè)信息基本上也就可以完成該需求了，下面是需求實(shí)現(xiàn)的代碼

FisrtWordsSqlUtils 這個(gè)Utils類(lèi)是用來(lái)存儲(chǔ)漢子范圍和拼接sql語(yǔ)句用的

package com.oracle;

import java.util.HashMap;
import java.util.Map;

/**
 * @author WYH
 */
public class FisrtWordsSqlUtils {
    
    //依次從小到大排序
    private static Map<String,String> wordsMap;

    static {
        wordsMap = new HashMap<>();
        wordsMap.put("a","45217,45252");wordsMap.put("b","45253,45760");wordsMap.put("c","45761,46317");
        wordsMap.put("d","46318,46825");wordsMap.put("e","46826,47009");wordsMap.put("f","47010,47296");
        wordsMap.put("g","47297,47613");wordsMap.put("h","47614,48118");wordsMap.put("j","48119,49061");
        wordsMap.put("k","49062,49323");wordsMap.put("l","49324,49895");wordsMap.put("m","49896,50370");
        wordsMap.put("n","50371,50613");wordsMap.put("o","50614,50621");wordsMap.put("p","50622,50905");
        wordsMap.put("q","50906,51386");wordsMap.put("r","51387,51445");wordsMap.put("s","51446,52217");
        wordsMap.put("t","52218,52697");wordsMap.put("w","52698,52979");wordsMap.put("x","52980,53640");
        wordsMap.put("y","53689,54480");wordsMap.put("z","54481,55289");
    }
    /**
     * 拼接sql
     * @param str
     */
    public static String getSql(String str){
        String wordsStr = str.toLowerCase();
        //排除該三個(gè)首字母，因?yàn)橹形木蜎](méi)有以他們開(kāi)頭的拼音
        if(str.contains("i")||str.contains("u")||str.contains("v")){
            System.out.println("暫無(wú)數(shù)據(jù)");
            return null;
        }
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < wordsStr.length(); i++) {
            String c = wordsStr.charAt(i)+"";
            String wordsASC  = wordsMap.get(c);
            String[] asc = wordsASC.split(",");
            int ASC01 = Integer.parseInt(asc[0]);
            int ASC02 = Integer.parseInt(asc[1]);
            if(i!=wordsStr.length()-1){
                sb.append("CONV(HEX(SUBSTRING(CONVERT(name USING gbk ), "+(i+1)+",1)), 16, 10) BETWEEN "+ASC01+" AND "+ASC02 + " and ");
            }else{
                sb.append("CONV(HEX(SUBSTRING(CONVERT(name USING gbk ), "+(i+1)+",1)), 16, 10) BETWEEN "+ASC01+" AND "+ASC02);
            }
        }
        return sb.toString();
    }
}

sql拼接代碼中：“CONV，HEX，SUBSTRING，CONVERT”這幾個(gè)是數(shù)據(jù)庫(kù)的一些函數(shù)，整體意思先轉(zhuǎn)GBK編碼，然后截取某個(gè)位置字母，然后轉(zhuǎn)16進(jìn)制的字符集，然后再由16進(jìn)制轉(zhuǎn)10進(jìn)制，最終得到的就是像那個(gè)hashmap集合里面的那一串?dāng)?shù)字。后面的between and 語(yǔ)句就不解釋了，這個(gè)不知道估計(jì)這篇文章也不會(huì)看的太不懂。

上面的sql語(yǔ)句已經(jīng)可以得到，下面是mysql數(shù)據(jù)庫(kù)原生連接的方法

/**
 * @author WYH
 */
public class MySqlConnectUtil {

    public static Map<String,Object> getResultMap(String dbUser, String dbPwd, String orclUrl, String tableName,String mySql){
        Map<String,Object> resultMap = new HashMap<>();
        Connection con=null;
        PreparedStatement pre=null;
        ResultSet resultSet=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con= DriverManager.getConnection(orclUrl,dbUser,dbPwd);
            String sql="select * from "+tableName +" where " + mySql;
            System.out.println(sql);
            pre=con.prepareStatement(sql);
            resultSet=pre.executeQuery();
            resultMap = getResultMap(resultSet);
        }catch(Exception ex){
            ex.printStackTrace();
            System.out.println("未能返回結(jié)果");
        }finally{
            JdbcUtil.release(con,pre,resultSet);
        }
        return resultMap;
    }

    private static Map<String,Object> getResultMap(ResultSet resultSet) throws SQLException {
        Map<String,Object> reusltMap = new HashMap<>();
        List<Map> maps = new ArrayList<>();
        ResultSetMetaData rsmd = resultSet.getMetaData();
        while(resultSet.next()){
            int columnCount = rsmd.getColumnCount();
            Map map = new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                Object object = resultSet.getObject(i);
                String columnName = rsmd.getColumnName(i);
                String columnTypeName = rsmd.getColumnTypeName(i).toLowerCase();
                if(columnTypeName.equals("date")&&columnTypeName.equals("time")&&columnTypeName.equals("timestamp")){
                    Date date = (Date) object;
                    long time = date.getTime();
                    map.put(columnName,time+"");
                }else {
                    map.put(columnName, object);
                }
            }
            maps.add(map);
        }
        reusltMap.put("data",maps);
        return reusltMap;
    }
}

這個(gè)是連接數(shù)據(jù)庫(kù)的工具類(lèi)，手寫(xiě)的原生的，如果項(xiàng)目中使用第三方的如mybatis等可以不用，這里我寫(xiě)這個(gè)也是為了測(cè)試使用，邏輯上看的更清晰一點(diǎn)。

下面是最終測(cè)試的方法

/**
 * @author WYH
 */
public class TestFirstWordsSearcher {
    public static void main(String[] args) {
        String userName = "root";
        String password = "root";
        String oracleUrl = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=utf-8";
        String tableName = "student";
        try {
            String sql = FisrtWordsSqlUtils.getSql("LS");
            Map<String, Object> reusltMap =MySqlConnectUtil.getResultMap(userName,password,oracleUrl,tableName,sql);
            String s = JSON.toJSONString(reusltMap);
            System.out.println(s);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

測(cè)試結(jié)果截圖如下：