mysql 按首字母进行检索数据

爱被打了一巴掌 2022-02-04 11:01 310阅读 0赞

## 一、介绍 ##

最近有个这样的需求，一张有大量数据元素的表，这里就暂且举例为 student 表，现在要按照学生的首字母来进行检索学生信息。比如用户输入“ZS”，获得的学生列表的姓名第一个字拼音以“Z”开头，第二个字以“S”开头。我想这个应该大家都明白什么需求，对于这个需求我之前项目中没有遇到过，至于有没有一些搜索系统或者第三方解决这个问题，我不太清楚，下面是我就这个需求进行的实现，也为以后自己遇到类似需求做参考。

## 二、分析 ##

以首字母来查询有两种情况，1、可以增加表字段，即将student表中添加一个firstwords字段来记录学生名的首字母，这样就可以直接拿此字段进行模糊匹配检索（name：“张三”，firstwords:"ZS"）。2、不能增加表字段，工作中很多表或者数据库都是客户的或者是长时间不动的表，基本不建议修改的情况，无法增加首字母的相关字段，此时就需要用代码来实现此需求。这里只介绍第二种情况的实现，第一种情况太简单就不说了。

## 三、实现 ##

刚开始我看需求时，我上网各种查询，发现很多方法都不能用或者说我用不好吧，不过通过阅读网上各种方法，我自己总结了一个搜索方式，测试过没发现什么问题。

我们知道数据库查询时可以排序查询，比如ASC关键字排序，那么汉子是通过什么排序呢，最后通过网上查阅资料，如果存储汉字的字段编码使用的是GBK字符集的话，其采用的是拼音排序的方法，UTF-8的字符集目前我没去研究，虽然mysql存储数据基本都是utf-8，但可以在查询的时候转为GBK，一样可以查询，下面是A—Z的字符集对应汉子的范围

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");
        }

这是段代码，代码意思为每个首字母对应汉子的包含范围，比如：首字母为“a”的汉子ASC编码范围都在45217和45252之间，有了这个信息基本上也就可以完成该需求了，下面是需求实现的代码

FisrtWordsSqlUtils 这个Utils类是用来存储汉子范围和拼接sql语句用的

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();
            //排除该三个首字母，因为中文就没有以他们开头的拼音
            if(str.contains("i")||str.contains("u")||str.contains("v")){
                System.out.println("暂无数据");
                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”这几个是数据库的一些函数，整体意思先转GBK编码，然后截取某个位置字母，然后转16进制的字符集，然后再由16进制转10进制，最终得到的就是像那个hashmap集合里面的那一串数字。后面的between  and 语句就不解释了，这个不知道估计这篇文章也不会看的太不懂。

上面的sql语句已经可以得到，下面是mysql数据库原生连接的方法

/**
     * @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("未能返回结果");
            }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;
        }
    }

这个是连接数据库的工具类，手写的原生的，如果项目中使用第三方的如mybatis等可以不用，这里我写这个也是为了测试使用，逻辑上看的更清晰一点。

下面是最终测试的方法

/**
     * @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();
            }
        }
    }

测试结果截图如下：

![20190507165356614.png][]

文章核心点就是每个首字母对应的ASC编码的范围，利用此范围进行拼接sql，然后查询数据

[20190507165356614.png]: /images/20220204/8c710416eb474924817b450104f07eb4.png