MySQL5指定时间段查询问题(已解决)

 
今天在做数据分发同步工具的时候遇到了点问题,MySQL5按照字段的时间段范围查询不能成功的问题。
 
现在问题还没有解决,下面是我专门为了一个低级的查询写了个JDBC测试,连接池什么的统统不要,将问题的范围尽量缩小。
 
原因是PreparedStatement设置java.sql.Date参数没有被认为传递的是合法的Date,而查询不到数据。如果你不用JDBC、不用PreparedStatement,你是发现不到这个问题的。
 
一、环境
MySQL5.1.30
Java 1.5
 
 
SQL脚本
CREATE TABLE genreparam (                         
                            genre_id int(11) NOT NULL,                    
                            updatenum bigint(20) DEFAULT '0',     
                            ishd bigint(1) DEFAULT NULL,                
                            kind bigint(20) DEFAULT NULL,             
                            clicks bigint(20) DEFAULT '0',            
                            create_time datetime NOT NULL,            
                            update_time datetime NOT NULL,            
                            PRIMARY KEY (genre_id)                            
                        ) ENGINE=MyISAM DEFAULT CHARSET=utf8        
 
 
二、测试
 
要求传递两个参数,用来控制update_time字段的范围,查询数据库记录。
 
测试代码:
import java.sql.*; 
import java.util.ArrayList; 
import java.util.Calendar; 
import java.util.List; 

public class TestJDBC { 
        private static final String genreparam_sql = "" + 
                        "select genre_id, updatenum, ishd, kind, clicks, create_time, update_time\n" + 
                        "    from genreparam\n" + 
                        " where update_time BETWEEN ? AND ? \n" + 
                        " limit ?," + 300; 

        private static final String genreparam1_sql = "" + 
                        "select genre_id, updatenum, ishd, kind, clicks, create_time, update_time\n" + 
                        "    from genreparam\n" + 
                        " where DATE_FORMAT(update_time, '%Y-%c-%e %T') BETWEEN ? AND ? \n" + 
                        " limit ?," + 300; 

        public static void main(String[] args) throws SQLException, ClassNotFoundException { 
                Class.forName("com.mysql.jdbc.Driver"); 
                String user = "vcom"
                String pswd = "vcom"
                String dburl = "jdbc:mysql://192.168.104.163:3306/VOD3_01"; 
                Connection conn = DriverManager.getConnection(dburl, user, pswd); 
                System.out.println(conn == null); 

//                List<Genreparam> genList = new ArrayList<Genreparam>(); 
                Calendar c = Calendar.getInstance(); 
                c.set(1900, 1, 1); 
                Date _start = new Date(c.getTimeInMillis()); 
                Date _end = new Date(System.currentTimeMillis()); 
                System.out.println(_start); 
                System.out.println(_end); 
                PreparedStatement pstmt = conn.prepareStatement(genreparam1_sql); 
                pstmt.setDate(1, _start); 
                pstmt.setDate(2, _end); 
                pstmt.setString(1, "1900-2-1 00:00:00"); 
                pstmt.setString(2, "2010-2-1 00:00:00"); 
                pstmt.setInt(3, 0); 
                ResultSet rs = pstmt.executeQuery(); 
                int i = 0; 
                while (rs.next()) { 
//                        Genreparam genreparam = new Genreparam(); 
//                        genreparam.setGenre_id(rs.getLong("genre_id")); 
//                        genreparam.setUpdatenum(rs.getLong("updatenum")); 
//                        genreparam.setIshd(rs.getInt("ishd")); 
//                        genreparam.setKind(rs.getInt("kind")); 
//                        genreparam.setClicks(rs.getLong("clicks")); 
//                        genreparam.setCreate_time(rs.getDate("create_time")); 
//                        genreparam.setUpdate_time(rs.getDate("update_time")); 
//                        genList.add(genreparam); 
                        i++; 
                } 
                rs.close(); 
                conn.close(); 
                System.out.println("查询记录总数为:" + i); 
        } 
}
 
传递java.sql.Date参数总查不到数据,于是修改为传递字符串,才勉强获取到了。。
 
false 
1900-02-01 
2009-07-31 
查询记录总数为:4 

Process finished with exit code 0
 
写SQL查:
SELECT * 
    FROM genreparam 
WHERE DATE_FORMAT(update_time, '%Y-%c-%e %T'BETWEEN '1900-2-1 00:00:00' AND    '2010-2-5 00:00:00'
查询了4条记录。
 
三、思考与解决
 
究竟是什么原因呢,日期不能直接做大小比较吗?
最终发现,MySQL对日期的比较直接可以用字符串来比较,比如:
select genre_id, updatenum, ishd, kind, clicks, create_time, update_time 
    from genreparam 
where update_time < '2010-2-1 00:00:00 '
虽然,'2010-2-1 00:00:00 '不是日期类型,但仍然可以做比较。
 
下面看看如果将字符串转为日期呢,能否比较呢?
select genre_id, updatenum, ishd, kind, clicks, create_time, update_time 
    from genreparam 
where update_time < TIMESTAMP('2010-2-1 00:00:00 ')
执行结果依然正确。
 
这就非常让人迷惑了,为什么不能直接传递一个java.sql.Date参数就挂了呢?
在此,实在很无奈,就写个格式化方法吧,将Date参数改为传String参数就可以了。
        private static final String data_format = "yyyy-MM-dd kk:mm:ss"

        public static String data2ISOString(java.util.Date date) { 
                SimpleDateFormat sf = new SimpleDateFormat(data_format); 
                return sf.format(date); 
        }
 
这样,问题就彻底得到解决了。
 


本文转自 leizhimin 51CTO博客,原文链接:http://blog.51cto.com/lavasoft/185888,如需转载请自行联系原作者
上一篇:使用nginx负载均衡lucene


下一篇:[MySQL 优化] --order by 原理