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 = "" + 

                        " where DATE_FORMAT(update_time, '%Y-%c-%e %T') BETWEEN ? AND ? \n" + 

        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 '不是日期類型，但仍然可以做比較。

下面看看如果将字元串轉為日期呢，能否比較呢？

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，如需轉載請自行聯系原作者