写代码中的某一天,我写下了如下的代码:
public Connection getConn() {
String driver = "com.mysql.jdbc.Driver";
String url = "***";
String username = "***";
String password = "***";
Connection conn = null;
PreparedStatement pst = null;
try {
Class.forName(driver); //classLoader,加载对应驱动
conn = (Connection) DriverManager.getConnection(url, username, password);
String sql = "select * from info info , info_extend extend where info.id = extend.infoid "
+ " order by (CASE WHEN info.pid=1006 THEN 1 ELSE 0 END) asc ,(CASE WHEN info.pid = 1006 THEN info.intext ELSE extend.intext END) desc "
+ " limit 0,10";
System.out.println(sql);
pst = (PreparedStatement) conn.prepareStatement(sql);//准备执行语句
ResultSet rs = pst.executeQuery(); while(rs.next()){ System.out.println(rs.getString(1)+"-----"+rs.getString(2) + "-----" + rs.getString(3) + "----" + rs.getString(4)); } } catch (Exception e) { e.printStackTrace(); } return conn; }
很明显,上面这段sql是连表查询了两张表 info 和 extend ,有两个字段进行order by ,如果pid=1006的话那就排在上面,如果pid不是1006那就按extend表的intext字段来排序,我们可以认为这两个order by 是没有问题的。
但是查询出来的结果就存在一点奇怪了,请看!
第一次执行:
第二次执行,在看:
请注意细看,两次执行的数据的顺序发生了改变。虽然他还是按照我们的orderby的顺序排的(只是在intext值一样的时候,他就开始随机的排序了)
看到了吧,同一段sql居然会有不一样的结果(),是不是很奇葩~
我觉得很奇怪,就把sql粘下来,拿到mysql客户端去执行,结果如下
无论执行多少次,他们的顺序也不会发生变化。这就真的很奇怪了,sql都是一样的,为什么就会有不一样的结果呢。
查询了很多资料,也没找到一个比较确认的理由,只是较多人认为是由于缓存的存在。
引用一下别人的回答:
“
根数据库系统的算法有关,早期版本的算法是自然的多个线程二分法,那个线程先查到满足条件的数据就先输出出来,这样就是乱序的,后期经过改进按照主键自然排序输出。
如果order by的值相同,一般是按自然排序,就是首个字符的字母或汉字的发音的首字母的s排序。
”
order by的字段自然排序,如果你的order by 字段是resort,resort值相同的情况下,是可能有两种结果,一种就是你列出的随机排序,还有一种就是按主键来排序。这个问题不是固定的,还可能跟你的服务器性能都有关系,如果内存足够大,执行mysql的时候会提供足够大的缓冲池,也可能会出现另一种结果。
所以很有可能是因为缓存的存在,在mysql客户端存在缓存,然后每一次查询都走缓存所以他的顺序不会发生改变。然而执行java程序顺序不一致,可能就是因为没有走缓存,每一次都是实时查询。也许java程序的这种结果本身就具有更多的可靠性。
还有一个很奇怪的现象:有时候java程序的结果是不会变的,有时候就是会发生变化的。这一点很令人疑惑,但同时也更肯定了缓存的因素。
那如何解决这个问题?
很简单,他不是顺序有时会不一致吗,那我们再给他一个顺序进行排序就好了。在order by 后面加上 id desc , 那么我们的查询结果就是一致了的。