mysql如何按首字母进行检索数据_Mysql

一、介绍

最近有个这样的需求，一张有大量数据元素的表，这里就暂且举例为 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();
        }
    }
}

测试结果截图如下：